I already described 2 methods how to download file from URL in one of my old articles. They are really good, but not the only methods. In this article I’m going to present the way to download file using WinHttp & ADODB method.
How to understand mix of those methods?
In simple words – send request to the server of specified URL, put the request response body into created data flow (stream) and save to the specified location.
Having this little imagination, we can start coding.
So, to be able to use ADODB, we need to send the request using WinHTTP object.
First of all, let’s set the reference to have the possibility to use early binding. In my case it is Microsoft WinHTTP Services, version 5.1 in Options/References.
Having that done we can declare and set the WinHTTP object.
Dim httpMethod As WinHttp.WinHttpRequest Set httpMethod = New WinHttp.WinHttpRequest
Now it’s time for the URL of the file to download. For the URL example we will use the meme from the How to download file from URL article.
Dim URL As String Let URL = "https://simpleexcelvba.com/wp-content/uploads/2020/03/download2J-e1584221224751.jpg"
Then we can open and send the request to the server to get the file.
httpMethod.Open "GET", URL, False httpMethod.send
There is one last thing before we can go to the second part of our approach. We need to pass the path for the new file. Not to complicate the things we will just take the path of ThisWorkbook and type picture1.jpg for the name of the file.
Dim FilePath As String Let FilePath = ThisWorkbook.Path & "\picture1.jpg"
OK, there is everything what we will require to pass to the ADODB method to download the file.
To be able to use early binding of ADODB we have to set the Microsoft ActiveX Data Objects reference.
To be able to save the file using SaveToFile function we have to set the Microsoft Scripting Runtime reference.
So firstly we need to declare and create new ADODB Stream.
Dim objADOStream As ADODB.Stream Set objADOStream = New ADODB.Stream
Secondly we should create-open the Stream and set its type as binary.
objADOStream.Open objADOStream.Type = 1
And there goes the most important part – we will write to the stream the responseBody property of the WinHTTP object and setting the stream position to the start.
objADOStream.Write ObjectWinHttp.responseBody objADOStream.Position = 0
To use the SaveToFile function we will declare and set the FileSystemObject of the Scripting library.
Dim objFSO As Scripting.FileSystemObject Set objFSO = New Scripting.FileSystemObject
And finally we can save and close the downloaded file.
objADOStream.SaveToFile FileName objADOStream.Close
WinHTTP & ADODB full code
Gathering all together, there are 2 subroutines to have everything nice and clear.
Sub downloadFileFromURL() Dim httpMethod As WinHttp.WinHttpRequest Set httpMethod = New WinHttp.WinHttpRequest Dim URL As String Let URL = "https://simpleexcelvba.com/wp-content/uploads/2020/03/download2J-e1584221224751.jpg" httpMethod.Open "GET", URL, False httpMethod.send Dim FilePath As String Let FilePath = ThisWorkbook.Path & "\picture1.jpg" ADODBmethod httpMethod, FilePath End Sub
Sub ADODBmethod(ObjectWinHttp As Object, FileName As String) Dim objADOStream As ADODB.Stream Set objADOStream = New ADODB.Stream objADOStream.Open objADOStream.Type = 1 objADOStream.Write ObjectWinHttp.responseBody objADOStream.Position = 0 Dim objFSO As Scripting.FileSystemObject Set objFSO = New Scripting.FileSystemObject objADOStream.SaveToFile FileName objADOStream.Close End Sub
In this article You could learn how to download file using WinHTTP and ADODB. I hope it will be another useful alternative to download from URL.