I'm using excel to get values from a webpage. Among other elements, the HTML contains the following table:
<div id="myDiv">
<table class="myTable">
2 Answers
You seem to have a typo in your variable name. Have you used Option Explicit?
Function myFunction(id)
Call myConnection(id)
Set myDadta = oHtml.getElementById("myDiv").getElementsByClassName("myTable")(0).getElementsByClassName("data")(0)
myFunction = myData.innerText ' <-- This line
End Function
UPDATE
I put a Button on VBA form and the following corrected code that works:
Option Explicit
Dim oHtml, myData
Private Sub CommandButton1_Click()
MsgBox myFunction(0)
End Sub
Function myFunction(id)
Call myConnection(id)
Set myData = oHtml.getElementById("myDiv").getElementsByTagName("Table")(0).getElementsByTagName("td")(1)
myFunction = myData.innerText ' <-- will give 0.51
End Function
Public Sub myConnection(id)
Set oHtml = New HTMLDocument
With CreateObject("WINHTTP.WinHTTPRequest.5.1")
'.Open "GET", "http://www.example.com" & id, False
.Open "GET", "http://localhost/Test/Test.htm", False '<-- this is my local machine; replace appropriately
.send
oHtml.body.innerHTML = .responseText
End With
End Sub
UPDATED CODE TO DEMONSTRATE FUNCTION ON LIVE URL
Option Explicit
Dim oHtml, myData
Private Sub CommandButton1_Click()
MsgBox myFunction(0)
End Sub
Function myFunction(id)
Call myConnection(id)
Set myData = oHtml.getElementById("overallRatios").getElementsByTagName("Table")(0).getElementsByTagName("td")(1)
myFunction = myData.innerText
End Function
Public Sub myConnection(id)
Set oHtml = New HTMLDocument
With CreateObject("WINHTTP.WinHTTPRequest.5.1")
'.Open "GET", "http://www.example.com" & id, False
.Open "GET", "http://www.reuters.com/finance/stocks/overview?symbol=PTI.LS", False
.send
oHtml.body.innerHTML = .responseText
End With
End Sub
12 Comments
Pradeep Kumar
That was just at the first glance. There are more problems with the code. Posting updates...
Siddharth Rout
Good work pradeep :) Wish i could upvote this again. @NunoNogueira: If this is what you were looking for then accept this answer rather than mine ;)
Pradeep Kumar
@NunoNogueira: AFAIK Sid, he never answers questions for points. He has a pet phrase something which says that points can't buy you harley davidson. However I feel that he had answered what your actual quesiton was. My answer is just an update to your edit. BTW avoid using
getElementsByClassName as it won't work on all IE versions and all browsers consistantly. Or you will have to define your own javascript extension methods explicitly.Siddharth Rout
@PradeepKumar: Let's stop confusing the poor guy about what to accept and what not to :) He has made a choice which is right in my opinion and lets stick to it... Oh you remember the phrase :P
Pradeep Kumar
@NunoNogueira: Regarding the slowness, it is because everything is working synchronously and on single thread. Excel VBA is basically single threaded. Had it been .NET or any other language that supports multi-threading, you could have used that capability to speed it up. The best you can do is try putting
DoEvents somewhere inside the loop to stop the UI from freezing. |
This works for me. I have set reference to Microsoft HTML Object Library
BTW you are missing a "in <div id=myDiv">
Option Explicit
Sub Sample()
Dim objIe As Object, xobj As HTMLDivElement
Set objIe = CreateObject("InternetExplorer.Application")
objIe.Visible = True
objIe.navigate "C:\a.htm"
While (objIe.Busy Or objIe.READYSTATE <> 4): DoEvents: Wend
Set xobj = objIe.document.getElementById("myDiv")
Set xobj = xobj.getElementsByClassName("myTable").Item(0)
Set xobj = xobj.getElementsByClassName("data")(0)
Debug.Print xobj.innerText
Set xobj = Nothing
objIe.Quit
Set objIe = Nothing
End Sub
Screenshot:
5 Comments
Siddharth Rout
Did you set a reference to
Microsoft HTML Object Library?