Reads content of Page from URL to a sheet.
This was made specially for certain webpage reading whole table in html (tag < table >), when using this code, make sure you read the HTML output of your page to adjust code to fit data you want to import.
It is reading rows and columns of table with id = octable into sheet specified.
Again, post image is AI-generated.
Sub ScrapeURL_ToSheet(ToSheet, ToWB, FromURL)
Dim ie As Object
Dim url As String
Dim doc As HTMLDocument
Dim tables As Object
Dim table As Object
Dim row As Object
Dim cell As Object
Dim i As Long, j As Long
Dim ws As Worksheet
Set ws = Workbooks(ToWB).Worksheets(ToSheet) ' ThisWorkbook.Worksheets("Sheet1")
' Define the URL of the webpage
url = FromURL ' "https://www.WEBSITE.com/SUBFOLDERS/optionKeys.jsp"
' Create a new instance of the InternetExplorer object
Set ie = CreateObject("InternetExplorer.Application")
' Send a GET request to the URL
ie.Navigate url
i = 1
j = 1
' Wait for the page to load
Do While ie.ReadyState < > 4 Or ie.Busy
DoEvents
Loop
' Get the HTML content of the page
Set doc = ie.document
' Find the table containing the option data
Set tables = doc.getElementsByTagName("table")
For Each table In tables
If table.ID = "octable" Then
' Loop through the rows of the table and extract the data
For Each row In table.Rows
For Each cell In row.Cells
' Write the cell data to the Excel sheet
'ActiveSheet.Cells(i, j).Value = cell.innerText
ws.Cells(i, j).Value = cell.innerText
j = j + 1
Next cell
i = i + 1
j = 1
Next row
Exit For
End If
Next table
' Clean up
ie.Quit
Set ie = Nothing
End Sub
Dim ie As Object
Dim url As String
Dim doc As HTMLDocument
Dim tables As Object
Dim table As Object
Dim row As Object
Dim cell As Object
Dim i As Long, j As Long
Dim ws As Worksheet
Set ws = Workbooks(ToWB).Worksheets(ToSheet) ' ThisWorkbook.Worksheets("Sheet1")
' Define the URL of the webpage
url = FromURL ' "https://www.WEBSITE.com/SUBFOLDERS/optionKeys.jsp"
' Create a new instance of the InternetExplorer object
Set ie = CreateObject("InternetExplorer.Application")
' Send a GET request to the URL
ie.Navigate url
i = 1
j = 1
' Wait for the page to load
Do While ie.ReadyState < > 4 Or ie.Busy
DoEvents
Loop
' Get the HTML content of the page
Set doc = ie.document
' Find the table containing the option data
Set tables = doc.getElementsByTagName("table")
For Each table In tables
If table.ID = "octable" Then
' Loop through the rows of the table and extract the data
For Each row In table.Rows
For Each cell In row.Cells
' Write the cell data to the Excel sheet
'ActiveSheet.Cells(i, j).Value = cell.innerText
ws.Cells(i, j).Value = cell.innerText
j = j + 1
Next cell
i = i + 1
j = 1
Next row
Exit For
End If
Next table
' Clean up
ie.Quit
Set ie = Nothing
End Sub
ToSheet, ToWB, FromURL
Views 94
Downloads 37
CodeID
DB ID