Click here to Skip to main content
15,844,903 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
How to resolve this "Object required" error on Micorsoft edge Windows 10 and Office 2010 in not working VBA Code but not working in Windows 11 in Office 2010 working below code.

What I have tried:

VB
Sub ScrapeTableDataWithHyperlinks()
    Dim URL As String
    Dim ie As Object ' InternetExplorer
    Dim HTMLDoc As Object ' HTMLDocument
    Dim Table As Object ' HTMLTable
    Dim Row As Object ' HTMLTableRow
    Dim Cell As Object ' HTMLTableCell
    Dim i As Long, j As Long
    Dim ws As Worksheet
    Dim startRow As Long, startCol As Long
    Dim myMessage As Variant    
    
    Application.ScreenUpdating = False 
    
    ' Replace this URL with the website URL you want to scrape
    URL = "https://indexes.nasdaqomx.com/Index/Weighting/NDX"

    ' Replace the worksheet name with the name of the sheet you want to use
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    ' Replace the row and column number where you want to start 
    ' writing the table data
    startRow = 5
    startCol = 1
    
    ' Create a new instance of InternetExplorer and navigate to the URL
    Set ie = CreateObject("InternetExplorer.Application")
    ie.Visible = False ' Set to True if you want to see the browser window
    
    ie.navigate URL
    
    ' Wait for the page to load completely

    Do While ie.Busy Or ie.readyState <> 4
        On Error GoTo 0
       DoEvents
    Loop
    
   ' On Error Resume Next
   ' If Err.Number <> 0 Then
   ' myMessage = "Error " & CStr(Err.Number) & " " & Err.Description
    ' do something with this info
    'Err.Clear
    ' Exit Sub
'End If
    
    'Do While ie.readyState = 4: DoEvents: Loop   
    'Do Until ie.readyState = 4: DoEvents: Loop
    
    ' Get the HTML document
    Set HTMLDoc = ie.document
    
    ' Find the table in the HTML document
    Set Table = HTMLDoc.getElementById
    ("weightingsTable") ' Replace "table_id" with the actual ID of the table
    
    ' Check if the table exists
    If Not Table Is Nothing Then
        i = startRow
        
        ' Loop through each row in the table
        For Each Row In Table.getElementsByTagName("tr")
            j = startCol
            
            ' Loop through each cell in the row
            For Each Cell In Row.getElementsByTagName("td")
                ' Check if the cell contains a hyperlink
                If Cell.getElementsByTagName("a").Length > 0 Then
                    ' If the cell contains a hyperlink, 
                    ' write the hyperlink text and URL to the worksheet
                    ws.Cells(i, j).Value = Cell.getElementsByTagName("a")(0).innerText
                    ws.Hyperlinks.Add Anchor:=ws.Cells(i, j), 
                    Address:=Cell.getElementsByTagName("a")(0).href
                Else
                    ' If the cell does not contain a hyperlink, 
                    ' write the cell value to the worksheet
                    ws.Cells(i, j).Value = Cell.innerText
                End If
                
                j = j + 1
            Next Cell
            
            i = i + 1
        Next Row
    Else
        MsgBox "Table not found on the page."
    End If
    
    ' Clean up
    ie.Quit
    Set ie = Nothing
    Set HTMLDoc = Nothing
    Set Table = Nothing
    Set Row = Nothing
    Set Cell = Nothing
    Set ws = Nothing
    
    Application.ScreenUpdating = True
    MsgBox "Table data with hyperlinks has been scraped and 
            written to the worksheet.", vbInformation     
    
End Sub
Posted
Updated 2-Oct-23 5:00am
v2
Comments
Richard Deeming 26-Sep-23 6:57am    
You haven't told us what the exact error message is, and you haven't told us which line it occurs on. All you've done is dumped your code on us, and asked us to fix it for you.

Click the green "Improve question" link, and update your question to include the full error details, and to indicate which line of your code it's thrown from.

"Object required" in VBA means you're trying to use an object that doesn't hold an object or is null. You have to start with the line that threw the error and work backwards to find out why that variable doesn't have the object you expect it to.

Since you didn't say which line the error was thrown on, this is about the most information you're going to get.
 
Share this answer
 
The problem is with
VB
Set ie = CreateObject("InternetExplorer.Application")
Your question states that this works with Windows 11 but not with Windows 10 - I suspect you have that the wrong way around!

If you Google[^] for "internet explorer in Windows 11" you will find there are additional steps you must take to make the code work.
There is also this article which suggests a policy set up I need code that ran in IE to work in Edge. - Microsoft Q&A[^]

Given IE's demise and how the world moves on, it may be time to take a completely different approach than "web scraping" - see this article How To Use Selenium with Excel in 4 Simple Steps - Excel VBA Is Fun[^] or use Power Query - Extract data from a Web page by example - Power Query | Microsoft Learn[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900