This VBA script automates downloading images from URLs listed in an Excel sheet. Users specify a folder path, and the script fetches each URL, downloads the corresponding image, and saves it to the chosen location. Ideal for bulk image retrieval in data analysis, e-commerce, education, or personal projects. Ensure URLs are valid and from trusted sources to avoid errors and security risks.
Downloading images from URLs listed in an Excel column and saving them to a specific folder, you will need to write a VBA script in Excel. Below is a step-by-step guide on how you can do this:
1. Prepare Excel:
First, ensure your Excel has URLs listed, for example in Column A starting from Row 1. Also, make sure the folder where you want to save images exists.2. Enable Microsoft XML, v6.0:
In the VBA editor, go to Tools -> References and enable "Microsoft XML, v6.0" as this script requires XMLHTTP to download images from the internet.3. Enable Microsoft Scripting Runtime:
Similarly, enable "Microsoft Scripting Runtime" for file system manipulation.4. VBA Script:
Use the following script which you should paste into a new module in the VBA editor (press ALT + F11 to access the editor, then right-click on any existing Worksheets on the left side > Insert > Module):* Setting Initial Parameters:
Dim ws As Worksheet
Dim lastRow As Long, i As Long
Dim url As String, imagePath As String
Dim xmlhttp As Object, myURL As String, winHttpReq As Object
Dim path As String
Set ws = ThisWorkbook.Sheets("Sheet1")- Declares variables for worksheet interaction, row counting, URL processing, and HTTP requests.
- Initializes a reference to the worksheet containing the URLs (assumed to be "Sheet1").
* Prompting User for Save Path:
path = InputBox("Enter the folder path to save images:", "Enter Path")
If path = "" Then
MsgBox "No path entered. Operation Cancelled."
Exit Sub
End If
If Right(path, 1) <> "\" Then
path = path & "\"
End If- Retrieves the user's desired file save path through an input box.
- Validates the input; if none is provided, the script is terminated.
- Ensures the path string ends with a backslash, essential for later file saving.
* Identifying Last Row with Data:
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row- Determines the last row with a URL to ensure the script runs through all available URLs.
* Creating XMLHTTP Object:
Set xmlhttp = CreateObject("MSXML2.XMLHTTP.6.0")- Initializes the XMLHTTP object used to send requests to the internet for file downloading.
* Looping Through URLs to Download Images:
For i = 1 To lastRow
myURL = ws.Cells(i, 1).Value
If myURL Like "http*://*.*" Then
xmlhttp.Open "GET", myURL, False
xmlhttp.send
imagePath = path & Right(myURL, InStr(1, StrReverse(myURL), "/")
- 1)
If xmlhttp.Status = 200 Then
Set winHttpReq = CreateObject("WinHttp.WinHttpRequest.5.1")
winHttpReq.Open "GET", myURL, False
winHttpReq.send
Set oStream = CreateObject("ADODB.Stream")
oStream.Open
oStream.Type = 1
oStream.Write winHttpReq.responseBody
oStream.SaveToFile imagePath, 2
oStream.Close
End If
End If
Next i- Iterates through each URL in the Excel sheet.
- Validates URL format before attempting download.
- Sends an HTTP request for each URL and, if the request is successful (status 200), downloads the file.
- Saves the file to the previously specified path, using the original file name from the URL.
* Completion Message:
MsgBox "Images downloaded successfully."- Displays a message box to the user indicating the process has been completed successfully.
This script systematically downloads images from a list of URLs in an Excel worksheet, saving them to a specified directory, ideal for various applications requiring bulk image downloads. Remember to handle potential errors and ensure URL security.
Sub DownloadImages()
Dim ws As Worksheet
Dim lastRow As Long, i As Long
Dim url As String, imagePath As String
Dim xmlhttp As Object, myURL As String, winHttpReq As Object
Dim path As String
' Reference to the sheet with the data (change Sheet1 to your actual sheet name)
Set ws = ThisWorkbook.Sheets("Sheet1")
' Input box to get the desired path from user
path = InputBox("Enter the folder path to save images:", "Enter Path")
If path = "" Then
MsgBox "No path entered. Operation Cancelled."
Exit Sub
End If
' Make sure the folder path ends with a backslash
If Right(path, 1) <> "\" Then
path = path & "\"
End If
' Determining the last row with data in the column with URLs
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
' Creating XMLHTTP object
Set xmlhttp = CreateObject("MSXML2.XMLHTTP.6.0")
For i = 1 To lastRow
myURL = ws.Cells(i, 1).Value 'assuming your URL is in column A and starts from row 1
' Validate URL
If myURL Like "http*://*.*" Then
' Requesting the URL
xmlhttp.Open "GET", myURL, False
xmlhttp.send
' File name from URL or you can write your own
imagePath = path & Right(myURL, InStr(1, StrReverse(myURL), "/") - 1)
' Saving the file If xmlhttp.Status = 200 Then
Set winHttpReq = CreateObject("WinHttp.WinHttpRequest.5.1")
winHttpReq.Open "GET", myURL, False
winHttpReq.send
Set oStream = CreateObject("ADODB.Stream")
oStream.Open
oStream.Type = 1
oStream.Write winHttpReq.responseBody
oStream.SaveToFile imagePath, 2 ' 1 = no overwrite, 2 = overwrite
oStream.Close
End If
End If
Next i
MsgBox "Images downloaded successfully."
End Sub5. Run the Script:
After pasting the script into the module, you can run it by pressing F5 while in the VBA editor or by pressing ALT + F8, selecting `DownloadImages` from the list, and clicking "Run" while in Excel. It will prompt you to enter the directory where you want to save the images.Remember, this script assumes that your URLs are located in the first column (column A) starting from the first row. Adjust the range if your setup is different. Also, ensure that the entered path exists, as the script does not handle the creation of a new directory.
