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.
Thanks a lot for the awesome tutorials.
I can’t recognize “Content-Disposition” in the Response Headers and I have used the following link as an example: https://simpleexcelvba.com/wp-content/uploads/2019/12/downloadjpg.jpg and in Headers when pressing F12, I couldn’t see this “Content-Disposition”..
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.
Thank you very much for your swift reply. Can you give me an example of how to use “Content-Disposition”?
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.)
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
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!