Excel VBA: Get inner text of HTML table td

by

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

screenshot of working demo

More Related Contents:

  1. Export html table data to Excel using JavaScript / JQuery is not working properly in chrome browser
  2. HTML Table to Excel Javascript
  3. Is it possible to use any HTML5 fanciness to export local storage to Excel?
  4. Encoding UTF-8 when exporting HTML table to Excel
  5. Click button or execute JavaScript function with VBA
  6. How do I export multiple html tables to excel?
  7. Fill user name and password in a webpage using VBA
  8. Copy/Paste from Excel to a web page
  9. Double digit 30 seconds countdown
  10. Using HTML5/Canvas/JavaScript to take in-browser screenshots
  11. What’s the effect of adding ‘return false’ to a click event listener?
  12. Custom attributes – Yea or nay?
  13. Way to ng-repeat defined number of times instead of repeating over array?
  14. innerText vs innerHTML vs label vs text vs textContent vs outerText
  15. Connecting to TCP Socket from browser using javascript
  16. How do I check if a cookie exists?
  17. HTML 5 video or audio playlist
  18. Attach an event in a child iframe to a handler in the parent window
  19. Positioning divs in a circle using JavaScript
  20. issue with comparing two numbers in javascript
  21. Display image from blob using javascript and websockets
  22. jQuery text() and newlines
  23. Formatting the date time with Javascript
  24. Print a div using javascript in angularJS single page application
  25. How do I make an “else” in an IE HTML conditional?
  26. html() vs innerHTML jquery/javascript & XSS attacks
  27. Google’s Imageless Buttons
  28. button onclick function firing twice
  29. Why do two web pages have different localStorage? How can I fix this?
  30. D3 put arc labels in a Pie Chart if there is enough space

Leave a Comment