September 18, 2020
I really need your kind support to revise the code.
My job is to get data from a website by filling some search criteria, clicking on search button and pulling necessary information to Excel file.
However, the code is driving me crazy when nothing comes to Excel when I run the code normally but it runs once F8 is activated to run through steps.
Could anybody help me with this issue?
Thanks so much.
Sub PullDataFromWeb() Dim IE As Object, W As Excel.Worksheet Dim doc As HTMLDocument Dim lastRow As Integer, b As Boolean, tmp As String, a2 As String Dim lis, li Dim SearchButton As Object Set W = ThisWorkbook.Sheets("Sheet1") Set IE = VBA.CreateObject("InternetExplorer.Application") IE.Visible = True 'hien cua so IE IE.navigate "http://pus.customs.gov.vn/faces/ContainerBarcode" Do While IE.Busy Or IE.readyState <> 4 'doi IE chay xong Application.Wait DateAdd("s", 1, Now) Loop Set doc = IE.document lastRow = W.Range("B" & W.UsedRange.Rows.Count + 2).End(xlUp).Row 'dong cuoi cung trong cot B container If lastRow < 2 Then GoTo Ends On Error Resume Next For intRow = 2 To lastRow 'tu dong toi dong b = False b = W.Range("I" & intRow).Value Like "[Yy]" If W.Range("B" & intRow).Value <> "" And Not b Then doc.getElementById("pt1:it2::content").Value = W.Range("B" & intRow).Value 'so TK doc.getElementById("pt1:it1::content").Value = W.Range("A" & intRow).Value 'ma DN doc.getElementById("pt1:it3::content").Value = W.Range("C" & intRow).Value 'ma HQuan doc.getElementById("pt1:it4::content").Value = W.Range("D" & intRow).Value 'ngay TK Set SearchButton = doc.getElementsByClassName("btngetdata xfl p_AFTextOnly")(0) SearchButton.Click Do While IE.Busy Or IE.readyState <> 4 Application.Wait DateAdd("s", 1, Now) Loop strFindTrangThaiTK = "" strFindTrangThaiTK = doc.getElementById("pt1:png1").getElementsByTagName("table")(1).Rows(4).Cells(0).innerText a2 = "" a2 = doc.getElementsByClassName("x15p")(0).innerText If LCase(a2) Like "*khai*" And strFindTrangThaiTK = "" Then 'error window pop-up strFindTrangThaiTK = a2 W.Range("E" & intRow) = strFindTrangThaiTK doc.getElementById("d1_msgDlg::close").Click Else W.Range("E" & intRow) = strFindTrangThaiTK End If End If Next Ends: IE.Quit Set IE = Nothing 'Cleaning up Set objElement = Nothing Set objCollection = Nothing Application.StatusBar = "" Application.DisplayAlerts = True Application.ScreenUpdating = True MsgBox "PUS CUSTOMS UPDATED!" End Sub
September 18, 2020
October 5, 2010
Between your 2 posts there just over 90 minutes elapsed. Have some patience, you're not paying for this service, and we all have jobs.
It's a timing issue between IE and VBA. When you step through the code, each line of code executes as it should because the elements/data that it expects to be there, are there. They've had time to load.
When you just run the code, IE isn't ready even though it's reporting as ready/not busy. I had problems when the error dialog box popped up indicating so results were found in the search. Your code didn't click on the button to dismiss this because IE told VBA it was ready and when your code checked for the error message it didn't find it, and proceeded as if everything was ok.
Try rewriting the code so that you are checking for something on the page that you know indicates a successful search.
Or insert a delay into the code, don't rely on IE to report it's ready.
Or use a different browser.
Web Scraping with VBA, Chrome and Selenium
BTW - NEVER turn off error handling unless you handle the error, and always turn it back on again.