This article will explain how to add a macro in Excel to convert image URLs to images/signatures.
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.
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
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.
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.