使用Excel VBA阅读网页 [英] Reading Web Pages using Excel VBA

问题描述

我想使用Excel VBA阅读网页。如何执行这项任务？这是可能的吗？

I want to read web pages using Excel VBA. How do I carry out this task? Is it even possible?

推荐答案

来自Excel 2003，是的，这是可能的 - 你可以使用 SHDocVw.InternetExplorer 和 MSHTML.HTMLDocument 对象来调用网页，获取对DOM对象的控制权并与之交互。在创建对Microsoft HTML对象库（... \system32\MSHTML.TLB）和Microsoft Internet Control（... \system32\ieframe.dll）的引用后，您可以使用以下示例进行播放：

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

这篇关于使用Excel VBA阅读网页的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！