使用Excel VBA编辑HTML代码

Question

我需要使用VBA编辑HTML代码。至于编辑文本框的值，我确实得到了这个工作。我的问题是，当我模拟点击“提交”按钮时，出现了新的表格。网址保持不变，但现在为表格生成了新的html代码。我试图从这些表中读取数据，但当我尝试查询它们时，似乎它们不存在。所以我猜我需要更新或刷新IE浏览器的HTML代码后，我按“提交”按钮。我似乎无法弄清楚如何做到这一点。任何帮助是极大的赞赏。这是我的代码到目前为止：

Sub Import*Data() 

Dim SearchFor As IHTMLElement 
Dim RowNumber As Long 
RowNumber = 4 

'to refer to the running copy of Internet Explorer 
Dim ie As InternetExplorer 
'to refer to the HTML document returned 
Dim html As HTMLDocument 
'open Internet Explorer in memory, and go to website 
Set ie = New InternetExplorer 
ie.Visible = True 
ie.Navigate "http://google.com" 
'Wait until IE is done loading page 
Do While ie.ReadyState <> READYSTATE_COMPLETE 
Application.StatusBar = "Trying to go to TRSDataBase ..." 
DoEvents 
Loop 

Set html = ie.Document 
Application.StatusBar = "" 

'clear old data out and put titles in 
Cells.Clear 

Set SearchFor = html.getElementById("ddl_process") 

'if this is the tag containing the question details, process it 
If SearchFor.ID = "ddl_process" Then 

'Replace the value of dl-process with copperhead name 
Call SearchFor.setAttribute("value", "CPHB_FAT") 
Cells(RowNumber, 1).Value = "Successfully replaced ddl_process to : " &  
SearchFor.getAttribute("value") 

'go on to next row of worksheet 
RowNumber = RowNumber + 1 
End If 

Set SearchFor = html.getElementById("txt_startdate") 
If SearchFor.ID = "txt_startdate" Then 

'Replace the value of dl-process with copperhead name 
Call SearchFor.setAttribute("value", "07-07-17") 
Cells(RowNumber, 1).Value = "Successfully replaced startdate to : " &  
SearchFor.getAttribute("value") 

'go on to next row of worksheet 
RowNumber = RowNumber + 1 

End If 

Set SearchFor = html.getElementById("txt_enddate") 
If SearchFor.ID = "txt_enddate" Then 

'Replace the value of dl-process with copperhead name 
Call SearchFor.setAttribute("value", "07-14-17") 
Cells(RowNumber, 1).Value = "Successfully replaced enddate to : " &    
SearchFor.getAttribute("value") 

'go on to next row of worksheet 
RowNumber = RowNumber + 1 

End If 
'find view button and click it 
Set SearchFor = html.getElementById("btn_header") 
If SearchFor.ID = "btn_header" Then 
    SearchFor.Click 
    Cells(RowNumber, 1).Value = "The View Button has been clicked." 
    'go on to next row of worksheet 
    RowNumber = RowNumber + 1 
End If 


'Now get data from table after it loads 
Application.Wait (Now + TimeValue("0:00:20")) 
Set html = ie.Document <----------This is where i am trying to update or refresh my code after it loads with the new tables 

Debug.Print ie.Document.body.innerHTML 
Range("L5").Value = ie.Document.getElementsByTag("table") 
(1).Rows(1).Cells(2).innerText 

Answer 1

尝试获取一个新的指针窗口。有时候，这就是诀窍。

Public Function FindWindow(SearchBy As String, SearchCriteria As String) As Object 
    Dim Window As Object 

    For Each Window In CreateObject("Shell.Application").Windows 
     If SearchBy = "URL" And Window.LocationURL Like "*" & SearchCriteria & "*" Then 
      Set FindWindow = Window 
      Exit Function 
     ElseIf SearchBy = "Name" And Window.LocationName Like "*" & SearchCriteria & "*" Then 
      Set FindWindow = Window 
      Exit Function 
     End If 
    Next 

    Set FindWindow = Nothing 
End Function 

Sub getNewPointer() 
    Dim ie As InternetExplorer 

    ie.Navigate "www.google.com" 

    'Wait for the page to load and do actions etc 
    'Your code here 
    ' 
    ' 

    'Clear the IE reference 
    Set ie = Nothing 

    'get a new pointer to the window of interest 
    'Keep in mind it finds the first matching Window based on your criteria! 
    Set ie = FindWindow("URL", "www.google.ca") 

    'Try getting the property you want 
    Debug.Print 
End Sub