Excel问题:对象变量或块变量未设置

Question

我有一个为我工作的代码，大约70%的时间，其他时候它抛出错误代码91如果我点击End并重新运行它，它就会正常工作。

该功能是接收输入到excel电子表格单元格中的数据，填充文本框，检查单选按钮，并从网页上的下拉列表中进行选择。

我不能发布一个链接到实际的网页隐私问题，但我希望有人能帮助我，为什么会出现错误？

显示错误的行是drp.selectedIndex = Thisworkbook.sheets("sheet1").Range("L2").。

Sub FillInternetForm()
Dim ie As Object
Set ie = CreateObject("InternetExplorer.Application")

'set references for copying to submitted reps sheet and deleting for next                  rep
Application.ScreenUpdating = False

'create new instance of IE.

ie.navigate "removed for privacy"

'go to web page listed inside quotes
ie.Visible = True


While ie.busy
DoEvents 'wait until IE is done loading page.
Wend

'select onboarding system CRIS or ENS
Set html = ie.document
Dim drp As HTMLFormElement
Set drp =     html.getElementById("ctl00$m$g_62853594_bb4b_4cec_8b5c_17fb6abb735e$ff45_1$ctl00    $DropDownChoice")
drp.selectedIndex = ThisWorkbook.Sheets("sheet1").Range("L2")

'set address nickname based on value
Set drp =     html.getElementById("ctl00$m$g_62853594_bb4b_4cec_8b5c_17fb6abb735e$ff32_1$ctl00    $Lookup")
drp.selectedIndex = ThisWorkbook.Sheets("sheet1").Range("m2")

'set market based on value
Set drp =     html.getElementById("ctl00$m$g_62853594_bb4b_4cec_8b5c_17fb6abb735e$ff6_1$ctl00$    DropDownChoice")
drp.selectedIndex = ThisWorkbook.Sheets("sheet1").Range("e2")

'check Not moved from another partner
ie.document.getElementById("ctl00_m_g_62853594_bb4b_4cec_8b5c_17fb6abb735e_ff46_1_ctl00_ctl01").Click

'input name and ssn based on excel sheet values
ie.document.all("ctl00$m$g_62853594_bb4b_4cec_8b5c_17fb6abb735e$ff3_1$ctl00$ctl00$TextField").Value = ThisWorkbook.Sheets("sheet1").Range("a2")
ie.document.all("ctl00$m$g_62853594_bb4b_4cec_8b5c_17fb6abb735e$ff4_1$ctl00$ctl00$TextField").Value = ThisWorkbook.Sheets("sheet1").Range("b2")
ie.document.all("ctl00$m$g_62853594_bb4b_4cec_8b5c_17fb6abb735e$ff5_1$ctl00$ctl00$TextField").Value = ThisWorkbook.Sheets("sheet1").Range("c2")

'input extra fields based on ICL value
'owner
ie.document.all("ctl00_m_g_62853594_bb4b_4cec_8b5c_17fb6abb735e_ff11_1_ctl00_ctl00_TextField").Value = ThisWorkbook.Sheets("sheet1").Range("j2")
'city
ie.document.all("ctl00$m$g_62853594_bb4b_4cec_8b5c_17fb6abb735e$ff14_1$ctl00$ctl00$TextField").Value = ThisWorkbook.Sheets("sheet1").Range("f2")
'state
ie.document.all("ctl00$m$g_62853594_bb4b_4cec_8b5c_17fb6abb735e$ff15_1$ctl00$ctl00$TextField").Value = ThisWorkbook.Sheets("sheet1").Range("g2")
'address
ie.document.all("ctl00$m$g_62853594_bb4b_4cec_8b5c_17fb6abb735e$ff13_1$ctl00$ctl00$TextField").Value = ThisWorkbook.Sheets("sheet1").Range("i2")
'phone
    ie.document.all("ctl00$m$g_62853594_bb4b_4cec_8b5c_17fb6abb735e$ff10_1$ctl00$ctl00$TextField").Value = ThisWorkbook.Sheets("sheet1").Range("k2")
'zip
ie.document.all("ctl00$m$g_62853594_bb4b_4cec_8b5c_17fb6abb735e$ff16_1$ctl00$ctl00$TextField").Value = ThisWorkbook.Sheets("sheet1").Range("h2")


End Sub

Answer 1

While ie.busy可能是罪魁祸首。使用Loop Until ie.readyState = READYSTATE_COMPLETE代替。

我会使用.Range("L2")将CInt(.Range("L2"))转换为整数。您有对HTML对象库集的引用。您应该继续添加对Microsoft控件的引用。这样，您将获得intellisense和internet常量的优势。如果不想设置引用，将Const READYSTATE_COMPLETE = 4添加到代码并将ie引用更改回

Sub FillInternetForm()
    Dim ie As InternetExplorer
    Dim doc As HTMLDocument

    Set ie = New InternetExplorer

    'set references for copying to submitted reps sheet and deleting for next rep
    Application.ScreenUpdating = False

    'create new instance of IE.

    ie.navigate "removed for privacy"

    'go to web page listed inside quotes
    ie.Visible = True

    Do
        DoEvents
    Loop Until ie.readyState = READYSTATE_COMPLETE

    With Sheets("sheet1")

        'select onboarding system CRIS or ENS
        Set doc = ie.document
        Dim drp As HTMLFormElement
        Set drp = HTML.getElementById("ctl00$m$g_62853594_bb4b_4cec_8b5c_17fb6abb735e$ff45_1$ctl00    $DropDownChoice")
        drp.selectedIndex = CInt(.Range("L2"))

        'set address nickname based on value
        Set drp = HTML.getElementById("ctl00$m$g_62853594_bb4b_4cec_8b5c_17fb6abb735e$ff32_1$ctl00    $Lookup")
        drp.selectedIndex = CInt(.Range("m2"))

        'set market based on value
        Set drp = HTML.getElementById("ctl00$m$g_62853594_bb4b_4cec_8b5c_17fb6abb735e$ff6_1$ctl00$    DropDownChoice")
        drp.selectedIndex = CInt(.Range("e2"))

        'check Not moved from another partner
        doc.getElementById("ctl00_m_g_62853594_bb4b_4cec_8b5c_17fb6abb735e_ff46_1_ctl00_ctl01").Click

        'input name and ssn based on excel sheet values
        doc.all("ctl00$m$g_62853594_bb4b_4cec_8b5c_17fb6abb735e$ff3_1$ctl00$ctl00$TextField").value = .Range("a2")
        doc.all("ctl00$m$g_62853594_bb4b_4cec_8b5c_17fb6abb735e$ff4_1$ctl00$ctl00$TextField").value = .Range("b2")
        doc.all("ctl00$m$g_62853594_bb4b_4cec_8b5c_17fb6abb735e$ff5_1$ctl00$ctl00$TextField").value = .Range("c2")

        'input extra fields based on ICL value
        'owner
        doc.all("ctl00_m_g_62853594_bb4b_4cec_8b5c_17fb6abb735e_ff11_1_ctl00_ctl00_TextField").value = .Range("j2")
        'city
        doc.all("ctl00$m$g_62853594_bb4b_4cec_8b5c_17fb6abb735e$ff14_1$ctl00$ctl00$TextField").value = .Range("f2")
        'state
        doc.all("ctl00$m$g_62853594_bb4b_4cec_8b5c_17fb6abb735e$ff15_1$ctl00$ctl00$TextField").value = .Range("g2")
        'address
        doc.all("ctl00$m$g_62853594_bb4b_4cec_8b5c_17fb6abb735e$ff13_1$ctl00$ctl00$TextField").value = .Range("i2")
        'phone
        doc.all("ctl00$m$g_62853594_bb4b_4cec_8b5c_17fb6abb735e$ff10_1$ctl00$ctl00$TextField").value = .Range("k2")
        'zip
        doc.all("ctl00$m$g_62853594_bb4b_4cec_8b5c_17fb6abb735e$ff16_1$ctl00$ctl00$TextField").value = .Range("h2")
    End With

End Sub

Answer 2

我同意@Thomas的观点，因为这可能是因为文档还没有完全加载。

但我在过去的项目中发现，处理这30%的故障率的最佳方法是在处理或引用元素之前“稍等一段时间”。

尝试在DoEvents调用之前添加一个selectedIndex和一个睡眠命令。看看会不会有什么区别

在模块顶部(在声明任何subs之前)

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

然后在开始设置doc变量之前添加这两行

Doevents
Sleep 2000 ' Sleep two seconds (2000 ms)
Set doc = ie.document

一旦你得到一个对你有用的数字，你就可以使用睡眠值了。

编辑添加的解决方案

一种更可靠的方法是实际运行一个具有DoEvents/睡眠和numTries递增计数器的循环。只有在getElementById的检查没有失败或到达您的maxTries计数器时，它才退出循环。

非常简单的循环，但是如果您需要一个示例，请告诉我

编辑-示例：A等待加载循环

更改此

Set drp = HTML.getElementById("ctl00$m$g_62853594_bb4b_4cec_8b5c_17fb6abb735e$ff45_1$ctl00    $DropDownChoice")
drp.selectedIndex = CInt(.Range("L2"))

To此文：

Dim iTries As Integer
Dim iMaxTries As Integer ' it's better to turn this into a const at top of your sub

iMaxTries = 3
iTries = 0
While (iTries < iMaxTries) And IsNull(HTML.getElementById("ctl00$m$g_62853594_bb4b_4cec_8b5c_17fb6abb735e$ff45_1$ctl00    $DropDownChoice"))
    iTries = iTries + 1
    DoEvents
    Sleep 750 ' try 3/4 second delays to start with
Wend

If iTries = iMaxTries Then
    MsgBox "Did not load HTML element in " & iMaxTries & " tries"
    Exit Sub
End If

' Should all be loaded and ready to process now
Set drp = HTML.getElementById("ctl00$m$g_62853594_bb4b_4cec_8b5c_17fb6abb735e$ff45_1$ctl00    $DropDownChoice")
drp.selectedIndex = CInt(.Range("L2"))