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
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
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