html - Is there a way in VBA to get elements produced by javascript? - Stack Overflow

admin管理员组

文章数量:1026989

I've been doing Google detective work for a few hours (including searching StackOverflow) for a technique to allow targeting of HTML elements produced by JavaScript in VBA.

As an example, I cannot use ie.Document.getElementById(id) on this: .html?page=quote&sym=NGU12&mode=i

However, that method is quite capable of finding elements on static pages, as tested on google.

I've played with simply getting all the InnerText from Document.body and then parsing the file for my desired TD values, but I ran into a roadblock when trying to split two values that are on different lines. As an illustration, the following was understood by Split(..) as "2040":

20
40

From what I understand, the problem with getElementById(id) is that the website's table is generated by JavaScript after the page has loaded, and thus any elements created by that JavaScript cannot be targeted by my VBA code. Is there anyway to have my VBA code see this JavaScript-generated content?

Thank you for your help!

Edit

The VBA code being used:

Function Quote(Market, Parameter)
    Set ie = CreateObject("InternetExplorer.Application")
    ie.Navigate ".html?page=quote&sym=NGU12&mode=i"
    While ie.Busy
        DoEvents
    Wend
    Dim id As String
    id = "dt1_" & Market & "_" & StrConv(Parameter, vbLowerCase)
    Quote = ie.Document.getElementByID(id).InnerText
    ie.Quit
End Function

Basically I try to build an HTML element ID using the column header for the Market. So if, for example the market is "NGU12" and the column header is "Open", the ID built is: "dt1_NGU12_open", which is the ID for the TD element containing the "Open" value for market NGU12.

I've been doing Google detective work for a few hours (including searching StackOverflow) for a technique to allow targeting of HTML elements produced by JavaScript in VBA.

As an example, I cannot use ie.Document.getElementById(id) on this: http://www.kisfutures./electronic.html?page=quote&sym=NGU12&mode=i

However, that method is quite capable of finding elements on static pages, as tested on google..

I've played with simply getting all the InnerText from Document.body and then parsing the file for my desired TD values, but I ran into a roadblock when trying to split two values that are on different lines. As an illustration, the following was understood by Split(..) as "2040":

20
40

From what I understand, the problem with getElementById(id) is that the website's table is generated by JavaScript after the page has loaded, and thus any elements created by that JavaScript cannot be targeted by my VBA code. Is there anyway to have my VBA code see this JavaScript-generated content?

Thank you for your help!

Edit

The VBA code being used:

Function Quote(Market, Parameter)
    Set ie = CreateObject("InternetExplorer.Application")
    ie.Navigate "http://www.kisfutures./electronic.html?page=quote&sym=NGU12&mode=i"
    While ie.Busy
        DoEvents
    Wend
    Dim id As String
    id = "dt1_" & Market & "_" & StrConv(Parameter, vbLowerCase)
    Quote = ie.Document.getElementByID(id).InnerText
    ie.Quit
End Function

Basically I try to build an HTML element ID using the column header for the Market. So if, for example the market is "NGU12" and the column header is "Open", the ID built is: "dt1_NGU12_open", which is the ID for the TD element containing the "Open" value for market NGU12.

• javascript
• html
• ajax
• vba

Share Improve this question Follow edited Aug 5, 2012 at 23:05 Zhihao 14.7k22 gold badges2828 silver badges3636 bronze badges asked Aug 5, 2012 at 22:21 adairdavidadairdavid 41311 gold badge77 silver badges1717 bronze badges 6

• Rather than (or as well as) linking to a full page, can you please show a cut-down version of the VBA code in question? Wouldn't hurt to show a few lines of the JS too... Are you sure you're running your VBA after the elements have been created by JS? – nnnnnn Commented Aug 5, 2012 at 22:33
• Sorry, I'm having trouble understanding what you're doing and what you're trying to do. It sounds like you're using JS to grab the elements on the page, but you're trying to do this with VBA? Is there a way to have JavaScript write the necessary elements for Document.getElementById(..) before it is called? Write where? Before what is called? – Zhihao Commented Aug 5, 2012 at 22:34
• As a side note, I'd like to mention that using innerText will be tricky. For example, Firefox does not support innerText, and uses the W3C standard textContent instead. Whitespace will also be treated differently (e.g. You won't get line breaks with FF textContent), and you will get the script tag contents as well. Apparently Opera (I haven't tried it myself) has innerText, but it's treated as textContent. I feel the only reliable way of getting consistent content is with innerHTML, but then you will be parsing markup, which is a whole other problem. – Zhihao Commented Aug 5, 2012 at 22:38
• I've edited my original post with the VBA code in question. I am not running any JavaScript code myself. The website (linked in OP) generates large tables of data using JS. I am having trouble using getElementByID(id) because it does not retrieve anything on these types of dynamic pages. – adairdavid Commented Aug 5, 2012 at 22:44
• 1 Once the page has pleted loading (and any js-generated elements have been added to the document) then there's no difference between "static" and generated elements as far as the DOM goes: they should be equally accessible to scripting. If you add a slight delay between the page loading and when you try accessing its elements that should be sufficient to make any dynamic content available to you. – Tim Williams Commented Aug 5, 2012 at 23:22

 |  Show 1 more ment

1 Answer 1

Sorted by: Reset to default 1

As Tim has mentioned there should be no difference between statically and dynamically generated content when attempting access. Try this:

Function Quote(Market, Parameter)
    Set ie = CreateObject("InternetExplorer.Application")
    ie.Navigate "http://www.kisfutures./electronic.html?page=quote&sym=NGU12&mode=i"
    While ie.Busy
        DoEvents
    Wend
    Application.Wait Now + TimeSerial(0,0,1) 'Pause 1 second, hopefully
                                             'the dynamic content is 
                                             'generated within this 
                                             'time-frame
    Dim id As String
    id = "dt1_" & Market & "_" & StrConv(Parameter, vbLowerCase)
    Quote = ie.Document.getElementByID(id).InnerText
    ie.Quit
End Function 

There are better ways of halting execution (see Windows Sleep API) but I'll leave that to you if you need more fine-grained control.

Also, if you'd like to test whether a specific id exists after the page has been generated you could load the page manually in IE and add a bookmarklet containing the following as the URL:

javascript:void((function() {var a = document.getElementById(prompt('Please enter an id to test','dt1_NGU12_open')); if (a) {alert('Element found');} else {alert('Element not found')};})())

I've been doing Google detective work for a few hours (including searching StackOverflow) for a technique to allow targeting of HTML elements produced by JavaScript in VBA.

As an example, I cannot use ie.Document.getElementById(id) on this: .html?page=quote&sym=NGU12&mode=i

However, that method is quite capable of finding elements on static pages, as tested on google.

I've played with simply getting all the InnerText from Document.body and then parsing the file for my desired TD values, but I ran into a roadblock when trying to split two values that are on different lines. As an illustration, the following was understood by Split(..) as "2040":

20
40

From what I understand, the problem with getElementById(id) is that the website's table is generated by JavaScript after the page has loaded, and thus any elements created by that JavaScript cannot be targeted by my VBA code. Is there anyway to have my VBA code see this JavaScript-generated content?

Thank you for your help!

Edit

The VBA code being used:

Function Quote(Market, Parameter)
    Set ie = CreateObject("InternetExplorer.Application")
    ie.Navigate ".html?page=quote&sym=NGU12&mode=i"
    While ie.Busy
        DoEvents
    Wend
    Dim id As String
    id = "dt1_" & Market & "_" & StrConv(Parameter, vbLowerCase)
    Quote = ie.Document.getElementByID(id).InnerText
    ie.Quit
End Function

Basically I try to build an HTML element ID using the column header for the Market. So if, for example the market is "NGU12" and the column header is "Open", the ID built is: "dt1_NGU12_open", which is the ID for the TD element containing the "Open" value for market NGU12.

I've been doing Google detective work for a few hours (including searching StackOverflow) for a technique to allow targeting of HTML elements produced by JavaScript in VBA.

As an example, I cannot use ie.Document.getElementById(id) on this: http://www.kisfutures./electronic.html?page=quote&sym=NGU12&mode=i

However, that method is quite capable of finding elements on static pages, as tested on google..

I've played with simply getting all the InnerText from Document.body and then parsing the file for my desired TD values, but I ran into a roadblock when trying to split two values that are on different lines. As an illustration, the following was understood by Split(..) as "2040":

20
40

From what I understand, the problem with getElementById(id) is that the website's table is generated by JavaScript after the page has loaded, and thus any elements created by that JavaScript cannot be targeted by my VBA code. Is there anyway to have my VBA code see this JavaScript-generated content?

Thank you for your help!

Edit

The VBA code being used:

Function Quote(Market, Parameter)
    Set ie = CreateObject("InternetExplorer.Application")
    ie.Navigate "http://www.kisfutures./electronic.html?page=quote&sym=NGU12&mode=i"
    While ie.Busy
        DoEvents
    Wend
    Dim id As String
    id = "dt1_" & Market & "_" & StrConv(Parameter, vbLowerCase)
    Quote = ie.Document.getElementByID(id).InnerText
    ie.Quit
End Function

Basically I try to build an HTML element ID using the column header for the Market. So if, for example the market is "NGU12" and the column header is "Open", the ID built is: "dt1_NGU12_open", which is the ID for the TD element containing the "Open" value for market NGU12.

• javascript
• html
• ajax
• vba

Share Improve this question Follow edited Aug 5, 2012 at 23:05 Zhihao 14.7k22 gold badges2828 silver badges3636 bronze badges asked Aug 5, 2012 at 22:21 adairdavidadairdavid 41311 gold badge77 silver badges1717 bronze badges 6

• Rather than (or as well as) linking to a full page, can you please show a cut-down version of the VBA code in question? Wouldn't hurt to show a few lines of the JS too... Are you sure you're running your VBA after the elements have been created by JS? – nnnnnn Commented Aug 5, 2012 at 22:33
• Sorry, I'm having trouble understanding what you're doing and what you're trying to do. It sounds like you're using JS to grab the elements on the page, but you're trying to do this with VBA? Is there a way to have JavaScript write the necessary elements for Document.getElementById(..) before it is called? Write where? Before what is called? – Zhihao Commented Aug 5, 2012 at 22:34
• As a side note, I'd like to mention that using innerText will be tricky. For example, Firefox does not support innerText, and uses the W3C standard textContent instead. Whitespace will also be treated differently (e.g. You won't get line breaks with FF textContent), and you will get the script tag contents as well. Apparently Opera (I haven't tried it myself) has innerText, but it's treated as textContent. I feel the only reliable way of getting consistent content is with innerHTML, but then you will be parsing markup, which is a whole other problem. – Zhihao Commented Aug 5, 2012 at 22:38
• I've edited my original post with the VBA code in question. I am not running any JavaScript code myself. The website (linked in OP) generates large tables of data using JS. I am having trouble using getElementByID(id) because it does not retrieve anything on these types of dynamic pages. – adairdavid Commented Aug 5, 2012 at 22:44
• 1 Once the page has pleted loading (and any js-generated elements have been added to the document) then there's no difference between "static" and generated elements as far as the DOM goes: they should be equally accessible to scripting. If you add a slight delay between the page loading and when you try accessing its elements that should be sufficient to make any dynamic content available to you. – Tim Williams Commented Aug 5, 2012 at 23:22

 |  Show 1 more ment

1 Answer 1

Sorted by: Reset to default 1

As Tim has mentioned there should be no difference between statically and dynamically generated content when attempting access. Try this:

Function Quote(Market, Parameter)
    Set ie = CreateObject("InternetExplorer.Application")
    ie.Navigate "http://www.kisfutures./electronic.html?page=quote&sym=NGU12&mode=i"
    While ie.Busy
        DoEvents
    Wend
    Application.Wait Now + TimeSerial(0,0,1) 'Pause 1 second, hopefully
                                             'the dynamic content is 
                                             'generated within this 
                                             'time-frame
    Dim id As String
    id = "dt1_" & Market & "_" & StrConv(Parameter, vbLowerCase)
    Quote = ie.Document.getElementByID(id).InnerText
    ie.Quit
End Function 

There are better ways of halting execution (see Windows Sleep API) but I'll leave that to you if you need more fine-grained control.

Also, if you'd like to test whether a specific id exists after the page has been generated you could load the page manually in IE and add a bookmarklet containing the following as the URL:

javascript:void((function() {var a = document.getElementById(prompt('Please enter an id to test','dt1_NGU12_open')); if (a) {alert('Element found');} else {alert('Element not found')};})())

本文标签： htmlIs there a way in VBA to get elements produced by javascriptStack Overflow