Can I use VBA to import images (gifs) from the web into Excel?

Question

I have a list of URLs to .gif files in an Excel sheet. I want to use VBA to query these websites and insert these images into the same worksheet. I am using Excel 2007.

I tried using the 'Get External Data - From Web' feature to query the website, but it returned the following error:

"Unable to open http://.../blah.GIF. The Internet site cannot return the object you request. (HTTP/1.0 403)"

Is this because Excel cannot import online images with this feature? Or is this problem specifically related to the website?

And most importantly, is there some other way to do this entirely in VBA?

Answer 1

Yes, you can do so. It is actually quite easy. Excel has a AddPicture function. From Excel VBA Help:

expression.AddPicture(Filename, LinkToFile, SaveWithDocument, Left, Top, Width, Height)

Use like this:

Sheets(1).Shapes.AddPicture "http://www.mywebsite.com/images/map.gif" _
                          , msoFalse, msoTrue, 100, 100, 500, 600

Left, Top, Width, & Height are all required and in points.

Answer 2

First you'll want to download the image. There are several methods for doing this. I use the URLDownloadToFile API.

Use the URLDownloadToFile API function to download a file from a URL into a file*:

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

(*visit link for full code)

Then a code sample like this will import them onto the worksheet wherever you like:

From Insert pictures using VBA in Microsoft Excel*:

Sub TestInsertPicture()
    InsertPicture "C:\FolderName\PictureFileName.gif", _
        Range("D10"), True, True
End Sub

(*visit link for full code)

To be fair to the original authors, I won't repost the code in its entirety here. Apologies for a mostly link-based answer.