Excel VBA多个For循环不工作
问题描述:
我似乎陷入了一个奇怪的问题。我有下面的Excel VBA代码来访问一个网站并输入一个用户ID(来自表1中的用户ID列A),然后检索在点击提交按钮后显示的用户名,然后继续其余的用户ID。Excel VBA多个For循环不工作
Public Sub TEST()
TestPage = "http://test.com/"
Dim IE As New InternetExplorer
Dim Doc As HTMLDocument
Dim GetElem As Object
Dim GetElem2 As Object
IE.Visible = True
IE.navigate TestPage
Do
DoEvents
Loop Until IE.readyState = READYSTATE_COMPLETE
Application.Wait (Now + TimeValue("0:00:04"))
Set Doc = IE.document
CurRow = Sheet1.UsedRange.Rows.Count
Do While CurRow > 0
Application.Wait (Now + TimeValue("0:00:4"))
'Find/Get the userID textbox and enter the current userID
For Each GetElem In Doc.getElementsByTagName("input")
If GetElem.ID = "query" Then 'I could just do getElementByID later
GetElem.Value = Sheet1.Range("A" & CurRow).Value
End If
Next
'Find and click the submit button
For Each GetElem2 In Doc.getElementsByTagName("button")
If GetElem2.Type = "submit" Then
GetElem2.Click
Application.Wait (Now + TimeValue("0:00:03"))
End If
Next
CurRow = CurRow - 1
Loop
End Sub
问题是代码只能工作一次。它将第一个用户标识输入文本框并点击提交。当它循环并尝试输入下一个用户ID时,代码会陷入循环。
如果我删除整个2nd For-Next循环,它将起作用(尽管它不提交,它会在文本框中输入每个用户标识)。
最重要的是,如果我使用F8调试代码一步一步,一切工作正常。只有让问题时,完全运行的代码。:(
答
Public Sub TEST()
TestPage = "http://test.com/"
Dim IE As New InternetExplorer
Dim Doc As HTMLDocument
Dim GetElem As Object
Dim GetElem2 As Object
IE.Visible = True
IE.navigate TestPage
Do
DoEvents
Loop Until IE.readyState = READYSTATE_COMPLETE
Application.Wait (Now + TimeValue("0:00:04"))
Set Doc = IE.document
CurRow = Sheet1.UsedRange.Rows.Count
For Each GetElem In Doc.getElementsByTagName("input")
If GetElem.ID = "query" Then 'I could just do getElementByID later
GetElem.Value = Sheet1.Range("A" & CurRow).Value
For Each GetElem2 In Doc.getElementsByTagName("button")
If GetElem2.Type = "submit" Then
GetElem2.Click
Application.Wait (Now + TimeValue("0:00:03"))
End If
Next GetElem2
End If
CurRow = CurRow + 1
Next GetElem
End Sub
放了'debug.print CurRow'了'Loop'前,并检查它在即时窗口给的值。 – Vityata
它死循环。 CurRow总是大于零,每次它通过时,都会重新启动“For Each GetElem In Doc.getElementsByTagName(”input“)” - 因此它总是会在第一个实例中运行 - 每个循环都需要试着把所有的.query名字先放到数组中,然后用i = lbound(myArray)来绑定(myArray) –
我不明白:(我可以看到你的'你可能会看到这个问题,但是我在这里挠挠脑袋,为什么你说CurRow总是大于零?我的意思是CurRow从表单开始1.usedrange.rows.count是78,那么它每次在Do-While循环中循环时减1 – jay