Downloading a file in VBA and storing it

18,839

Solution 1

First of all - the link does not work. Second of all: there can be 2 approaches depending on the output of the HTTP request.

If the output is file you can use the code below:

Sub DownloadFile(url As String, filePath As String)

    Dim WinHttpReq As Object, attempts As Integer
    attempts = 3
    On Error GoTo TryAgain
TryAgain:
    attempts = attempts - 1
    Err.Clear
    If attempts > 0 Then
        Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
        WinHttpReq.Open "GET", url, False
        WinHttpReq.send

        If WinHttpReq.Status = 200 Then
            Set oStream = CreateObject("ADODB.Stream")
            oStream.Open
            oStream.Type = 1
            oStream.Write WinHttpReq.responseBody
            oStream.SaveToFile filePath, 2 ' 1 = no overwrite, 2 = overwrite
            oStream.Close
        End If
    End If
End Sub

If the output is a simple text HTML response you can save the output to a file

Function GetXMLHTTPResult(url As String)
    Dim XMLHTTP As Object, attempts As Integer
    attempts = 3
    On Error GoTo TryAgain
TryAgain:
    attempts = attempts - 1
    Err.Clear
    If attempts > 0 Then
        Set XMLHTTP = CreateObject("MSXML2.serverXMLHTTP")
        XMLHTTP.Open "GET", url, False
        XMLHTTP.setRequestHeader "Content-Type", "text/xml"
        XMLHTTP.setRequestHeader "Cache-Control", "no-cache"
        XMLHTTP.setRequestHeader "Pragma", "no-cache"
        XMLHTTP.setRequestHeader "User-Agent", "Mozilla/5.0 (Windows NT 6.1; rv:25.0) Gecko/20100101 Firefox/25.0"
        XMLHTTP.send
        GetXMLHTTPResult = XMLHTTP.ResponseText
    End If
End Function
Sub SaveFile(url)
        res = GetXMLHTTPResult(url)
        Open "C:\res.txt" For Output As #1
        Write #1, res
        Close #1
End Sub

Solution 2

If the file already exists on the server and doesn't have to be built by way of a query etc, you can use an API call like so:

Option Explicit

#If VB7 Then
    Declare PtrSafe Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" _
    (ByVal pCaller As LongPtr, ByVal szURL As String, ByVal szFileName As String, _
    ByVal dwReserved As LongPtr, ByVal lpfnCB As LongPtr) As Long
#Else
    Declare Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" _
    (ByVal pCaller As Long, ByVal szURL As String, ByVal szFileName As String, _
    ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long
#End If

Sub SO()

Dim fileURL As String, saveLocation As String

fileURL = "https://abc.def/geh/servlet/rest/vault?oid=xxx&expr=files.file1"
saveLocation = "C:\Users\bloggsj\desktop\files.file1"

MsgBox "Download completed: " & (URLDownloadToFile(0, fileURL, saveLocation, 0, 0) = 0)

End Sub
Share:
18,839
Admin
Author by

Admin

Updated on June 15, 2022

Comments

  • Admin
    Admin almost 2 years

    I need to download a file that I got from a REST search. The URL is like the following:

    https://abc.def/geh/servlet/rest/vault?oid=xxx&expr=files.file1

    (I needed to edit it due to privacy reasons..)

    The file is supposed to be a result of a Nastran computation, it can be viewed by a simple Texteditor. The Extension is .pch, it is relatively large (~21mb)

    How can that be implemented in VBA?

  • Jean-François Corbett
    Jean-François Corbett almost 9 years
    "First of all - the link does not work" Really? But abc.def is my favorite website!
  • AnalystCave.com
    AnalystCave.com almost 9 years
    Mine too! Along with xyz.declare :D
  • Admin
    Admin almost 9 years
    It worked! Due to Company regulations, I could not give the real URL. The code for downloading works perfectly, however, I needed to add another http request for logging in: Set WinHttpReq = CreateObject("Microsoft.XMLHTTP") WinHttpReq.Open "GET", UrlLogin, False WinHttpReq.send
  • AnalystCave.com
    AnalystCave.com almost 9 years
    Cool! Glad it worked. If needed I expanded more on this topic in my post: analystcave.com/excel-downloading-files-using-vba