Want to become an expert in VBA? So this is the right place for you. This blog mainly focus on teaching how to apply Visual Basic for Microsoft Excel. So improve the functionality of your excel workbooks with the aid of this blog. Also ask any questions you have regarding MS Excel and applying VBA. We are happy to assist you.

Download a file from url using VBA

Sometimes our Excel VBA applications need to interact with websites. Downloading a file through a URL is a typical example. In this lesson you can learn how to do that using XMLHttpRequest and ADODB.Stream object. XMLHttp is used to request the data from the web server. Once we receive the data from the server, the ADODB.Stream object is used to write that data to a file. You can use this method to download file types such as image files, csv files etc.

So let’s start writing our macro to download a file using the Uniform Resource Locator (URL). Let’s name our Sub procedure as “DownloadFileFromURL”


Sub DownloadFileFromURL()

End Sub

First we need to declare a few variables.

Dim FileUrl As String
Dim objXmlHttpReq As Object
Dim objStream As Object

Next assign the URL of the file to the FileUrl variables

FileUrl = "URL of your file"

For an example if your file URL is https://www.example.com/images/chart.jpg then you can assign it to the variable as follows.

FileUrl = "https://www.example.com/images/chart.jpg"

Next step is to create a XMLHttp object to request the data from the server.

Set objXmlHttpReq = CreateObject("Microsoft.XMLHTTP")

Call the open method followed by the send method.

objXmlHttpReq.Open "GET", FileUrl, False, "username", "password"
objXmlHttpReq.send

Then check the status. If it equals 200 (OK), we can move to the next step.

If objXmlHttpReq.Status = 200 Then

Now we need to create an ADODB.Stream object to write the data(received) to a file.

Set objStream = CreateObject("ADODB.Stream")

Stream in programming means flow of data. In programming data is what flows between processors and input or output devices. Check this link to see all the Properties, Methods and Events of Stream object.

Stream Object Properties, Methods, and Events

But we need to use only a few of them here.

objStream.Open
objStream.Type = 1
objStream.Write objXmlHttpReq.responseBody
objStream.SaveToFile ThisWorkbook.Path & "\" & "file.jpg", 2
objStream.Close

In the SaveToFile method I have entered 2 as SaveOptions. So the program will overwrite the file with the data from the stream if the file already exists. If you don’t want to overwrite the existing file then replace it with 1.

objStream.SaveToFile ThisWorkbook.Path & "\" & "file.jpg", 1

Below is the full code which you can use to download a file through a URL.

Sub DownloadFileFromURL()

     Dim FileUrl As String
     Dim objXmlHttpReq As Object
     Dim objStream As Object

     FileUrl = "URL of your file"

     'example
     'FileUrl = "https://www.example.com/images/chart.jpg"

     Set objXmlHttpReq = CreateObject("Microsoft.XMLHTTP")
     objXmlHttpReq.Open "GET", FileUrl, False, "username", "password"
     objXmlHttpReq.send

     If objXmlHttpReq.Status = 200 Then
          Set objStream = CreateObject("ADODB.Stream")
          objStream.Open
          objStream.Type = 1
          objStream.Write objXmlHttpReq.responseBody
          objStream.SaveToFile ThisWorkbook.Path & "\" & "file.jpg", 2
          objStream.Close
     End If

End Sub

In the above example I showed how to download a .jpg file. But you can use this method to download other file types as well. Remember to change the file extension depending on the type of the file you download. For example if you are downloading a CSV file then the SaveToFile method should be changed as follows.

objStream.SaveToFile ThisWorkbook.Path & "\" & "file.jpg", 2

How to add @ or = sign

            Hope you already know that you can enter any formula to excel cell by entering formula with equal (=) sign. This may have helped you to automate your spread sheets very easily.

But have you ever needed to put = sign or @ sign to a excel cell as a first character. (Not to enter a formula) .
If you enter equal sign first and then if you enter some thing which is not a formula then you will get an error message like below.

 #NAME?

And if you enter @ sign first then you will get following error message.





So I will show you how to enter something which is not a formula to a cell with = sign first. Or how to enter @ sign first. Solution is easy. You just need to put apostrophe ( ' ) before the = or @.
Below image will show how it works. You can see @Head at cell. And in formula bar you can see how it has entered using apostrophe.





Contact Form

Name

Email *

Message *