Submit form and fetch data from website VBA

The Problem (Q-score 3, ranked #89th of 95 in the VBA Core archive)

The scenario as originally posted in 2013

I’m trying to fetch data from this site using VBA in Excel. What I tried to do and what worked was using InternetExplorer object like this:

Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = False
IE.Navigate "http://zertifikate.finanztreff.de"
IE.document.getElementById("USFsecuritySearchDropDown").Value = "DE000BP5TBQ2"
IE.document.getElementById("USFsecuritySearchDropDownForm").submit

Do While IE.Busy Or IE.readyState <> 4  'wait until page is loaded
    Application.Wait DateAdd("s", 1, Now)
Loop
MsgBox IE.document.getElementById("BP5TBQ~30~5").innerHTML

However this worked very slow and didn’t get always the right results. I suspect that sometimes it didn’t wait until webpage was loaded. I tried to look for answers and I found this answer on stackoverflow. Now I’m trying to figure out how to rewrite my macro using MSXML2 and MSHTML. So far I was able to do this :

Dim IE As MSXML2.XMLHTTP60
Set IE = New MSXML2.XMLHTTP60

IE.Open "GET", "http://zertifikate.finanztreff.de", False
IE.send
While IE.ReadyState <> 4
    DoEvents
Wend

Dim HTMLDoc As MSHTML.HTMLDocument
Dim htmlBody As MSHTML.htmlBody

Set HTMLDoc = New MSHTML.HTMLDocument
Set htmlBody = HTMLDoc.body
htmlBody.innerHTML = IE.responseText
HTMLDoc.getElementById("USFsecuritySearchDropDown").Value = "DE000BP5TBQ2"

please, why HTMLDoc has method getElementById and htmlBody doesn’t ? How could I submit form “USFsecuritySearchDropDownForm”. I tried this :

 HTMLDoc.getElementById("USFsecuritySearchDropDownForm").submit

, but it always open new window in my default browser, I would like to have it hidden.
It seems to me that I am missing difference between XMLHTTP60 and MSHTML.HTMLDocument.
If you could please help me or at least show me where I can find this information I would be really thankful…

Why community consensus is tight on this one

Across 95 VBA Core entries in the archive, the accepted answer here holds niche answer (below median) status — meaning voters are unusually aligned on the right fix.

The Verified Solution — niche answer (below median) (+8)

30-line VBA Core pattern (copy-ready)

XMLHTTP sends an http request to the webserver and receives back a response. MSHTML receives a string and renders the HTML. When you use them together, XMLHTTP gets the webserver response and MSHTML puts that response in a form you can use.

I think you don’t need to submit anything. If you go to the site and type in the ticker, you get to a page like

http://zertifikate.finanztreff.de/dvt_einzelkurs_uebersicht.htn?seite=zertifikate&i=22558284&suchbegriff=DE000BP5TBQ2&exitPoint=

That has the ticker in it. You can “GET” that URL directly and get whatever information you need from the html that’s returned. This example gets what I assume is the stock price.

Sub GetPrice()

    Dim xHttp As MSXML2.XMLHTTP
    Dim hDoc As MSHTML.HTMLDocument
    Dim hDiv As HTMLDivElement
    Dim hTbl As HTMLTable

    Const sTICKER As String = "DE000BP5TBQ2"

    Set xHttp = New MSXML2.XMLHTTP

    xHttp.Open "GET", "http://zertifikate.finanztreff.de/dvt_einzelkurs_uebersicht.htn?seite=zertifikate&i=22558284&suchbegriff=" & sTICKER & "&exitPoint="
    xHttp.send

    Do Until xHttp.readyState = 4
        DoEvents
    Loop

    If xHttp.Status = 200 Then
        Set hDoc = New MSHTML.HTMLDocument
        hDoc.body.innerHTML = xHttp.responseText

        'Get the third TD in the first TABLE in the first DIV whose class is 'tape'
        Set hDiv = hDoc.getElementsByClassName("tape").Item(0)
        Set hTbl = hDiv.getElementsByTagName("table").Item(0)
        Debug.Print hTbl.getElementsByTagName("td").Item(2).innerText
    End If

End Sub

Post Example

Sub GetPriceByPost()

    Dim xHttp As MSXML2.XMLHTTP
    Dim hDoc As MSHTML.HTMLDocument
    Dim hDiv As HTMLDivElement
    Dim hTbl As HTMLTable

    Const sTICKER As String = "i=635957"

    Set xHttp = New MSXML2.XMLHTTP

    xHttp.Open "POST", "http://fonds.finanztreff.de/fonds_einzelkurs_uebersicht.htn"
    xHttp.setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
    xHttp.send sTICKER

    Do Until xHttp.readyState = 4
        DoEvents
    Loop

    If xHttp.Status = 200 Then
        Set hDoc = New MSHTML.HTMLDocument
        hDoc.body.innerHTML = xHttp.responseText

        'Get the third TD in the first TABLE in the first DIV whose class is 'tape'
        Set hDiv = hDoc.getElementsByClassName("tape").Item(0)
        Set hTbl = hDiv.getElementsByTagName("table").Item(0)
       Debug.Print hTbl.getElementsByTagName("td").Item(2).innerText
    Else
        Debug.Print xHttp.statusText
    End If

End Sub

Loop-performance notes specific to this pattern

The loop in the answer iterates in process. On a 2026 Office build, setting Application.ScreenUpdating = False and Application.Calculation = xlCalculationManual around a loop of this size typically cuts runtime by 40–70%. Re-enable both in the Exit handler.

When to Use It — classic (2013–2016)

Ranked #89th in its category — specialized fit

This pattern sits in the 93% tail relative to the top answer. Reach for it when your scenario closely matches the question title; otherwise browse the VBA Core archive for a higher-consensus alternative.

What changed between 2013 and 2026

The answer is 13 years old. The VBA Core object model has been stable across Office 2013, 2016, 2019, 2021, 365, and 2024/2026 LTSC, so the pattern still compiles. Changes that might affect you: 64-bit API declarations (use PtrSafe), blocked macros in downloaded files (Mark-of-the-Web), and the shift toward Office Scripts for web-first workflows.