VBA: not able to pull value within <input> tag using getelementsbyTagname().Value

Question

This is my first post on stackflow :) I've been Googling VBA knowledge and writing some VBA for about a month.

My computer info:

1.window 8.1

2.excel 2013

3.ie 11

My excel reference

Microsoft Object Library: yes

Microsoft Internet Controls: yes

Microsoft Form 2.0 Object library: yes

Microsoft Script Control 1.0: yes

Issue:

I was trying to retrieve data from internet explorer automatically using VBA. I would like to retrieve the value within an input tag from a id called "u_0_1" which is under a id called "facebook". I am expecting to retrieve the value "AQFFmT0qn1TW" on cell c2. However, it got this msg popped up after I run the VBA "run-time error '91':object variable or with block variable not set. I have been trying this for a couple of weeks using different methods such as,

1.getelementsbyClassname

2.getelementbyid

3.getelementsbyTagname

But it just doesn't work.

url:

http://coursesweb.net/javascript/getelementsbytagname

Below is my VBA code. Could you guys help me out a little bit please?

Private Sub CommandButton1_Click()
Dim ie As Object
Dim Doc As HTMLDocument
Dim getThis As String



Set ie = CreateObject("InternetExplorer.Application")

    ie.Visible = 0


    ie.navigate "http://coursesweb.net/javascript/getelementsbytagname"
    Do
    DoEvents
    Loop Until ie.readyState = 4



    Set Doc = ie.document


    getThis = Trim(Doc.getElementById("u_0_1")(0).getElementsByTagName("input")(0).Value)
    Range("c2").Value = getThis
End Sub

getElementById("u_0_1") returns a single element and not a collection, so you don't need the (0) following it. — Tim Williams
– Tim Williams, Commented Nov 9, 2014 at 21:55
Hi Tim, thanks for you tips. So only getelementbyId returns a single element and the others getelementsby method() returns a collection right? — pexpex223
– pexpex223, Commented Nov 10, 2014 at 16:50
In any HTML document the Id attribute is supposed to be unique, so the method just returns the first one it finds. Classes and tags can obviously occur multiple times: so those methods return collections. The clue is in the method name - if it's getElementsbyXXXX then it returns a collection. — Tim Williams
– Tim Williams, Commented Nov 10, 2014 at 16:56

pexpex223 · Accepted Answer · 2014-11-10 16:49:00Z

Thanks for your help. I have no idea that there is difference between JS and VBA in aspect of getelementsby () methods. And using the loop method to find the id which I find it very useful as well.

I still have some issues to retrieve value from a form or input type. I hope that you could help me or give me some suggestions as well.

Expected Result:

retrieve the value "AQFFmT0qn1TW" and copy it on Cell ("c2") automatically.

Actual Result:

nothing return to Cell ("C2")

Below is the HTML elements.

<form rel="async" ajaxify="/plugins/like/connect" method="post" action="/plugins/like/connect" onsubmit="return window.Event &amp;&amp; Event.__inlineSubmit &amp;&amp; Event.__inlineSubmit(this,event)" id="u_0_1">
<input type="hidden" name="fb_dtsg" value="AQFFmT0qn1TW" autocomplete="off">

Below is the VBA code based on your code.

Private Sub CommandButton1_Click()
Dim ie As Object
Dim Doc As HTMLDocument
Dim Elements As IHTMLElementCollection
Dim Element As IHTMLElement

Set ie = CreateObject("InternetExplorer.Application")

ie.Visible = 0

ie.navigate "http://coursesweb.net/javascript/getelementsbytagname"
Do
DoEvents
Loop Until ie.readyState = 4

Set Doc = ie.document

Set Elements = Doc.getElementsByTagName("input")

For Each Element In Elements
    If Element.name = "fb_dtsg" Then
        Range("c2").Value = Element.innerText
    End If
Next Element

Set Elements = Nothing

End Sub

Cheers.

Rafal Wegner · Accepted Answer · 2014-11-09 21:28:32Z

0

first of all, I can't find in source of website tags you were searching. Anyway, I think you can't chain getElementById.getElementsByTag as in JS. You have to loop through collection of document elements.

Private Sub CommandButton1_Click()
Dim ie As Object
Dim Doc As HTMLDocument
Dim Elements As IHTMLElementCollection
Dim Element As IHTMLElement

    Set ie = CreateObject("InternetExplorer.Application")

    ie.Visible = 0

    ie.navigate "http://coursesweb.net/javascript/getelementsbytagname"
    Do
    DoEvents
    Loop Until ie.readyState = 4

    Set Doc = ie.document

    Set Elements = Doc.getElementsByTagName("ul")

    For Each Element In Elements
        If Element.ID = "ex4" Then
            Sheets(1).Cells(1, 1).Value = Element.innerText
        End If
    Next Element

    Set Elements = Nothing

End Sub

First I'm getting collection of tags "ul", then looping through them for id "ex4". In your case you'd get collection of "input"s then loop for id you want. Finding id which is followed by different id shouldn't be hard, just some if...thens.

If you need further assistant please respond with url in which I can find exactly what you're looking for.

Cheers

answered Nov 9, 2014 at 21:28

Rafal Wegner

684 bronze badges

3 Comments

pexpex223 Over a year ago

Hi Rafal, could you help my below question as well please?

Rafal Wegner Over a year ago

@pexpex223, Element.getAttribute("value") is what you're looking for as your value sits in tag attribute not in innerText. And Element.getAttribute("name")="fb_dtsg" for your If...Then condition. But I'm worried that this might not work for you as I was saying earlier I cant find any input tag with name="fb_dtsg" in website source.

pexpex223 Over a year ago

@ rafal Sorry for the late reply. Thanks for your help and appreciate your time. I will let you know when i find the answer.

Collectives™ on Stack Overflow

VBA: not able to pull value within <input> tag using getelementsbyTagname().Value

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related