In Excel VBA, what is the way to check if the web page is fully loaded?

by

Functionality of webpages is very different, so there is no solution that will fit to all of them.

Regarding your example, your workaround is a working solution, the code might be like:

Sub TestIE()

    Dim q

    With CreateObject("InternetExplorer.Application")
        .Visible = True
        .Navigate "https://www.homedepot.ca/en/home/p.dry-cloth-refills-32---count.1000660019.html"
        ' Wait IE
        Do While .readyState < 4 Or .Busy
            DoEvents
        Loop
        ' Wait document
        Do While .document.readyState <> "complete"
            DoEvents
        Loop
        ' Wait element
        Do
            q = .document.querySelector(".product-total-price").innerText
            If Left(q, 1) <> "-" Then Exit Do
            DoEvents
        Loop
        .Quit
    End With
    Debug.Print q

End Sub

Anyway, you need to look into the webpage loading process, XHRs and DOM modifications, using browser developer tools (F12). Going that way, you may find that one of the numerous XHRs returns the price in JSON format. It’s logged on network tab of browser developer tools right before the price appearing while the page is loading. That XHR is made by one of the loaded JS, notably after the page loaded event. Try this URL (I just copied it from network tab):

https://www.homedepot.ca/homedepotcacommercewebservices/v2/homedepotca/products/1000660019/localized/9999?catalogVersion=Online&lang=en

So you may just reproduce that XHR and extract the price by splitting:

Sub TestXHR()

    Dim q

    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", "https://www.homedepot.ca/homedepotcacommercewebservices/v2/homedepotca/products/1000660019/localized/9999?catalogVersion=Online&lang=en", False
        .Send
        q = .ResponseText
    End With
    q = Replace(q, " : ", ":")
    q = Split(q, """displayPrice""", 2)(1)
    q = Split(q, """formattedValue"":""", 2)(1)
    q = Split(q, """", 2)(0)
    Debug.Print q

End Sub

But again, there is no common case.

You may also use JSON parser, take a look at some examples.

More Related Contents:

  1. How can I automate Save as dialog box in IE11 using VBA?
  2. Controlling IE11 “Do you want to Open/Save” dialogue window buttons in VBA
  3. Excel VBA “Method ‘Document’ of object ‘IWebBrowser2’ failed”
  4. Use Excel VBA to click on a button in Internet Explorer, when the button has no “name” associated
  5. Excel VBA: auto click and open file from website
  6. delete Autofilter Field resullt using the header Name
  7. Somehow it’s not copying [closed]
  8. VBA macro that search for file in multiple subfolders
  9. Getting ScriptControl to work with Excel 2010 x64
  10. Transpose a range in VBA
  11. Protecting cells in Excel but allow these to be modified by VBA script
  12. Saving excel worksheet to CSV files with filename+worksheet name using VB [duplicate]
  13. Using VBA code, how to export Excel worksheets as image in Excel 2003?
  14. How do I name a spreadsheet automatically, by referencing two cells?
  15. Excel VBA Delete Rows
  16. VBA EXCEL To Prompt User Response to Select Folder and Return the Path as String Variable [duplicate]
  17. Should I turn .CutCopyMode back on before exiting my sub procedure?
  18. VBA: How to delete filtered rows in Excel?
  19. Determine whether user is adding or deleting rows
  20. Excel VBA Formula German/French/Italian/Russian/Dutch/Foreign Function
  21. Iterate over slicer via VBA and select a single item each time
  22. Loop through Excel Sheets
  23. Return a range from A1 to the true last used cell
  24. How to add items to a combobox in a form in excel VBA?
  25. How can I open this VBA workbook faster?
  26. How do I click a link on a web page using Excel VBA?
  27. How do I avoid run-time error when a worksheet is protected in MS-Excel?
  28. How do I get a list of unique values from a range in Excel VBA?
  29. How to get selected value in multicolumn listbox
  30. Query my excel worksheet with VBA

Leave a Comment