The other day I was given a challenge by a reader who needed to download some images from the web. First off, why not do it automatically? The first reason would be to save time because with excel vba you can automate the task. It automate the task. In this case, the image is a stock chart which can change every day so being able to quickly update the excel file required being able to download it.

What Is Necessary To Do This?

The most important is to know the exact address or URL of the file including the name such as:

http://www.example.com/name.jpg

Once you know that, you should be ok. In my opinion there are 2 different tasks that could be ran when downloading a file, you can

#1-Download the file and save it to a directory

The first one requires a bit of code but it’s straight forward once you do have it in place, you can simply use the following:

Private Declare 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

Private Const ERROR_SUCCESS As Long = 0

Sub LancementProcedure()
DownloadFile “http://finviz.com/chart.ashx?t=INTC&ty=c&ta=0&p=w&s=l”, “C:\stock.png”
End Sub

Public Function DownloadFile(ByVal sURL As String, ByVal sLocalFile As String) As Boolean
Dim lngRetVal As Long
DownloadFile = URLDownloadToFile(0&, sURL, sLocalFile, 0&, 0&) = ERROR_SUCCESS
End Function

The 2 main parameters that you must change are the Downloaded File URL and the location+name where you’re saving it.

#2-Download the file and add it to the excel spreadsheet

Sheets(1).Shapes.AddPicture “http://finviz.com/chart.ashx?t=” & Ticker & “&ty=c&ta=1&p=d&s=l” _
, msoFalse, msoTrue, 380, 60, 800, 410

In this case, it will simply add the image to the spreadsheet of the size and location that is mentioned. I also run a couple of lines to always delete the previous chart before adding a new one.

You can see what it looks like here: