VBA：不一致的错误91在循环w / IE.doc引用 [英] VBA: Inconsistent error 91 in loop w/ IE.doc reference

问题描述

我自由地承认我对HTML对象库没有太多的经验。

我有一个包含IRS雇主识别号码的电子表格，我必须将其标识为在我的数据库与否。我只有对这个数据库进行基于Web的访问，其他人写过HTML并管理数据库。我相信他们的方法是陈旧的，他们的设计做法很差;但我最终不是数据库管理员，那我知道什么呢？因此，我最后一般的做法是在搜索页面上输入EIN并记下结果。

我的Excel宏旨在

1. 登录基于Web的数据库查询站点。




2. 循环浏览EIN，注意找到哪些EIN

但是，我有以下问题：

• A。登录部分工作得很好，但有一个奇怪的是：我必须离开
  验证登录成功（否）
  entact的If Then Else，否则登录失败。鉴于登录后出现If Then Else
  ，这完全令人困惑。


• B。告诉EIN是否在数据库中的唯一方法是查看
  的innerText，并查看是否在
  查询页面上发生EIN。这不工作，即只有当
  （在测试中）时，我才会得到一个肯定的命中，我连续两次查询相同的EIN。 （我获得了
  秒EIN的命中。）


• C。在循环中，我得到不一致的错误91（对象变量不是
  设置）。有时循环完成;有时它挂起，但从来没有在
  的同一个地方。

我的代码如下（虽然我不得不更改URL）：

  Option Explicit 
 Sub FillFromWorkbookTest（）
 
错误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 
 
'##打开浏览器&去管理模块和登录
设置IE = CreateObject（InternetExplorer.Application）
 IE.Visible = True 
 IE.Navigate cURL 
 
'##等待Adimn模块加载
 Do Until IE.ReadyState = 4 
 DoEvents 
循环
 
'##获取管理模块登录页面（cURL）的HTML文档
设置Doc = IE.document 
 
'##获取管理模块登录表单
设置LoginForm = Doc.forms（f1）
 
' ##获取用户名输入字段并填充
'## HTML：< input id = EIN type = text tabindex = 3 size = 9 maxlength = 9 name = EIN title =Admin Code> 
设置UsernameInput = LoginForm.elements（EIN）
 UsernameInput.Value = cUsername 
 
'##获取密码输入字段并填充
'## HTML ：< input id = PIN type = password tabindex = 4 size = 8 maxlength = 8 name = PIN title =PIN> 
设置PasswordInput = LoginForm.elements（PIN）
 PasswordInput.Value = cPassword 
 
'##提交LoginForm 
'## HTML：< input type = submit value = Login tabindex = 5 title =Login> （no onClick属性; no元素）
 LoginForm.submit 
 
直到IE.ReadyState = 4 
 DoEvents 
循环
 
'＃ ＃获取新页面的HTML文档
设置Doc = IE.document 
 
'##确定登录是否成功
如果InStr（Doc.body.innerText，无效登录。）= 0然后
 MsgBox登录成功。 
 Else 
 MsgBox登录失败。 
 End If 
 
 Debug.Print当前网址：& IE.LocationURL 
 
'##导航到全局更改并重置HTML文档
 IE.Navigatehttps://www.someURL.com/LOGGED_IN/SomePage.jsp
 
 Do Until IE.ReadyState = 4 
 DoEvents 
循环
 
设置Doc = IE.document 
 
'##查找最后一行电子表格
 BotRow =工作表（Sheet1）。范围（A1）。结束（xlDown）.Row 
设置EINRange =范围（A1：A& BotRow）
 
'##设置循环计数器变量
i = 0 
 
'##循环通过IRS标识的EIN 
对于每个c在EINRange.Cells 
 
 cEIN = c.Value 
i = i + 1 
 
'##获取管理模块登录表单
设置SearchForm = Doc.forms（0）
 
'##获取EIN输入字段并填充
'## HTML：< input type =textid = EIN name = EIN title =Enter charity EINmaxlength = 9 size = 9 tabindex = 11> 
设置EINInput = SearchForm.elements（EIN）
 EINInput.Value = cEIN 
 
'##提交SearchForm 
'## HTML：< input type =submitvalue =Searchtabindex = 15 title =点击此处查找慈善机构申请class =black_bold
'## onclick =if（btn_OnClick（EIN，CODE））{document.f1 .action ='SomeOther.jsp'; document.f1.submit（）; return true;} else return false; > 
'##（有onClick属性）
 
设置SearchButton = Doc.body.getElementsByTagName（table）（2）。 _ 
 getElementsByTagName（tr）（0）。 _ 
 getElementsByTagName（td）（0）。 _ 
 getElementsByTagName（input）（2）
 SearchButton.Click 
 
直到IE.ReadyState = 4 
 DoEvents 
循环
 
'##获取新页面的HTML文档
设置Doc = IE.document 
 
'##在结果页面上查找EIN字符串;一些数字如果发现; Null如果不是
 EINCheck = Doc.body.getElementsByTagName（table）（3）.innerText 
 EINCount = InStr（1，EINCheck，cEIN，1）
 MsgBox EINCount 
 
'##确定哪些EIN是CFC慈善机构
如果InStr（1，EINCheck，cEIN，1）= 0然后
工作表（Sheet1）。范围（F& i ）.Value =NO
 Else 
工作表（Sheet1）。范围（F& i）.Value =YES
如果
 
下一步c 
 
 ErrHandler：
'##清理
 MsgBoxError&错误编号& ：& Err.Description 
设置IE = Nothing 
设置Doc = Nothing 
设置LoginForm = Nothing 
设置UsernameInput = Nothing 
设置PasswordInput = Nothing 
设置LoginButton =没有
设置SearchForm =没有
设置EINInput =没有
设置SearchButton =没有
 
 End Sub 
  

任何建议？

解决方案

以下为等待直到IE准备就绪

 公共声明Sub Sleep Libkernel32（ByVal dwMilliseconds As Long）
公共功能IEWait（p_ieExp As InternetExplorer）
 
'这应该从准备就绪
 Dim initialReadyState As Integer 
 initialReadyState = p_ieExp.ReadyState 
 
'等待250毫秒，直到完成
尽管p_ieExp.Busy或p_ieExp.ReadyState<> READYSTATE_COMPLETE 
睡眠250 
循环
 
结束功能
  

你会称之为

  IEWait IE'你的Internet Explorer被命名为IE
  

我正在运行太多奇怪的错误，只使用准备的条件之一。修改我的准备支票后，这个方法已经近100％了。有时准备状态不能正确地反映状态。

关于您的第一个问题，请使用 Sleep 方法以上，尝试在每个命令之前添加 Sleep 1000 以验证问题在您的逻辑中，而不是在IE加载太慢。或者慢慢地跟着一个调试器。

你所描述的声音听起来非常类似于IE部分加载的一些问题，我的代码将继续执行。

I freely admit that I am not too experienced with the HTML Object Library.

I have a spreadsheet with IRS Employer Identification Numbers that I must identify as being in my database or not. I have only web-based access to this database to which some other people have written the HTML and manage the database. I believe their methods are antiquated and their design practices poor; but I'm not ultimately a database manager, so what do I know? As such, the normal practice on my end is to enter the EIN on the search page and note the result.

My Excel macro is meant to

1. Log in to the web-based database query site.

2. Loop through the EINs, noting which EINs are found

However, I have the following problems:

• A. The login portion works nicely, but with one quirk: I must leave the "If Then Else" that verifies the login was successful (or not) entact, else the login fails. Given that the "If Then Else" occurs after the login, this is totally baffling.
• B. The only way to tell if the EIN is in the database is to look at the innerText and see if the EIN occurs on the page resulting from the query. This isn't working, i.e. I only get a positive hit when (in testing) I query the same EIN twice in a row. (I get a hit on the second EIN.)
• C. In the loop, I get inconsistent Errors 91 (Object variable not set). Sometimes the loop completes; sometimes it hangs, but never in the same place.

My code is as follows (though I've had to change the URLs):

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

Any suggestions?

解决方案

I've found better success using the following for the "wait until IE is ready"

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

You'd call it like

IEWait IE   'your internet explorer is named "IE"

I was running into far too many quirky errors with using only one of the conditions for "ready." After modifying my "ready" checks to that method, this has nearly 100% gone away. Sometimes the ready state does not reflect the state accurately.

Regarding your first questions, using the Sleep method I reference above, try adding Sleep 1000 or so before each of your commands to verify the problem is in your logic, and not in IE loading too slowly. Or stepping through with a debugger slowly.

What you are describing sounds very similar to some issues I've had when IE would partially load and my code would then continue to execute.

这篇关于VBA：不一致的错误91在循环w / IE.doc引用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！