我想從網頁 http://www.eex.com/en/market-data/power/derivatives-market/phelix-futures 獲得一些數據。
如果我使用舊的InternetExplorer對象( 代碼 below ),我可以遍歷HTML文檔。 但我想使用 XMLHTTP
對象( 第二代碼) 。
Sub IEZagon()
'we define the essential variables
Dim ie As Object
Dim TDelement, TDelements
Dim AnhorLink, AnhorLinks
'add the"Microsoft Internet Controls" reference in your VBA Project indirectly
Set ie = CreateObject("InternetExplorer.Application")
With ie
. Visible = True
. navigate ("[URL]http://www.eex.com/en/market-data/power/derivatives-market/phelix-futures[/URL]")
While ie.ReadyState <> 4
DoEvents
Wend
Set AnhorLinks =. document.getElementsbytagname("a")
Set TDelements =. document.getElementsbytagname("td")
For Each AnhorLink In AnhorLinks
Debug.Print AnhorLink.innertext
Next
For Each TDelement In TDelements
Debug.Print TDelement.innertext
Next
End With
Set ie = Nothing
End Sub
在XMLHTTP對象中使用代碼:
Sub FuturesScrap(ByVal URL As String)
Dim XMLHttpRequest As XMLHTTP
Dim HTMLDoc As New HTMLDocument
Set XMLHttpRequest = New MSXML2.XMLHTTP
XMLHttpRequest.Open"GET", URL, False
XMLHttpRequest.send
While XMLHttpRequest.readyState <> 4
DoEvents
Wend
Debug.Print XMLHttpRequest.responseText
HTMLDoc.body.innerHTML = XMLHttpRequest.responseText
With HTMLDoc.body
Set AnchorLinks =. getElementsByTagName("a")
Set TDelements =. getElementsByTagName("td")
For Each AnchorLink In AnchorLinks
Debug.Print AnhorLink.innerText
Next
For Each TDelement In TDelements
Debug.Print TDelement.innerText
Next
End With
End Sub
我只得到基本的HTML:
<html>
<head>
<title>Resource Not found</title>
<link rel= 'stylesheet' type='text/css' href='/blueprint/css/errorpage.css'/>
</head>
<body>
<table class="header">
<tr>
<td class="CMTitle CMHFill"><span class="large">Resource Not found</span></td>
</tr>
</table>
<div class="body">
<p style="font-weight:bold;">The requested resource does Not exist.</p>
</div>
<table class="footer">
<tr>
<td class="CMHFill"> </td>
</tr>
</table>
</body>
</html>
我想瀏覽表格和coresponding數據。 最後,我想從年到月的時間間隔中選擇:
我很感激你的幫助 ! 謝謝!