Q:

Downloading a file in VBA and storing it

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? 

A:

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

