Get value from web document input element with VBA

Question

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

Here's my HTML and VBA code.

HTML:

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

VBA:

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

There is a similar html code in my work place. I needed to retrieve the value from an input type but i tried many times and just didn't work. — pexpex223
– pexpex223, Commented Dec 23, 2014 at 16:53
innerText will give you what's beteween the tags; that's not what you want. You want the value of the "value" attribute. — Casey
– Casey, Commented Dec 23, 2014 at 16:56
so are you saying that if i change "innerText" to "value" it will solve the problem? — pexpex223
– pexpex223, Commented Dec 23, 2014 at 16:57
You probably need to change it to .getAttribute("value") or something like that. — Casey
– Casey, Commented Dec 23, 2014 at 16:58

KyleMit · Accepted Answer · 2014-12-24 13:31:58Z

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:

var value = document.getElementsByName("points")[0].value;
console.log(value);

<input type="text" disabled="disabled" value="300" name="points" />

Open the console (F12), 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:

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:

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

QHarr · Accepted Answer · 2018-06-30 10:03:06Z

1

CSS selector:

Use a CSS selector to get the element of input[name='points']

You don't show enough HTML to know if this is the only on the page. The above says elements with input tag having attribute name whose value is 'points'

CSS query:

VBA:

You apply the CSS selector with .querySelector method of document for a single element; .querySelectorAll for a nodeList of all matching elements i.e. if there is more than one on the page and you get the one of interest by index.

Debug.Print ie.document.querySelector("input[name='points']").getAttribute("value")

edited Jun 30, 2018 at 10:03

answered Jun 29, 2018 at 18:42

QHarr

84.5k14 gold badges58 silver badges105 bronze badges

Comments

Hubvill · Accepted Answer · 2014-12-23 21:43:20Z

You need to use .getAttribute("name of attribute") to get an attributes value. In your case .getAttribute("value") will return 300.

Dim ie As Object
Dim myPoints As String

Set ie = CreateObject("InternetExplorer.Application")

With ie
  .Visible = 1
  .navigate "website URL"

   While .Busy Or .readyState <> 4
   DoEvents
   Wend

End With

Dim Doc As HTMLDocument
Set Doc = ie.document

myPoints = Trim(Doc.getElementsByTagName("td")(0).getElementsByTagName("input")(0).getAttribute("value"))
Range("A1").Value = myPoints

Just on a side note. I don't know much about HTML and maybe someone can elaborate on this more. But if you want to test that HTML code you need to add in the < table> < tr> tags.
Something like this:

<table>
    <tr>
        <td id="myPower_val_9" style="visibility: visible;">
            <input type="text" disabled="disabled" value="300" name="points"></input>
        </td>
    </tr>
</table>

Collectives™ on Stack Overflow

Get value from web document input element with VBA

3 Answers 3

HTML Code

VBA

References

Demo

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

HTML Code

VBA

References

Demo

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related