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
HTML Code:
https://ClinicalTrials.gov/show/NCT04134676
HTML Code:
https://ClinicalTrials.gov/show/NCT04235296