我坦率地承认我对HTML对象库不太熟悉.
我有一个带有IRS雇主识别号码的电子表格,我必须将其识别为在我的数据库中.我只对这个数据库进行基于Web的访问,其他人编写了HTML并管理数据库.我相信他们的方法过时了,他们的设计实践很差; 但我最终不是数据库管理员,所以我知道什么?因此,我的正常做法是在搜索页面输入EIN并记下结果.
我的Excel宏意味着
登录基于Web的数据库查询站点.
循环通过EIN,注意找到哪些EIN
但是,我有以下问题:
A.登录部分运行良好,但有一个怪癖:我必须离开"If Then Else"验证登录成功(或不成功),否则登录失败.鉴于在登录后发生了"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: Set UsernameInput = LoginForm.elements("EIN") UsernameInput.Value = cUsername '## Get Password input field and populate it '## HTML: Set PasswordInput = LoginForm.elements("PIN") PasswordInput.Value = cPassword '## Submit LoginForm '## HTML: (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: Set EINInput = SearchForm.elements("EIN") EINInput.Value = cEIN '## Submit SearchForm '## HTML: '## (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
有什么建议?