Home / MS Office Tutorials / MS Excel / Excel Macro to Pull the data from a website

Excel Macro to Pull the data from a website

Please find below the excel vba macro to pull the data from a website based on the html class.

Option Explicit

Sub test()

Dim ele As Object
Dim sht As Worksheet
Dim RowCount As Long
Dim objIE As Object
Dim i As Integer
Dim j As Integer

For i = 2 To 30
For j = 2 To 2
Set sht = Sheets(“Sheet1”)
sht.Range(“A1”).Value = “Company”
RowCount = sht.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row – 1
Set objIE = CreateObject(“InternetExplorer.Application”)
With objIE
.Visible = True
.navigate “https://www.sec.gov/cgi-bin/browse-edgar?CIK=” & Cells(i, j)
Do Until .readyState = 4: DoEvents: Loop

For Each ele In .document.all
Select Case ele.className
Case “companyName”
RowCount = RowCount + 1
sht.Cells(RowCount, “A”).Value = ele.innerText
End Select
Next

End With
objIE.Quit
Set objIE = Nothing
Next j
Next i
End Sub

Leave a Reply