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.
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.
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 counter = 0 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
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.