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.

Http request with XmlHttpRequest

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.

Http request with XmlHttpRequest reference

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.

Author: Tomasz Płociński

I'm very advanced in VBA, Excel, also easily linking VBA with other Office applications (e.g. PowerPoint) and external applications (e.g. SAP). I take part also in RPA processes (WebQuery, DataCache, IBM Access Client Solutions) where I can also use my SQL basic skillset. I'm trying now to widen my knowledge into TypeScript/JavaScript direction.

11 thoughts on “How to get http request with XmlHttpRequest”

    1. I appreciate that 🙂
      Yes, that image is not returning that. Working with .WinHttpRequest code I noticed that if You got a link to file with no file name with extension included (unlike your example) links are returning names using “Content-Disposition”. And if there is file name in link usually there is no response, that’s why I suggested in article to get the file name from link.

      1. Thank you very much for your swift reply. Can you give me an example of how to use “Content-Disposition”?

        1. I just found specially for You a hyperlink with no file name in it, but when You enter this You can clearly see that this is PDF file.
          Try to get its “Content-Disposition” and You will see what I described in the article 🙂
          https://www.oras.com/datasheet/2222F/pl
          (this is the link into catalog card of ORAS company product, You can try to find something like this by yourself ofc.)

  1. Hi Tomasz,
    thanks for the tutorial. I have a real layman’s problem: the script always ends at the point “counter = 0” with an “compiling error”: “Variable not defined.”
    Can you tell me what I’m doing wrong?
    Thank you very much, greetings,
    Manfred

    1. Hello Manfred
      This variable is not declared, but also this variable is not needed anywhere. Just get rid of that and the rest should be fine!
      Corrected this in article. Thanks!

Leave a Reply

Your email address will not be published. Required fields are marked *