This article will explain how to add a macro in Excel to convert image URLs to images/signatures. 


Step 1:

Open your Excel export and press ALT+F11. When the VBA editor opens, double click on the sheet where your images are (for example Sheet4). A new window will open on the right of the editor.



Note: If the VBA editor does not open, you’ll need to follow the instructions at the bottom of this article.


Step 2:

Paste the following into the VB window that opens on the right.


Public Sub Add_Images_To_Cells()

    Dim lastRow As Long
    Dim URLs As Range, URL As Range
    Dim pic As Picture
    Dim urlColumn As String
    
    With ActiveSheet
        urlColumn = "H"
        lastRow = .Cells(Rows.Count, urlColumn).End(xlUp).Row
        Set URLs = .Range(urlColumn & "2:" & urlColumn & lastRow)
    End With

    For Each URL In URLs
        If InStr(URL.Value, "http") > 0 Then
            URL.Offset(0, 0).Select
            Set pic = URL.Parent.Pictures.Insert(URL.Value)
            With pic.ShapeRange
                .LockAspectRatio = msoFalse
                .Height = URL.Offset(0, 0).Height - 1
                .Width = URL.Offset(0, 0).Width - 1
                .LockAspectRatio = msoTrue
            End With
            
            URL.Clear
            DoEvents
        End If
    Next
    
End Sub



Step 3:

Change the urlColumn (as seen in row 9 above: urlColumn = "H") to match the column you want to replace. This will be the column that holds the media URL links. 


Step 4:

Press the green play button in the toolbar at the top.





It should start replacing the media URLs one by one in the Excel sheet. The process can be repeated for each image column by updating the urlColumn (as covered in step 3). 







Note: You will need to be connected to the internet to run the macro and download the images the first time. Once you've replaced the URLs with the images, you can save the file and open it without being connected.



If ALT-F11 doesn’t open the VBA editor, follow these steps below:
  • Click on 'File' in the top left
  • Select 'Options'
  • Click on 'Customise ribbon'
  • Click on 'Developer', and then 'OK'
  • Click on the Developer tab




  • Click on Visual Basic to open the VBA editor and follow the steps as outlined at the start of the article.