我自由地承认我对HTML对象库没有太多的经验。
我有一个带有IRS雇主识别号的电子表格，我必须将其识别为不在数据库中。我只有基于Web的对此数据库的访问权限，而其他一些人已经向该数据库编写HTML并管理该数据库。我相信他们的方法过时，设计实践也很差。但我最终不是数据库管理员，那我知道些什么？因此，我通常的做法是在搜索页面上输入EIN并记录结果。
我的Excel宏旨在
登录到基于Web的数据库查询站点。
遍历EIN，注意找到了哪些EIN
但是，我有以下问题：
答：登录部分效果很好，但有一个怪癖：我必须离开
验证登录是否成功的“ If Then Else”
entact，否则登录失败。鉴于发生了“ If Then Else”
登录后，这完全令人困惑。
B.判断EIN是否在数据库中的唯一方法是查看
innerText并查看EIN是否出现在页面上，原因是
查询。这是行不通的，即当
（在测试中）我连续两次查询相同的EIN。 （我受到了打击
第二个EIN。）
C.在循环中，我得到不一致的错误91（对象变量不是
组）。有时循环完成；有时会挂起，但永远不会挂
同一个地方。
我的代码如下（尽管我不得不更改URL）：
Option Explicit
Sub FillFromWorkbookTest()
On Error GoTo ErrHandler
Const cURL = "https://www.someURL.com/LoginPage.jsp"
Const cUsername = "myUSERNAME"
Const cPassword = "myPASSWORD"
Dim IE As Object
Dim Doc As HTMLDocument
Dim LoginForm As HTMLFormElement
Dim UsernameInput As HTMLInputElement
Dim PasswordInput As HTMLInputElement
Dim LoginButton As HTMLInputButtonElement
Dim SearchForm As HTMLFormElement
Dim EINInput As HTMLInputElement
Dim SearchButton As HTMLInputButtonElement
Dim cEIN As String
Dim BotRow As Long
Dim EINRange As Range
Dim c As Variant
Dim i As Integer
Dim EINCheck As String
Dim EINCount As Integer
'## Open Browser & go to Admin Module, and Login
Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True
IE.Navigate cURL
'## Wait for Adimn Module to load
Do Until IE.ReadyState = 4
DoEvents
Loop
'## Get the HTML Document of Admin Module login page (cURL)
Set Doc = IE.document
'## Get Admin Module login form
Set LoginForm = Doc.forms("f1")
'## Get Username input field and populate it
'## HTML: <input id=EIN type=text tabindex=3 size=9 maxlength=9 name=EIN title="Admin Code">
Set UsernameInput = LoginForm.elements("EIN")
UsernameInput.Value = cUsername
'## Get Password input field and populate it
'## HTML: <input id=PIN type=password tabindex=4 size=8 maxlength=8 name=PIN title="PIN">
Set PasswordInput = LoginForm.elements("PIN")
PasswordInput.Value = cPassword
'## Submit LoginForm
'## HTML: <input type=submit value=Login tabindex=5 title="Login"> (no onClick attribute; no element)
LoginForm.submit
Do Until IE.ReadyState = 4
DoEvents
Loop
'## Get the HTML Document of the new page
Set Doc = IE.document
'## Determine if login succeeded
If InStr(Doc.body.innerText, "Invalid Login.") = 0 Then
MsgBox "Login successful."
Else
MsgBox "Login failed."
End If
Debug.Print "Current URL: " & IE.LocationURL
'## Navigate to Global Change and reset HTML Document
IE.Navigate "https://www.someURL.com/LOGGED_IN/SomePage.jsp"
Do Until IE.ReadyState = 4
DoEvents
Loop
Set Doc = IE.document
'## Find last row in spreadsheet
BotRow = Worksheets("Sheet1").Range("A1").End(xlDown).Row
Set EINRange = Range("A1:A" & BotRow)
'## Set loop counter variable
i = 0
'## Cycle through IRS-identified EINs
For Each c In EINRange.Cells
cEIN = c.Value
i = i + 1
'## Get Admin Module login form
Set SearchForm = Doc.forms(0)
'## Get EIN input field and populate it
'## HTML: <input type="text" id=EIN name=EIN title="Enter charity EIN" maxlength=9 size=9 tabindex=11 >
Set EINInput = SearchForm.elements("EIN")
EINInput.Value = cEIN
'## Submit SearchForm
'## HTML: <input type="submit" value="Search" tabindex=15 title="Click here to search charity application" class="black_bold"
'## onclick="if (btn_OnClick(EIN,CODE)) {document.f1.action='SomeOther.jsp'; document.f1.submit(); return true;} else return false;" >
'## (has onClick attribute)
Set SearchButton = Doc.body.getElementsByTagName("table")(2). _
getElementsByTagName("tr")(0). _
getElementsByTagName("td")(0). _
getElementsByTagName("input")(2)
SearchButton.Click
Do Until IE.ReadyState = 4
DoEvents
Loop
'## Get the HTML Document of the new page
Set Doc = IE.document
'## Find EIN string on resulting page; Some number if found; Null if not
EINCheck = Doc.body.getElementsByTagName("table")(3).innerText
EINCount = InStr(1, EINCheck, cEIN, 1)
MsgBox EINCount
'## Determine which EINs are CFC charities
If InStr(1, EINCheck, cEIN, 1) = 0 Then
Worksheets("Sheet1").Range("F" & i).Value = "NO"
Else
Worksheets("Sheet1").Range("F" & i).Value = "YES"
End If
Next c
ErrHandler:
'## Cleanup
MsgBox "Error" & Err.Number & ": " & Err.Description
Set IE = Nothing
Set Doc = Nothing
Set LoginForm = Nothing
Set UsernameInput = Nothing
Set PasswordInput = Nothing
Set LoginButton = Nothing
Set SearchForm = Nothing
Set EINInput = Nothing
Set SearchButton = Nothing
End Sub
有什么建议？
最佳答案
使用“等待直到IE准备就绪”以下代码，我发现了更好的成功
Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Public Function IEWait(p_ieExp As InternetExplorer)
'this should go from ready-busy-ready
Dim initialReadyState As Integer
initialReadyState = p_ieExp.ReadyState
'wait 250 ms until it's done
Do While p_ieExp.Busy Or p_ieExp.ReadyState <> READYSTATE_COMPLETE
Sleep 250
Loop
End Function
你会这样称呼它
IEWait IE 'your internet explorer is named "IE"
仅使用“就绪”条件之一时，我遇到了太多古怪的错误。在将我的“就绪”检查修改为该方法后，这几乎已100％消失了。有时就绪状态不能准确反映该状态。
关于第一个问题，请使用上面引用的
Sleep方法，在每个命令之前先添加Sleep 1000左右，以确认问题出在逻辑上，而不是IE加载速度太慢。或逐步调试器。您所描述的内容听起来与我部分加载IE并随后继续执行代码时遇到的一些问题非常相似。