Get value from web document input element with VBA

Profile picture for pexpex223
pexpex223

I am having difficult to retrieve value 300 from the input named points.

Here's my HTML and VBA code.

HTML:

<!-- language: lang-html --> 
<td id="myPower_val_9" style="visibility: visible;">
    <input type="text" disabled="disabled" value="300" name="points"></input>
</td>

VBA:

<!-- language: lang-vb --> 
Dim ie As Object
Dim myPoints As String

Set ie = CreateObject("InternetExplorer.Application")

With ie
  .Visible = 0
  .navigate "www.example.com"
   
   While .Busy Or .readyState <> 4 
   DoEvents
   Wend

End With

Dim Doc As HTMLDocument
Set Doc = ie.document

myPoints = Trim(Doc.getElementsByTagName("td")(0).getElementById("myPoints").innerText)
Range("A1").Value = myPoints

HTML Code

I'd try working out the code that manipulates the Document Object Model (DOM) in javascript in a web browser so you can make use of better web based debugging tools.

There are several issues here that a console or debugger could help out with:

  • You want to get the element ID myPoints but in HTML it's just called points
  • You want to get the element by ID, but you've only set the name property -
  • As long as name is unique to the element, you don't need to search for a td first
  • As you can see from <input></input>, input elements do not have innerText (the text inside the ><). Instead they have a value attribute
  • The element exposes it's attributes and other data through the properties on the object itself. So you can check the input's value by just looking at .value

Here's a javascript example of what you're trying to do:

<!-- begin snippet: js hide: false --> <!-- language: lang-js --> 
var value = document.getElementsByName("points")[0].value;
console.log(value);
<!-- language: lang-html --> 
<input type="text" disabled="disabled" value="300" name="points" />
<!-- end snippet -->

Open the console (<kbd>F12</kbd>), and you should see 300

VBA

To convert it to VBA code for Excel, just make sure you uses parentheses () for VB arrays instead of square brackets [] for JS arrays:

<!-- language: lang-vb --> 
myPoints = Trim(Doc.getElementsByName("points")(0).Value)

That should work just fine.

References

Since I'm not sure at what point you're failing in VB, also make sure you have all the proper web references in place in your VBA script.

Go to Tools > References > and add "Microsoft HTML Object Library" and "Microsoft Internet Controls":

References

Demo

I created a demo in plunker so there would be a live site to go against instead of example.com.

Paste the following code into excel and everything should work fine:

<!-- language: lang-vb --> 
Public Sub GetValueFromBrowser()
    Dim ie As Object
    Dim url As String
    Dim myPoints As String
    
    url = "http://run.plnkr.co/plunks/6UTb9kHRZ363Ivhh2BPE/"
    Set ie = CreateObject("InternetExplorer.Application")
    
    With ie
      .Visible = 0
      .navigate url
       While .Busy Or .readyState <> 4
         DoEvents
       Wend
    End With
    
    Dim Doc As HTMLDocument
    Set Doc = ie.document
    
    myPoints = Trim(Doc.getElementsByName("points")(0).Value)
    Range("A1").Value = myPoints

End Sub

Output:

output