Object Variable or With Block Variable Not Set

[Atuor]

Hello! This has been stumping me for about a week now. I'm attempting to create a spreadsheet that can pull the table data from HP's Network Node Manager automatically based off the MAC address populated in the field. The script is now able to load the correct page but I'm getting the above error on:

aTD = Doc.getElementsByTagName("td")(0).innerText

---

Below is the entire script.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Row = Range("MAC_Address").Row And _
Target.Column = Range("MAC_Address").Column Then
Dim IE As New InternetExplorer


IE.Visible = True
IE.navigate "https://nnm/nnm/protected/switchportresults.jsp?endNodeName=" & Range("MAC_Address").Value
Do
DoEvents
Loop While IE.readyState = READYSTATE_COMPLETE
Dim Doc As HTMLDocument
Set Doc = IE.Container
Dim aTD As String
aTD = Doc.getElementsByTagName("td")(0).innerText
IE.Quit
Dim aRD As Variant
sTD = Split(aTD, ",")
Range("Interface").Value = sTD(1)
Range("Switch").Value = sTD(2)
Range("Vlan").Value = sTD(0)
MsgBox tRD
End If
End Sub

 

[Atuor]

Typo not affecting question - Dim aRD - should be sTD

 

[John_w]

Try:

Set Doc = IE.Document

Please use CODE tags when posting VBA code - the # icon in the message box.

 

[Atuor]

Thanks, I'll try that. In previous attempts IE.Document has been used.

 

[Atuor]

Okay, changed it to

Set Doc = IE.document

Am now getting automation error/unspecified error. Run-Time error 80004005

 

[Atuor]

Not sure if its the browser, But I'm using IE 11.09600.18282.
Available References:
MS Excel 12.0 Object Library
MS HTML Object Library
MS Internet Controls
OLE Automation
Microsoft Office 12.0 Object Library.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
  
    If Target.Row = Range("MAC_Address").Row And _
    Target.Column = Range("MAC_Address").Column Then
        Dim IE As New InternetExplorerMedium
        
        
        IE.Visible = True
        IE.navigate "https://nnm/nnm/protected/switchportresults.jsp?endNodeName=" & Range("MAC_Address").Value
        Do
            DoEvents
        Loop While IE.readyState = READYSTATE_COMPLETE
        Dim Doc As HTMLDocument
        Set Doc = IE.document
        Dim aTD As String
        aTD = Doc.getElementsByTagName("td")(0).innerText
        IE.Quit
        Dim sTD As Variant
        sTD = Split(aTD, ",")
        Range("Interface").Value = sTD(1)
        Range("Switch").Value = sTD(2)
        Range("Vlan").Value = sTD(0)
        MsgBox aTD
    End If
End Sub

 

[John_w]

Your references are correct.

But your IE wait loop is wrong: it isn't waiting until IE is ready and therefore IE.Document isn't available or complete at the point where you assign it to the Doc variable. Try instead Loop Until...

I normally wait for IE like this:

    With IE
        .Visible = True
        .Navigate URL
        While .Busy Or .ReadyState <> READYSTATE_COMPLETE: DoEvents: Wend  'sometimes only 1 of Busy or ReadyState is checked 
        While .Document.ReadyState <> "complete": DoEvents: Wend   'may not be needed
        Set HTMLdoc = .Document
    End With

 

[Atuor]

Getting a runtime error now

Private Sub Worksheet_Change(ByVal Target As Range)
  
    If Target.Row = Range("MAC_Address").Row And _
    Target.Column = Range("MAC_Address").Column Then
        Dim IE As New InternetExplorer
        
        
    With IE
        .Visible = True
        .navigate "https://nnm/nnm/protected/switchportresults.jsp?endNodeName=" & Range("MAC_Address").Value
        While .Busy Or .readyState <> READYSTATE_COMPLETE: DoEvents: Wend  'sometimes only 1 of Busy or ReadyState is checked
        Set Doc = .document
    End With
        Dim aTD As String
        aTD = Doc.getElementsByTagName("td")(0).innerText
        IE.Quit
        Dim aRD As Variant
        sTD = Split(aTD, ",")
        Range("Interface").Value = sTD(1)
        Range("Switch").Value = sTD(2)
        Range("Vlan").Value = sTD(0)
        MsgBox tRD
    End If
End Sub

 

[John_w]

Which line causes the error? Click Debug on the error message and the errant line will be highlighted in yellow.

 

[Atuor]

Which line causes the error? Click Debug on the error message and the errant line will be highlighted in yellow.

Set Doc = .document