Automating JPG Image Downloads in Excel with VBA

Learn how to automate the process of extracting image URLs from HTML data in Microsoft Excel using a VBA macro. This quick and efficient solution will save you time and effort when dealing with image-rich content.

VBA Code:

Sub ExtractImageURLs()
    Dim rng As Range
    Dim cell As Range
    Dim htmlDoc As Object
    Dim imageNodes As Object
    Dim imageURLs As String
    Dim outputCell As Range

    ' Define the range you want to extract image URLs from
    Dim startCell As Range
    Dim endCell As Range
    Set startCell = Range("A1")  ' Modify the starting cell as needed
    Set endCell = Cells(Rows.Count, startCell.Column).End(xlUp)
    Set rng = Range(startCell, endCell)

    ' Create a new HTMLDocument object
    Set htmlDoc = CreateObject("htmlfile")

    ' Loop through each cell in the defined range
    For Each cell In rng
        ' Load the HTML content into the HTMLDocument object
        htmlDoc.Open
        htmlDoc.Write "<span style=""color: #007F00;"">" & cell.Value & "</span>"
        htmlDoc.Close

        ' Extract the image URLs
        Set imageNodes = htmlDoc.getElementsByTagName("img")
        imageURLs = ""

        ' Concatenate the image URLs
        For Each img In imageNodes
            imageURLs = imageURLs & img.src & vbNewLine
        Next img

        ' Remove extra characters and assign the URLs back to the cell
        cell.Value = "<span style=""color: #7F0000;"">" & Left(imageURLs, Len(imageURLs) - 1) & "</span>"

        ' Adjust the row height to fit the content if needed
        cell.EntireRow.AutoFit
    Next cell
End Sub

Step 1: Access VBA Editor

Open your Excel workbook and access the VBA editor by pressing ALT + F11.

Step 2: Insert the Macro

Copy and paste the provided VBA macro code into a new module.

Step 3: Define the Range

Specify the range from which you want to extract image URLs.

Step 4: Run the Macro

Close the VBA editor, return to your worksheet, and run the "ExtractImageURLs" macro from the "Macros" window (ALT + F8).

Step 5: Enjoy Automation

Sit back and watch as the macro swiftly extracts image URLs, saving you time and manual effort.

Conclusion:

Automate the process of extracting image URLs from HTML data in Excel using the provided VBA macro. Enhance your productivity and efficiency with this simple solution. Happy extracting!