Reading Web Pages using Excel VBA

by

Coming from Excel 2003, yes this is possible – you may use the SHDocVw.InternetExplorer and MSHTML.HTMLDocument objects to call a web page, gain control over and interact with the DOM object. After creating references to Microsoft HTML Object Library (…\system32\MSHTML.TLB) and Microsoft Internet Control (…\system32\ieframe.dll) you can play with the following example:

Sub Test()
Dim Browser As SHDocVw.InternetExplorer
Dim HTMLDoc As MSHTML.HTMLDocument

    Set Browser = New SHDocVw.InternetExplorer                     ' create a browser
    Browser.Visible = True                                         ' make it visible
    Application.StatusBar = ".... opening page"
    Browser.navigate "http://www.google.com"                       ' navigate to page
    WaitForBrowser Browser, 10                                     ' wait for completion or timeout

    Application.StatusBar = "gaining control over DOM object"
    Set HTMLDoc = Browser.document                                 ' load the DOM object
    WaitForBrowser Browser, 10

    ' at this point you can start working with the DOM object. Usefull functions are
    ' With HTMLDoc
    '     .getElementByID(string)
    '     .getElementsByTagName(string)
    '     .getElementsByName(string)
    '     .getAttribute(string)
    '     .setAttribute string, string     .... to change field values, click a button etc.
    ' End With

    Application.StatusBar = ""                                      ' time to clean up
    Browser.Quit
    Set HTMLDoc = Nothing
    Set Browser = Nothing
End Sub

Sub WaitForBrowser(Browser As SHDocVw.InternetExplorer, Optional TimeOut As Single = 10)
Dim MyTime As Single

    MyTime = Timer
    Do While Browser.Busy Or (Timer <= MyTime + TimeOut)
        DoEvents
    Loop

    ' if browser still busy after timeout, give up
    If Browser.Busy Then
        MsgBox "I waited for " & Timer - MyTime & " seconds, but browser still busy" & vbCrLf & _
               "I give up now!"
        End
    End If
End Sub

More Related Contents:

  1. Unprotect VBProject from VB code
  2. Assign event handlers to controls on user form created dynamically in VBA
  3. VBA: How to delete filtered rows in Excel?
  4. Round function in Excel, worksheet function vs VBA
  5. Excel VBA Userform – Execute Sub when something changes
  6. simple vba code gives me run time error 91 object variable or with block not set
  7. VBA Function argument list select
  8. Excel VBA wildcard search
  9. VBA macro that search for file in multiple subfolders
  10. So, I have 6 “master” files to then divide into 40 separate files
  11. Getting ScriptControl to work with Excel 2010 x64
  12. Application.Match gives type mismatch
  13. Count and Highlight keywords within phrases
  14. Transpose a range in VBA
  15. Detect merged cells in VBA Excel with MergeArea
  16. Closing Excel Application using VBA
  17. Parse HTML content in VBA
  18. How to add a DocumentProperty to CustomDocumentProperties in Excel?
  19. VBA EXCEL To Prompt User Response to Select Folder and Return the Path as String Variable [duplicate]
  20. Should I turn .CutCopyMode back on before exiting my sub procedure?
  21. Determine whether user is adding or deleting rows
  22. How to use VBA SaveAs without closing calling workbook?
  23. Removing the password from a VBA project
  24. Removing special characters VBA Excel
  25. How can I automate Save as dialog box in IE11 using VBA?
  26. Excel VBA: Get inner text of HTML table td
  27. How do I get a list of unique values from a range in Excel VBA?
  28. How to get selected value in multicolumn listbox
  29. Query my excel worksheet with VBA
  30. How to do a “Save As” in vba code, saving my current Excel workbook with datestamp?

Leave a Comment