我想使用Excel VBA阅读网页。如何完成此任务?这是可能的吗?
我想使用Excel VBA阅读网页。如何完成此任务?这是可能的吗?
如果你来自Excel 2003,是可以实现这个功能的 - 你可以使用SHDocVw.InternetExplorer
和MSHTML.HTMLDocument
对象调用一个网页,并控制和交互DOM对象。在创建对Microsoft HTML Object Library(...\system32\MSHTML.TLB)和Microsoft Internet Control(...\system32\ieframe.dll)的引用之后,你可以尝试以下示例:
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
您可以使用VBA自动化IE(通过Google有很多示例),或者您可以使用MSHTTP的实例直接获取页面(网络上也有很多示例)。哪种方法更适合您的需求取决于您想要做什么。如果没有更详细的要求,很难说更多。