CODEWITHSUNDEEP
excelvbaweb-scrapingintranet
pexpex223
pexpex223

Reputation: 371

Web-scraping on intranet

I wrote a VBA code to scrape data from my company's intranet.

Issues:

The below error occurs:

Run-time error '91':
object variable or with block variable not set

It happens on:

myPoints = Trim(Doc.getElementsByName("price")(0).getAttribute("value"))

When I debug it and run line by line, it can retrieve all the values.

Input and Output:

I input multiple product ID on column B and retrieve data on column C:
Column B = product ID
Column C = price

HTML:

<td id="myPower_val_9" style="visibility: visible;">
    <input type="text" disabled="disabled" value="300" name="price"></input>
</td>

VBA:

Sub Button1_Click()

Dim ie As Object
Dim r As Integer
Dim myPoints As String
Dim Doc As HTMLDocument

Set ie = New InternetExplorerMedium

For r = 2 To Range("B65535").End(xlUp).Row

    With ie
        .Visible = 0

        .navigate "www.example.com/product/" & Cells(r, "B").Value

        Do Until .readyState = 4
            DoEvents
        Loop

    End With

    Set Doc = ie.document

    myPoints = Trim(Doc.getElementsByName("price")(0).getAttribute("value"))
    Cells(r, "C").Value = myPoints

Next r

End Sub

Have I missed an error handler?

Upvotes: 2

Views: 1814

Answers (3)

QHarr
QHarr

Reputation: 84465

You could also loop until element is set (add a timeout clause as well)

Dim a As Object
Do
    DoEvents
    On Error Resume Next 
    Set a = Doc.getElementsByName("price")
    On Error GoTo 0
Loop While a Is Nothing

Upvotes: 1

Tmdean
Tmdean

Reputation: 9299

You need to wait for the document to be fully rendered and the DOM available before accessing any elements. ie.ReadyState changes to READYSTATE_COMPLETE once the page connects and starts loading. The reason that your code works when debugging is that in the couple of seconds it takes for you to start working with the debugger, the page finishes loading.

With ie
   .Visible = True
   .Navigate "www.example.com/product/" & Cells(r, "B").Value

   Do Until .ReadyState = READYSTATE_COMPLETE
       DoEvents
   Loop
   Do Until .Document.ReadyState = "complete"
       DoEvents
   Loop
End With

I would also recommend that you make the ie Window visible, at least while you're developing. Once you've got your functionality complete and debugging, you can make the window invisible. Keep in mind if you forget to close your invisible IE windows when your code finishes, your users will end up with runaway iexplore.exe processes.

Upvotes: 3

silentsurfer
silentsurfer

Reputation: 2428

If you only want to ignore the error and continue with the next iteration, use this modified code:

Sub Button1_Click()

Dim ie As Object
Dim r As Integer
Dim myPoints As String
Dim Doc As HTMLDocument


Set ie = New InternetExplorerMedium


For r = 2 To Range("B65535").End(xlUp).Row

With ie
  .Visible = 0

  .navigate "www.example.com/product/" & Cells(r, "B").Value

   Do Until .readyState = 4
   DoEvents
   Loop

End With


 Set Doc = ie.document


 'Edit:
 myPoints = ""

 On Error Resume Next
 myPoints = Trim(Doc.getElementsByName("price")(0).getAttribute("value"))
 On Error Goto 0

 Cells(r, "C").Value = myPoints

Next r

End Sub

Upvotes: 1

Related Questions