CODEWITHSUNDEEP
htmlvbaexcelweb-scraping
pexpex223
pexpex223

Reputation: 371

Get value from web document input element with VBA

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

Upvotes: 3

Views: 49662

Answers (3)

QHarr
QHarr

Reputation: 84465

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:

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")

Upvotes: 1

KyleMit
KyleMit

Reputation: 29927

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

Upvotes: 6

Hubvill
Hubvill

Reputation: 504

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>

Upvotes: 0

Related Questions