WebScrapingMacroCode

[RSHA]

Sub Webscraping()
  
    Dim ieObj As InternetExplorer
    Dim htmlEle As IHTMLElement
    Dim I As Integer
    Dim target As Range
    Dim Lr1 As Long
    Dim Lr2 As Long
    Dim Lp As Long
  
On Error GoTo err:

    Lr1 = ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row
    Lr2 = ThisWorkbook.Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row + 1
  
                check:
                For Lp = 2 To Lr1
                        
                       'create and get access to an instance of IE
                        Set ieObj = New InternetExplorer
                        ieObj.Visible = True
                        ieObj.navigate "MyWeb Site/index?id=" & ThisWorkbook.Sheets("Sheet1").Range("A" & Lp).Value
                       'give the webpage some time to load all content
                        Application.Wait Now + TimeValue("00:00:05")
                       'loop through all the rows in the table
                        For Each htmlEle In ieObj.document.getElementsByClassName("link of html code")(0).getElementsByTagName("tr")
                        If htmlEle.Children(0).textContent = "Total Hours" Then Exit For
                            With ThisWorkbook.Sheets("Sheet2")
                                        .Range("A" & Lr2).Value = htmlEle.Children(0).textContent
                                        .Range("B" & Lr2).Value = htmlEle.Children(1).textContent
                                        .Range("C" & Lr2).Value = htmlEle.Children(2).textContent
                                        .Range("D" & Lr2).Value = htmlEle.Children(3).textContent
                                        .Range("E" & Lr2).Value = htmlEle.Children(4).textContent
                                        .Range("F" & Lr2).Value = htmlEle.Children(5).textContent
                                        .Range("G" & Lr2).Value = ThisWorkbook.Sheets("Sheet1").Range("A" & Lp).Value
                                        Lr2 = Lr2 + 1
                            End With
    Next htmlEle
    ieObj.Visible = Quit
    If Lp = Lr1 Then Exit Sub
    ieObj.Quit
    Next Lp
    err:
    GoTo check

End Sub

Hi Team,

Can Someone look into my code and help me it this macro...

A. CONCEPT: I have two sheets. One is with order numbers where i have to fetch data with this numbers from the web site, which are in Sheet 1 and Sheet 2 is where i need to copy data from web site.
B. Below are the points that i need changes/corrections/additions in my macro code.
1. Once if Lp=Lr1 then "IE should be closed" and at the same time "Sub should exit".
2. I need time frame of this macro i.e., once the scraping/macro starts i need starting time, once the sub exits i need ending time of this macro.
3. Please go ahead and make any necessary changes that you find.

Thank a lot for your help in this.

RSha

 

[Marc L]

Hi !​

1) Use Quit on IE object and Exit Sub statement …​

2) Use Time or Now …​

​

 

[RSHA]

Thank you Marc L for your valuable time on this. It's working fine with the logic you gave.