We’re going to show you 2 methods of Excel VBA 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.
Download Practice Workbook
2 Ways to Insert Picture from URL Using VBA in Excel
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 need.
Here, if there is a blank cell then line22 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.
- Insert a Picture in Excel Header
- How to Insert Picture in Excel Cell Background (3 Methods)
- Insert Pictures in Excel Automatically Size to Fit Cells
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 insert picture from URL.
Read More: How to Insert an Image into a Cell with Excel VBA (2 Methods)
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.
We’ve shown you 2 methods to Excel VBA insert picture from URL. However, If you face any problems, feel free to comment below. Thanks for reading, keep excelling!
how to modify this code if i have merged range? as an example:
Set cRange = Range(“C5:C7, C8:C10, C11:C13”)
This code successfully retrieves the first value (URL) from the merged range, but the image position is only in the range C5, C8, and C11. What I want is the image position to be at C5:C7, C8:C10, C11:C13.
According to your example, Set cRange = Range(“C5, C8, C11”)
Then, you’ll need to change the height & width to MergeArea
.Width = cItem.MergeArea.Width
.Height = cItem.MergeArea.Height
Full Code >
The output will be this: https://ibb.co/KXHVzSC
I want to send the file, but the images disappear when saving. How to save the loaded images in the file?
Thank you for your question.
In my second code, instead of “ActiveSheet.Pictures.Insert” you can try using “ActiveSheet.Shapes.AddPicture”, which should embed the images in the Excel file and it will not disappear.
the modified VBA code from the second method will be:
Does it work with images in Google Drive shared with those who have the link? I’m trying but I can’t.
It also doesn’t work for me with the following image:
It works fine with this image:
I do not know what I’m doing wrong
You need to change Google Drive’s Shared URL when inputting it into the cell.
For example, if I share an image the URL will be -> “https://drive.google.com/file/d/18r34oAVY8-bicTmf3CaTIumuHp_hqZxH/view?usp=sharing”.
Then, I need to change it to -> “https://drive.google.com/uc?export=view&id=18r34oAVY8-bicTmf3CaTIumuHp_hqZxH” (putting the values after d/ inside the id values of the URL).
And for your second question, I think the URL is not correct, if I remove the image resolution info from the URL, then it works perfectly in my second code.
Your URL -> “https://www.exceleinfo.com/wp-content/uploads/2022/06/Referencias-3D-en-Excel-1024×477.png”.
it should be -> “https://www.exceleinfo.com/wp-content/uploads/2022/06/Referencias-3D-en-Excel.png”.
Thanks for sharing this!
But I have 1 problem with this. I used the excel data from Ms. Form and some rows don’t have any image URL since they didn’t upload image on the form. When I applied your second way of attaching image, it stopped to insert image if the cell is empty, but the row below that also stopped working (stopped attaching the image as well).
Could you please help me?
Thanks in advance.
Hello Amilia. Thank you for your question. You can use the following VBA code to ignore blank cells within the range.
Hi! How to make command that if the cell is empty, it will go to the next cell and so on until the end of the cell range? Thanks!
I have modified the VBA code from the second method to handle blank cells for your dataset. Please check the solution to your first comment.
Currently pictures which are high and a have a small width are pulled completely out of proportion. Is it possible to keep the proportions of the picture and fit within the cell?
You can use this code to keep the image proportion.
Here is the output.
That’s great! And how do I combine it with the earlier solution you gave for saving the images within the file?
Hello Robin. This code should work for you. It will make the image fit in the output cell, keep the aspect ratio constant, and the image will not disappear.
Hey – thank you very much for this pedagogical description.
I have a question regarding a Twitter scribing I’m conducting, where I would like to convert the media_URLs into photo. It works regarding the photo whose URL is still active. But some of the others doesn’t. Then I get a box saying “File not found: The file that you’re trying to insternt is no longer available”. I can manually click on the message box each time. But if’ I’m getting excel to converting a data set <1000 URL's and half of them needs me to click. Is there any way in the VBA script that this can be automated?
Hello Andreas, you need to add the following line to ignore the error.
Hi Im trying the code but could not success, though have followed the steps carefully even enabled macro too but your code no functioning im my excel sheet.
Thanks for your comment. It is the most common factor when you download a .xlsnm, the code doesn’t work. So, to avoid this consequence follow the steps stated below.
Firstly, download the .xlsm file from the article and right-click on the file. Select Properties.
Then, check the Unblock box and hit OK.
Hopefully, this method solves your issue. If not then change the directory of the file and Rename it at your preference. then the VBA code of the source file will run in the existing worksheet.
Again, if you are facing any obstacle then mail the Excel file to the address.
Fahim Shahriyar Dipto
Excel & VBA Content Developer
I tried your code .. It works well for some JPG images but some other JPG images Url are not displayed.. It doesn’t give me an error but just doesn’t display the images ..
Thanks in advance
I wanted to inform you that the code mentioned in the first method of the provided article has been executed successfully, and the images have been inserted into the designated range using the provided URLs.
If you experience any difficulties, please double-check that you copied the URLs accurately and that the range you chose is accurate. Also if you need further assistance, please don’t hesitate to share your concerns or provide the Excel file for a more detailed analysis. If any URL doesn’t work, you can share the URL with us also.
Thank you for your cooperation. We are here to help and ensure a smooth experience.