Html Script tag parameters, retrieve with VBA

[jrajul]

I am using an Access form with a web browser set on a certain webpage.

With the MSHTML library as a reference, I iterate through the node tree to obtain certain elements that I want. For example:

[vba]
command1_click

dim doc as mshtml.htmldocument
set doc=me.webBrowser1.Document

debug.print doc.scripts(1).nodeValue

end sub
[/vba] In the example above, I retrieve the node value from one of the script tags in the web page.


Working through the node tree works well for most properties (or attributes or whatever the proper term is). But it will not work for extracting the variables (aka parameters aka arguments) from inside a script tag.

<SCRIPT type=text/javascript>
var blah blah blah blah
var blah blah blah blah
var blah blah blah blah
var blah blah blah blah

var DBLE_CLK_PARAMS = { 'SKIES': 'cloudy', 'AIR_QUALITY': 'fair' }


var blah blah blah blah
var blah blah blah blah
var blah blah blah blah

</SCRIPT>


Now, with VBA, I want to "debug.print" the value for the skies parameter and the air_quality parameter. I can see the whole thing if I "debug.print" childnode.innerhtml. But I cannot selectively "debug.print" only one of the variables.



I am a newbie in HTML and I know next to nothing about JavaScript. So please break your explanations down for me.


Does anyone know how to get at these parameters inside the script tags in VBA WITHOUT using a whole bunch of text manipulation?

[JP2112]

I don't know what you consider a "whole bunch" of text manipulation, but this worked for me:

[vba]
Sub test()
Dim str As String
str = "var blah blah blah blah" & _
"var blah blah blah blah" & _
" var blah blah blah blah" & _
"var blah blah blah blah" & _
"var DBLE_CLK_PARAMS = { 'SKIES': 'cloudy', 'AIR_QUALITY': 'fair' }" & _
"var blah blah blah blah" & _
"var blah blah blah blah" & _
"var blah blah blah blah"
Dim inBetweenBraces As String
Dim commaSeparatedValues() As String
inBetweenBraces = Split(str, "{")(1)
commaSeparatedValues = Split(Left$(inBetweenBraces, WorksheetFunction.Find("}", inBetweenBraces) - 1), ",")
Debug.Print commaSeparatedValues(0)
Debug.Print commaSeparatedValues(1)
End Sub
[/vba]

Just assign the .InnerHtml to a string and this will parse it. You might also want to consider using a JSON parser:

http://code.google.com/p/vba-json/

[jrajul]

Thanks for replying, JP 2112. I found a shorter way to get the value of the variable.
VBA cannot get the variable, but HTML with the help of Javascript (which the website already uses) can. Yet, VBA can, in fact, create create HTML elements and put instructions inside them. So I insert a new element, tell the element to display the javascript variable. The element's display is something that VBA can detect.


[vba]Dim str as string
Dim elem as new htmlBaseElement
Dim doc as htmlDocument
set doc=webBrowser1.Document

elem.setAttribute "id", "Fido"
set elem= doc.AppendChild "Input"
doc.getElemenentById("Fido").setAttribute "value", DBLE-CLK-PARAMS.SKIES

str=doc.getElementByID("Fido").value
set elem=nothing
set doc=nothing
[/vba]

[JP2112]

You got it!

The only thing I would do is not declare "Dim ... As New". Instead I would do it like this:

[VBA]
Dim elem As htmlBaseElement
Set elem = New htmlBaseElement
[/VBA]

[stanl]

I missed this thread when it first appeared. I was wondering, since IE8 Microsoft included a jQuery Object - so

jQuery = IE.document.parentWindow.jQuery

so essentially (in the context of the thread) you could issue

var = jQuery.DBLE-CLK-PARAMS.SKIES

My question is: while IE has jQuery, does MSHTML?

[jrajul]

My question is: while IE has jQuery, does MSHTML?

In VBA it does.

The html elements from the MSHTML library have a function called query selector. It has similar parameters to jQuery.

You must be mindful of the punctuation rules of both HTML and VBA when using it.