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.
- 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
VBA Code Breakdown
- On Error Resume Next
- If the code finds any Runtime Errors, it will go to the next statement.
- Application.ScreenUpdating = False
- We’re setting the ScreenUpdating property to False. This is done to speed up the macro execution speed.
- Set xRange = ActiveSheet.Range(“D5:D10”)
- We’re setting the URL range to D5:D10. Our URL is up to cell D7, but we’ve set it to demonstrate that it will work in the case of an empty cell. You need to change this for your data.
After that, we’re running a “For loop” within that URL cell range.
- cColumn = cell.Column – 1
- We’re setting the picture to be inserted in the C column (before the image URL). You need to change it according to your needs.
Here, if there is a blank cell then line 22 will be executed. Then, we’re setting the picture properties in With cShape portion.
- .LockAspectRatio = msoTrue
- We’re setting the picture aspects ratio to be in a locked state.
If the picture is too large than the cell size then the width and the height are also set to 75% of the cell size.
- 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.
- 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
VBA Code Breakdown
- Set cRange = Range(“C5:C10”)
- We’re setting the cell range to C5:C10. We’re adding empty cells to prove that our code works in the case of an empty cell. This is the cell range where the picture will be inserted. You need to change it for your dataset.
- cPhoto = cItem.Offset(0, -1)
- We’re telling this to obtain the picture URL from the B column.
We’re adjusting the picture properties using the With cPicture portion.
- .ShapeRange.LockAspectRatio = msoFalse
- We’re not locking the aspect ratio of the picture.
- .Placement = xlMoveAndSize
- This ensures the picture moves and resizes with the cell.
- 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
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
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!