• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Scraping data from web to Excel: Code Works When "Stepping Into" (Using F8), But Not When Running as Normal|VBA & Macros|Excel Forum|My Online Training Hub

You are here: Home / Scraping data from web to Excel: Code Works When "Stepping Into" (Using F8), But Not When Running as Normal|VBA & Macros|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumVBA & MacrosScraping data from web to Excel: Co…
sp_PrintTopic sp_TopicIcon
Scraping data from web to Excel: Code Works When "Stepping Into" (Using F8), But Not When Running as Normal
Avatar
Duong Nguyen

New Member
Members
Level 0
Forum Posts: 2
Member Since:
September 18, 2020
sp_UserOfflineSmall Offline
1
September 18, 2020 - 6:03 pm
sp_Permalink sp_Print sp_EditHistory

Hi everyone,

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.

VBA Code:
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
Avatar
Duong Nguyen

New Member
Members
Level 0
Forum Posts: 2
Member Since:
September 18, 2020
sp_UserOfflineSmall Offline
2
September 18, 2020 - 7:39 pm
sp_Permalink sp_Print

Anybody? :((((((((((((((

Please help.

Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1514
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
3
September 19, 2020 - 3:16 pm
sp_Permalink sp_Print

Hi,

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.

Delaying or Pausing VBA

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.

Phil

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: MohamedGadAllah, Jeff Krueger, RAMEZ ATTAR, Kylara Papenfuss, Nada Perovic
Guest(s) 10
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
John Chisholm
vexokeb sdfg
John Jack
Malcolm Toy
Ray-Yu Yang
George Shihadeh
Naomi Rumble
Uwe von Gostomski
Jonathan Jones
drsven
Forum Stats:
Groups: 3
Forums: 24
Topics: 6212
Posts: 27236

 

Member Stats:
Guest Posters: 49
Members: 31889
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.