Automating JPG Image Downloads in Excel with VBA

In this blog post, we'll explore a powerful Excel VBA code that automates the process of downloading JPG images from URLs. This VBA macro will allow you to efficiently fetch and save multiple images from the web into a specified folder. Say goodbye to manual downloads and streamline your image retrieval process with this handy Excel tool.

Prerequisites:

To follow along with the steps in this blog, you'll need a basic understanding of Microsoft Excel and Visual Basic for Applications (VBA). Make sure you have Excel installed on your computer.

Step 1: Prepare Your Worksheet

Create a new Excel worksheet or use an existing one where you want to enter the image URLs and view the download status. For this example, we'll use "Sheet1." Set up columns as follows:

Column A: Image names (e.g., "image1", "image2")

Column B: Image URLs (e.g., "https://example.com/image1.jpg", "https://example.com/image2.jpg")

Column C: Status (this column will display the download status for each image)

Step 2: Insert the VBA Macro Code

Press ALT + F11 to access the VBA editor within Excel. Click on "Insert" in the menu and choose "Module." Copy and paste the following VBA Macro code into the new module:

Const FolderName As String = "P:\Test\"

Sub downloadJPGImages()
    Set ws = ActiveWorkbook.Sheets("Sheet1")
    lLastRow = ws.Range("A" & Rows.Count).End(xlUp).Row

    Set oXMLHTTP = CreateObject("MSXML2.XMLHTTP.6.0")
    Set oBinaryStream = CreateObject("ADODB.Stream")
    adTypeBinary = 1
    oBinaryStream.Type = adTypeBinary

    For i = 2 To lLastRow
        sPath = Downloads & ws.Range("A" & i).Value & ".jpg"
        sURI = ws.Range("B" & i).Value

        On Error GoTo HTTPError
        oXMLHTTP.Open "GET", sURI, False
        oXMLHTTP.Send
        aBytes = oXMLHTTP.responsebody
        On Error GoTo 0

        oBinaryStream.Open
        oBinaryStream.Write aBytes
        adSaveCreateOverWrite = 2
        oBinaryStream.SaveToFile sPath, adSaveCreateOverWrite
        oBinaryStream.Close

        ws.Range("C" & i).Value = "File successfully downloaded as JPG"
    NextRow:
    Next

    Exit Sub

HTTPError:
    ws.Range("C" & i).Value = "Unable to download the file"
    Resume NextRow
End Sub

Step 3: Customize the Macro Parameters Before running the macro, you need to make some adjustments:

1. The Excel file should be placed in a blank folder where you want to save the images. All the images will be saved there.

2. Don't Stop Macro while running.

3. After Downloading all image you can move them as your desired location