How to get http request with XmlHttpRequest

In this post You will learn how to get http request with XmlHttpRequest, get original file name from URL and download it to chosen location.

Case

We got a list of URL links to files in column B. We want to download everything automatically and save files in chosen location with original names from server.

How to do that?

There are several methods to do that (one of them is in previous post). In this article I’ll show You how to deal with our case using XmlHttpRequest.

How to declare XmlHttpRequest?

There are 2 ways:

1. Reference – early binding

First of all You need to choose the reference in Tools/Reference -find Microsoft XML, v6.0 or other version.

And put this in code:

Dim xmlhttp As New MSXML2.XMLHTTP60

Quick note: there would be MSXML2.XMLHTTP for previous versions.

2.Object – late binding

Using this method You don’t have to choose a reference, just declare and create MSXML2.serverXMLHTTP object.

Dim xmlhttp As Object
Set xmlhttp = CreateObject("MSXML2.serverXMLHTTP")

How to get original file name from URL?

After some attempts I “discovered”, that what we need is .getResponseHeader(“Content-Disposition”). It provides original file name.

name0 = xmlhttp.getResponseHeader("Content-Disposition")

Unfortunately it returns us something like this: filename=name of the file with extension. We need to erase filename= phrase.

pos = InStr(1, name0, "=")
name2 = Mid(name0, pos + 1, (Len(name0) - (pos)))

If it returns empty, we will extract file name from URL.

name2 = FileNameFromPath(myURL)

FileNameFromPath is a simple function, which extracts everything after last slash from URL (function included in final code).

How to download file?

To make it nice and easy we will use function URLDownloadToFile from urlmon library.

xstatus = URLDownloadToFile(0, myURL, dlpath & "\" & name2, 0, 0)

To make it possible we need to declare this function from library, like shown below. That has to be at the beginning, at the top of our code.

Private Declare PtrSafe 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

This function returns 0, when download is successful and returns 1, when something goes wrong.

Code

There is one more thing which I should mention.

dlpath = .Range("D2").Value

It means location for downloaded files, which is needed for URLDownloadToFile function.

And now, the code.

Option Explicit

Private Declare PtrSafe 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

Sub download_picture()

Dim wk As Workbook
Dim ws1 As Worksheet

Dim xmlhttp As New MSXML2.XMLHTTP60

Dim myURL As String, typ As String, name0 As String, name2 As String
Dim name1 As String, xstate As String, dlpath As String
Dim pos As Integer
Dim xstatus As Long, i As Long, lastRow As Long

Application.ScreenUpdating = False

Set wk = ThisWorkbook
Set ws1 = wk.Sheets(1)

With ws1
    dlpath = .Range("D2").Value
    
    lastRow = .Cells(Rows.Count, 2).End(xlUp).Row
    
    For i = 2 To lastRow
    
        myURL = .Range("B" & i).Value
        xmlhttp.Open "GET", myURL, False
        xmlhttp.send
        
        name0 = xmlhttp.getResponseHeader("Content-Disposition")
        
        If name0 <> "" Then
            pos = InStr(1, name0, "=")
            name2 = Mid(name0, pos + 1, (Len(name0) - (pos)))
        Else
            name2 = FileNameFromPath(myURL)
        End If
              
        xstatus = URLDownloadToFile(0, myURL, dlpath & "\" & name2, 0, 0)
        
        Debug.Print xstatus
        
    Next i
    
End With
Application.ScreenUpdating = True

End Sub

Function FileNameFromPath(strFullPath As String) As String

    FileNameFromPath = Right(strFullPath, Len(strFullPath) - InStrRev(strFullPath, "/"))

End Function

Summary

Now You know how to get http request with XmlHttpRequest, download file and save it with original file name. Remember that HTTP requests can be used to interact with a web service, API or even whole websites. Also keep in mind, that XmlHttpRequest is one from many methods to get http request, which I already mentioned in the article.