URL looping is not going well and scraping data is piling up

amo

Board Regular
Joined
Apr 14, 2020
Messages
96
Office Version
  1. 2010
Platform
  1. Windows
Good evening friends, everyone

I have a worksheet where the URL List is filled in range A and the URL worksheet has been prepared, I want to retrieve the data for Price and Stock. I want all the data in the Worksheet List I can take for Price and stock only.

Errors occur when running the iteration process and when there is data that is not retrieved, there are data results that do not match the URL list because the data scraping column that previously did not fill the previous list column

what I want :

1. When there is data that is not successfully retrieved, then the results in the worksheet sheet should be Blank. so it is not filled with further scraping results

2. When the looping process is complete, there is a Done message box


this is my first post and I'm just learning VBA
for your help I thank you



Screenshot_41.jpg
VBA Code:
Sub Extract_URL_List()


Dim i As Integer
    i = 1
    
'SHEET as sheet with URL
    Dim wsSheet As Worksheet, Rows As Long, links As Variant, IE As Object, link As Variant
    Set wb = ThisWorkbook
    Set wsSheet = wb.Sheets("Cek Produk")

'Set IE = InternetExplorer
    Set IE = CreateObject("InternetExplorer.Application")
    On Error Resume Next
    Rows = wsSheet.Cells(wsSheet.Rows.Count, "A").End(xlUp).Row
    links = wsSheet.Range("A2:A500" & Rows)

'IE Open Time per page 5sec and check links on Sheet2 Column A
    With IE
        '.Visible = True
        Application.Wait (Now + TimeValue("00:00:5"))

        For Each link In links
            .navigate (link)
            While .Busy Or .readyState <> 4: DoEvents: Wend

            For i = 1 To 1
             
                On Error Resume Next


                Dim rw As Long
                rw = Sheet1.Cells(Sheet1.Rows.Count, 1).End(xlUp).Row + 1
                'Price
                Sheets("Sheet1").Range("A" & rw).Value = IE.document.getElementsByClassName("_3n5NQx")(o).innerText
                
                'Stock
                Sheets("Sheet1").Range("B" & rw).Value = IE.document.getElementsByClassName("_1FzU2Y")(o).innerText
                
            Next i
            Application.Wait (Now + TimeValue("00:00:3"))
        Next link

    End With


    IE.Quit
    Set IE = Nothing

    
End Sub
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Forum statistics

Threads
1,141,204
Messages
5,704,944
Members
421,372
Latest member
Jamie11

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top