How to access a value that was placed via Javascript from a site via VBA?

Question

I’m trying to access the values that appear on the site: http://www2.bmf.com.br/pages/portal/bmfbovespa/boletim1/BoletimOnline1.asp?caminho=&pagetype=pop&Acao=BUSCA&cboMercadoria=DI1

I could not access the values by direct hyperlink in excel, nor creating a querytable via VBA. Of the two modes the values do not return. Searching the internet, I saw that the values that are inputados via Javascript can not be removed this way, even because the values do not have an ID. Well, looking at the source code I can see how the values are inputados by a Script, but I have no idea how to bring these values from the site to Excel via VBA.

Could someone help me?

Follow the excerpt of the source code where the values are entered, are in bold. When you go to the site the values will be different from the ones below because the values are stock quotes.

<SCRIPT LANGUAGE='JScript'>
 tab_A = tab_A + 'TR'
            "tab_C = tab_C + '<TD ALIGN="right" CLASS="tabelaConteudo1">13,650</TD>';
            tab_C = tab_C + '<TD ALIGN="right" CLASS="tabelaConteudo1">13,640</TD>';
            tab_C = tab_C + '<TD ALIGN="right" CLASS="tabelaConteudo1">13,650</TD>';
            tab_C = tab_C + '<TD ALIGN="right" CLASS="tabelaConteudo1">13,643</TD>';
            tab_C = tab_C + '<TD ALIGN="right" CLASS="tabelaConteudo1">13,641</TD>';
            tab_C = tab_C + '<TD ALIGN="right" CLASS="tabelaConteudo1">13,641</TD>';
            tab_C = tab_C + '<TD ALIGN="right" CLASS="tabelaConteudo1">13,649</TD>';"

...
            </SCRIPT>

Answer 1

The problem, in this case, is that values are placed on the page by a script executed afterward that the page loads, by the browser engine.

Then, you will have to use a browser. Luckily, there is one that you can use and manipulate through the VBA. It is the Activex Webbrowser control, which is nothing more than an encapsulated version of Internet Explorer, operating as if it were IE7.

You can do what has been suggested here, or create a Userform and put Webbrowser on it.

The important thing is to navigate to the page that interests you. You can do this with this command:

WebBrowser1.Navigate("http://www2.bmf.com.br/pages/portal/bmfbovespa/boletim1/BoletimOnline1.asp?caminho=&pagetype=pop&Acao=BUSCA&cboMercadoria=DI1")

You will have to create a Sub to manipulate the event WebBrowser.DocumentCompleted, that will fire when the page is finished loading, and access the property WebBrowser.Document return an object that gives you access to the DOM (Document Object Model) of that page, allows you to read (and even change) the HTML elements and their states.

To do this, you’ll have to scan the page’s HTML code to know what elements you should read, what values you should change and what DOM events you need to shoot to get what you need.

Answer 2

I couldn’t open the page here. Anyway, from what I saw in your example, the variable is public, right?

If so, by the method InternetExplorer.Document.parentWindow.execScript, you can create an HTML element, insert the variable text in its HTML and then access this element with the VBA.

If you’re going to do it that way, I think it would look something like this:

  Dim IE as New InternetExplorer
  Dim Doc as HTMLDocument

  IE.Visible = True
  IE.Navigate2 "http://www2.bmf.com.br/pages/portal/bmfbovespa/boletim1/BoletimOnline1.asp?caminho=&pagetype=pop&Acao=BUSCA&cboMercadoria=DI1"

  Do While IE.readystate  READYSTATE_COMPLETE: DoEvents: Loop

  Set Doc = IE.Document
  Doc.parentWindow.execScript "var p = document.createElement('p'); p.id = 'vba'; p.innerHTML = tab_C"

  MsgBox Doc.getElementById('vba').innerHTML

Now if you can’t/want to use a browser, you can simply make an http request for the page and turn around with its HTML (which will probably contain the text you want).

That’s how I would do it, and use regular expression to manage with HTML.

If you are interested:

    Dim WHTTP As New WinHttpRequest
    Dim HTML As String
    Dim RegEx As New RegExp
    Dim Matches As MatchCollection
    Dim Match As Match
    Dim Valores As New Collection
    Dim i As Double

    With WHTTP
        .Open "GET", "http://www2.bmf.com.br/pages/portal/bmfbovespa/boletim1/BoletimOnline1.asp?caminho=&pagetype=pop&Acao=BUSCA&cboMercadoria=DI1", False
        .send

        If .Status  200 Then
            Debug.Print "Requisição retornou status " & .Status & " (" & .statusText & ")"
            Stop
        End If

        HTML = .responseText
    End With
    Set WHTTP = Nothing

    With RegEx
        .Global = True
        .IgnoreCase = False
        .MultiLine = True
        .Pattern = "\d+(,?)\d{3}(?=)"

        Set Matches = .Execute(HTML)
    End With

    If Matches.Count > 0 Then
        For i = 0 To Matches.Count - 1
            Set Match = Matches.Item(i)

            Valores.Add Match.Value
        Next i
    End If

At the end of this code, the collection Valores will have all results matching the regular expression \d+(,?)\d{3}(?=</TD>)