Download xml file from url (https) with VBA or VB6

Question

I would like to download this file https://www.omniva.ee/locations.xml with VBA or VB6. I can do it with C# using webClient but i don't know how do it in VBA or VB6. Is it possible without IE API? Because it doesn't work for me:

Internet explorer can't show this page :-(. Can you help me please?

Yes. With URLMon or Binary download for starters. https://stackoverflow.com/a/52245239/6241235 — QHarr, Sep 20 '18 at 13:22
What are your reason for avoiding IE? It is an API approach and you'll never see its face — Sam, Sep 20 '18 at 13:23
You should look at using the Microsoft Internet Transfer Control. — jac, Sep 20 '18 at 17:44

score 0 · Answer 1 · answered Sep 20 '18 at 16:42

Just a simple example:

Sub Test()

    DownloadFile "https://www.omniva.ee/locations.xml", "C:\Test\locations.xml"

End Sub

Function DownloadFile(sURL As String, sPath As String)

    Dim aBody() As Byte

    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", sURL, False
        .Send
        aBody = .ResponseBody
    End With
    With CreateObject("ADODB.Stream")
        .Type = 1 ' adTypeBinary
        .Open
        .Write aBody
        .SaveToFile sPath, 2 ' adSaveCreateOverWrite
        .Close
    End With

End Function

Error ( Run-time error -2146697208 (800c0008) ) occurs when try to send request (.Send) — Alberto Palacio, Sep 30 '18 at 14:07

score 0 · Answer 2 · answered Sep 20 '18 at 21:08

VBScript is pastable into VBA or VB6. You don't specify what you don't like.

This uses IE.

On Error Resume Next
    Set ie = CreateObject("InternetExplorer.Application") 
    msgbox ie.offline
'   ie.offline = true
    ie.Visible = 1
    ie.Silent = 1 
    ie.Navigate2 "http://www.smh.com.au"
    Do
        Msgbox ie.document.readystate()
        wscript.sleep 500           
    Loop Until ie.document.readystate = "complete"
    Msgbox ie.document.body.innertext
    ie.refresh
    ie.quit

This uses MSHTTPXML. The purpose is to show why your URL doesn't work.

Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
Url = "http://www.smh.com.au"

objHTTP.Open "POST", Url, False
objHTTP.setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/63.0.3239.132 Safari/537.36"
objHTTP.setRequestHeader "Referer", "http://finra-markets.morningstar.com/bondSearch.jsp"
objHTTP.setRequestHeader "Content-type", "application/x-www-form-urlencoded"
'objHTTP.send ("count=1&curPage=1&query={'Keywords':[{'Name':'debtOrAssetClass','Value':'3'},{'Name':'showResultsAs','Value':'B'}]}&searchtype=B&sortfield=issuerName&sorttype=1&start=40")
objHTTP.send 
Msgbox objHTTP.Status
Msgbox objHTTP.responseText
Msgbox objHTTP.getAllResponseHeaders

This uses above plus ADODB to save the file to disk.

    Set fso = CreateObject("Scripting.FileSystemObject")
    Set Outp = Wscript.Stdout
    Set wshShell = CreateObject("Wscript.Shell")
    Set ShApp = CreateObject("Shell.Application")
    On Error Resume Next
    Set File = WScript.CreateObject("Microsoft.XMLHTTP")
    File.Open "GET", "http://definitionupdates.microsoft.com/download/definitionupdates/safetyscanner/x86/msert.exe:200", False
    File.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.0; Trident/4.0; SLCC1; .NET CLR 2.0.50727; Media Center PC 5.0; .NET CLR 1.1.4322; .NET CLR 3.5.30729; .NET CLR 3.0.30618; .NET4.0C; .NET4.0E; BCD2000; BCD2000)"
    File.Send
'   If err.number <> 0 then 
        wscript.echo "" 
        wscript.echo "Error getting file" 
        wscript.echo "==================" 
        wscript.echo "" 
        wscript.echo "Error " & err.number & "(0x" & hex(err.number) & ") " & err.description 
        wscript.echo "Source " & err.source 
        wscript.echo "" 
        wscript.echo "HTTP Error " & File.Status & " " & File.StatusText
        wscript.echo    File.getAllResponseHeaders
'   else
        On Error Goto 0
        Set BS = CreateObject("ADODB.Stream")
        BS.type = 1
        BS.open
        BS.Write File.ResponseBody
        BS.SaveToFile ShApp.Namespace(&h10).self.path & "\safetyscanner.exe", 2
        wshshell.Run "c:\users\safetyscanner.exe", 1, False
'   End If

Download xml file from url (https) with VBA or VB6

2 Answers2