ScrapeURL_ToSheet

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.

CodeFunctionName
What is this?

Public

Tested

Imported
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

ToSheet, ToWB, FromURL

Views 94

Downloads 37

CodeID
DB ID