Download file using WinHttp & ADODB method

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.

WinHttp method

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.

ADODB method

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.