How to Insert Picture from URL Using VBA in Excel (2 Methods)

We’re going to show you 2 methods of Excel VBA to insert picture from URL. To demonstrate our methods, we’ve picked a dataset with 3 columns: #, Image, and URL. We’re going to insert images into the blank column from our URL.

Insert Picture from URL Using VBA in Excel: 2 Ways

1. Using VBA to Insert Picture from URL in Excel

We’re going to insert pictures from the URL by using Excel VBA into the Image column.

Steps:

• Firstly, from the Developer tab >>> select Visual Basic.

The Visual Basic window will open.

• Secondly, from Insert >>> select Module.

• Thirdly, type the following code into the Module.

Sub URLPhotoInsert()
    Dim cShape As Shape
    Dim cRange As Range
    Dim cColumn As Long
    On Error Resume Next
    Application.ScreenUpdating = False
    Set xRange = ActiveSheet.Range("D5:D10")
    For Each cell In xRange
        cName = cell
        ActiveSheet.Pictures.Insert(cName).Select
        Set cShape = Selection.ShapeRange.item(1)
        If cShape Is Nothing Then GoTo line22
        cColumn = cell.Column - 1
        Set cRange = Cells(cell.Row, cColumn)
        With cShape
            .LockAspectRatio = msoTrue
            If .Width > cRange.Width Then .Width = cRange.Width * 3 / 4
            If .Height > cRange.Height Then .Height = cRange.Height * 3 / 4
            .Top = cRange.Top + (cRange.Height - .Height) / 2
            .Left = cRange.Left + (cRange.Width - .Width) / 2
        End With
line22:
        Set cShape = Nothing
        Range("D5").Select
    Next
    Application.ScreenUpdating = True
End Sub

Then close the window.

• After that, from the Developer tab >>> select Macros.

The Macro dialog box will appear.

• After that, select “URLPhotoInsert” as “Macro name:”.
• Finally, click on Run.

The pictures will appear in the middle column. If you have a large number of picture URLs, it may take a longer time.

2. Using the Range.Offset Property to Insert Picture from URL

Here, we’ll insert pictures from the URL using Excel VBA in the cell range C5:C7. Here, we’ll use the Range.Offset property to choose the picture location in our sheet.

Steps:

• Firstly, repeat the steps from method 1 to bring the VBA module.
• Secondly, type the following code in that Module.

Sub URLPhotoInsert2()
Dim cPhoto As String
Dim cPicture As Picture
Dim cRange As Range
Dim cItem As Range
Set cRange = Range("C5:C10")
For Each cItem In cRange
    cPhoto = cItem.Offset(0, -1)
    If cPhoto = "" Then Exit Sub
        Set cPicture = ActiveSheet.Pictures.Insert(cPhoto)
        With cPicture
            .ShapeRange.LockAspectRatio = msoFalse
            .Width = cItem.Width
            .Height = cItem.Height
            .Top = Rows(cItem.Row).Top
            .Left = Columns(cItem.Column).Left
            .Placement = xlMoveAndSize   
        End With 
    Next
End Sub

• Save and close that window.
• Thirdly, bring up the Macro window.
• After that, choose “URLPhotoInsert2”.
• Finally, click on Run.

We can see the pictures in column C. We’ve shown you another method to Excel VBA inserts pictures from URL.

Read More: Excel VBA: Insert Picture from Folder

Practice Section

We’ve added data practice tables for your convenience in the Excel file. Moreover, remember you need to change the cell range in the code in order to insert pictures into the cells from the URLs.

Download Practice Workbook

Conclusion

We’ve shown you 2 methods to Excel VBA insert pictures from URL. However, If you face any problems, feel free to comment below. Thanks for reading, keep excelling!

Related Articles

• How to Insert an Image into a Cell with Excel VBA
• Insert Pictures Automatically Size to Fit Cells with VBA in Excel