VBA MACRO get element as ID not working-Data Scrapping

[pedrosc]

Dear all,

I am trying to create a sub that when I have a list of links, access these links and removes a field in the html code and puts in in excel. For some reason I can't understand this is giving an error message on the line I put for get elementID.

The error is "Object variable or with block variable not set".

PS: the way I structured this macro you have to select the cells with links otherwise it won't work. They should be put in column B


Sub webiteration()



Dim nr As Integer
Dim i As Integer
Dim c As Integer
Dim IE As Object
Dim doc As HTMLDocument
Dim myurl As String
Dim sponsor As String




nr = Selection.Rows.Count
c = 1
Set IE = CreateObject("InternetExplorer.Application")


For i = 2 To nr + 1
If ActiveCell <> 0 Then
myurl = ThisWorkbook.Sheets("Sheet1").Range("B" & i).Value

IE.Navigate myurl
IE.Visible = True

sponsor = doc.getElementById("sponsor").innerText
ThisWorkbook.Sheets("Sheet1").Range("C" & i).Value = sponsor

End If
ActiveCell.Offset(1, 0).Select

Next i
End Sub





Examples of links I want to use are here


https://ClinicalTrials.gov/show/NCT04134676

https://ClinicalTrials.gov/show/NCT04235296

[Bob Phillips]

I am no expert on web scraping, but if you change


sponsor = doc.getElementById("sponsor").innerText

to


sponsor = IE.document.getElementById("sponsor").innerText

you should get more joy

[Bob Phillips]

Here is better code, doesn't need the urk=ls in column B to be selected, and selects nothing itself


Sub webiteration()
Dim IE As Object
Dim doc As HTMLDocument
Dim myurl As String
Dim sponsor As String
Dim nr As Long
Dim i As Long

Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True

With ThisWorkbook.Sheets("Sheet2")

nr = .Cells(.Rows.Count, "B").End(xlUp).Row

For i = 2 To nr

If .Cells(i, "B").Value <> vbNullString Then

IE.Navigate .Cells(i, "B").Value
Set doc = IE.document
sponsor = doc.getElementById("sponsor").innerText
.Cells(i, "C").Value = sponsor
End If
Next i
End With

IE.Quit
Set IE = Nothing
End Sub

[pedrosc]

I didn't. Where do you get this error?

[Bob Phillips]

Sorry, it looks like I edited your post rather than adding anew.

I have added a loop to check the page is loaded., Please try this and let me know if and where it fails.


Sub webiteration()
Dim IE As Object
Dim doc As HTMLDocument
Dim ready As Boolean
Dim myurl As String
Dim sponsor As String
Dim nr As Long
Dim i As Long

Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True

With ThisWorkbook.Sheets("Sheet2")

nr = .Cells(.Rows.Count, "B").End(xlUp).Row

For i = 2 To nr

If .Cells(i, "B").Value <> vbNullString Then

IE.Navigate .Cells(i, "B").Value
ready = False
Do

If IE.readyState <> 4 Then

DoEvents
Else

ready = True
End If
Loop Until ready
Set doc = IE.document
sponsor = doc.getElementById("sponsor").innerText
.Cells(i, "C").Value = sponsor
End If
Next i
End With

IE.Quit
Set IE = Nothing
End Sub

[pedrosc]

It worked now thank you! I guess it is the reference of the sheet2 while I was actually using sheet 1

I just wanted to really understand why my code did not work :(

[Bob Phillips]

Sorry, that was my fault, I tested on a workbook with Sheet2.

Your code wasn't working because you referenced a variable doc that you didn't set anywhere, I added in IR.document as the main change, but I did a lot of code tidying as well, then I added the wait.