Object Variable or With Block Variable Not Set

Atuor

New Member
Joined
Apr 21, 2016
Messages
11
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

[HR][/HR]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
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
5,997
Try:
Code:
Set Doc = IE.Document
Please use CODE tags when posting VBA code - the # icon in the message box.
 

Atuor

New Member
Joined
Apr 21, 2016
Messages
11
Thanks, I'll try that. In previous attempts IE.Document has been used.
 

Atuor

New Member
Joined
Apr 21, 2016
Messages
11
Okay, changed it to

Code:
Set Doc = IE.document
Am now getting automation error/unspecified error. Run-Time error 80004005
 

Atuor

New Member
Joined
Apr 21, 2016
Messages
11
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.

Rich (BB code):
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

MrExcel MVP
Joined
Oct 15, 2007
Messages
5,997
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:
Code:
    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

New Member
Joined
Apr 21, 2016
Messages
11
Getting a runtime error now :(

Code:
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

MrExcel MVP
Joined
Oct 15, 2007
Messages
5,997
Which line causes the error? Click Debug on the error message and the errant line will be highlighted in yellow.
 

Forum statistics

Threads
1,081,850
Messages
5,361,687
Members
400,648
Latest member
mamamia93

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top