EXCEL VBA scraping webpage

[timmy2020]

having a few issues getting an excel vba to scrape a webpage.

I require a vba to do the following

1. visit the url on the excel sheet - There is a whole list of 100 URL's.
2. extract 3 data sets
3. populate 3 columns in the excel sheet
4. Loop to next url in list.

I have managed to extract the data i need and get it into the immediate view window in but I am struggling to set the value of the URL from the cell and loop down the list.

I wish i could share the website but there is a login and password access required.

i have put +++++ where the url should be.

any pointers or help i will be grateful for

Sub Browsetosite()

    Dim IE As New SHDocVw.InternetExplorer
    Dim HTMLDoc As MSHTML.HTMLDocument
    Dim HTMLInput As MSHTML.IHTMLElement
    Dim HTMLTrs As MSHTML.IHTMLElementCollection
    Dim HTMLTr As MSHTML.IHTMLElement
    Dim rows As Long
    

    IE.Visible = True
    
    IE.Navigate "+++++", False
    
    
    Do While IE.ReadyState <> READYSTATE_COMPLETE
    Loop
    
    Set HTMLDoc = IE.Document
    
  
    Set HTMLTrs = HTMLDoc.getElementsByTagName("td")
        
    For Each HTMLTr In HTMLTrs


   ' Debug.Print HTMLTr.getAttribute("id"), ",", HTMLTr.innerText, HTMLTr.innerHTML
                     
    'code below find eb number title and code and assigns it to row E1, F1 and G1
    If HTMLTr.getAttribute("id") = "ctl00_bodyContents_td_displayPageContainer_props_prefix_0" Then
    Range("E1").Value = (HTMLTr.innerText)
    End If
    
    If HTMLTr.getAttribute("id") = "ctl00_bodyContents_td_displayPageContainer_props_title_0" Then
    Range("F1").Value = (HTMLTr.innerText)
    End If
    
    If HTMLTr.getAttribute("id") = "ctl00_bodyContents_td_displayPageContainer_props_Review_and_Acceptance_Status_2" Then
    Range("G1").Value = (HTMLTr.innerText)
    End If
    
    'Debug.Print HTMLTr.innerText
    
    Next HTMLTr

End Sub

[Pepe Le Mokko]

Administrative Note:

We would very much like to help you with your query, however you need to include code tags around your code. (not "quote" tags as you did)

Please take a moment to add the tags. Posting code between tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

I did it for you this time

[k1dr0ck]

lets say your URL list in column A starting in A2
try

Sub Browsetosite()

    Dim IE As New SHDocVw.InternetExplorer
    Dim HTMLDoc As MSHTML.HTMLDocument
    Dim HTMLInput As MSHTML.IHTMLElement
    Dim HTMLTrs As MSHTML.IHTMLElementCollection
    Dim HTMLTr As MSHTML.IHTMLElement
    Dim rows As Long
    Dim x As Integer
    
    NumRows = Range("A2", Range("A2").End(xldown)).Rows.Count
    Range("A2").Select

    For x = 1 To NumRows

    IE.Visible = True
    
    IE.Navigate Activecell.Value, False
    
    
    Do While IE.ReadyState <> READYSTATE_COMPLETE
    Loop
    
    Set HTMLDoc = IE.Document
    
  
    Set HTMLTrs = HTMLDoc.getElementsByTagName("td")
        
    For Each HTMLTr In HTMLTrs


   ' Debug.Print HTMLTr.getAttribute("id"), ",", HTMLTr.innerText, HTMLTr.innerHTML
                     
    'code below find eb number title and code and assigns it to row E1, F1 and G1
    If HTMLTr.getAttribute("id") = "ctl00_bodyContents_td_displayPageContainer_props_prefix_0" Then
    Range("E1").Value = (HTMLTr.innerText)
    End If
    
    If HTMLTr.getAttribute("id") = "ctl00_bodyContents_td_displayPageContainer_props_title_0" Then
    Range("F1").Value = (HTMLTr.innerText)
    End If
    
    If HTMLTr.getAttribute("id") = "ctl00_bodyContents_td_displayPageContainer_props_Review_and_Acceptance_Status_2" Then
    Range("G1").Value = (HTMLTr.innerText)
    End If
    
    'Debug.Print HTMLTr.innerText
    
    Next HTMLTr
    ActiveCell.Offset(1, 0).Select
    Next
End Sub