Note: The other languages of the website are Google-translated. Back to English
Log in Register

How to convert image URLs to actual images in Excel?

doc url to img 1

If you have a list of image URL addresses in column A, and now, you want to download the corresponding pictures from the URLs and display them into the adjacent column B as left screenshot shown. In Excel, how could you extract the actual pictures from the image URLs quickly and easily?

Convert the image URLs to actual images with VBA code

Convert the image URLs to actual images with Kutools for Excel

Convert the image URLs to actual images with VBA code

The following VBA code can help you quickly extract the actual images from the image URL addresses, please do as this:

1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.

2. Click Insert > Module, and paste the following code in the Module Window.

VBA code: Convert the image URLs to actual images:

Sub URLPictureInsert()
Dim Pshp As Shape
Dim xRg As Range
Dim xCol As Long
On Error Resume Next
Application.ScreenUpdating = False
Set Rng = ActiveSheet.Range("A2:A5")
For Each cell In Rng
filenam = cell
ActiveSheet.Pictures.Insert(filenam).Select
Set Pshp = Selection.ShapeRange.Item(1)
If Pshp Is Nothing Then GoTo lab
xCol = cell.Column + 1
Set xRg = Cells(cell.Row, xCol)
With Pshp
.LockAspectRatio = msoFalse
If .Width > xRg.Width Then .Width = xRg.Width * 2 / 3
If .Height > xRg.Height Then .Height = xRg.Height * 2 / 3
.Top = xRg.Top + (xRg.Height - .Height) / 2
.Left = xRg.Left + (xRg.Width - .Width) / 2
End With
lab:
Set Pshp = Nothing
Range("A2").Select
Next
Application.ScreenUpdating = True
End Sub

Notes: 

  • 1. In the above code, A2:A5 is the range of cells which contains the URL addresses you want to extract the images, you should change the cell references to your need.
  • 2. With this code, you can not specify the size of the extracted images to your need.
  • 3. The above code only can extract the actural images into the cells besides your URL column, you can not specify cell to output the images.
  • 4. You should have some basic knowledge of the code, if any character missed or incorrect, the code will not be executed successfully.

3. Then press F5 key to run this code, and all corresponding pictures have been extracted from the image URLs to the adjacent column at once, and the images will be placed at the center of your specific cells, see screenshot:

doc url to img 2

Convert the image URLs to actual images with Kutools for Excel

If you are not familiar with the VBA code or want to remedy the limitation of the above code, Kutools for Excel's Insert Pictures form Path(URL) feature can help you to quickly insert the cprresponding images based on the URL addresses or specific path in your computer as below screenshot shown. Click to download Kutools for Excel!

Note:To apply this Insert Pictures form Path(URL), firstly, you should download the Kutools for Excel, and then apply the feature quickly and easily.

After installing Kutools for Excel, please do as this:

1. Click Kutools > Insert > Insert Pictures form Path(URL), in the popped out dialog box, please set the following operations, see screenshots:

doc url to img 3 doc url to img 4

2. Then, click Ok button, and the pictures will be extracted from the URLs, see screenshot:

doc url to img 1

Click to Download and free trial Kutools for Excel Now!

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Comments (61)
No ratings yet. Be the first to rate!
Shayla
Shayla
This comment was minimized by the moderator on the site
Could you change the "on error resume next" to something that will wait until Google Chrome has time to open the picture? I tried this code and it works, but some of the pictures are getting pasted several rows below where they should, so I'm assuming the "on error" statement is not giving it enough time. If you think it could be something else or have any other suggestions, I'm open to hear it. Thanks!
Reply
Danny
Danny
This comment was minimized by the moderator on the site
This was a great help, ty so much
Reply
RadmilaHU
RadmilaHU
This comment was minimized by the moderator on the site
I have tried the formula, but it does not seem to work. Would it have to do something with the version of Excel (I have the newest version, though) I appreciate your answer
Reply
Danny
Danny
This comment was minimized by the moderator on the site
What issue are you getting?
Reply
Russell Thrasher
Russell Thrasher
This comment was minimized by the moderator on the site
Run-time error '1004': Unable to get the insert property of the Pictures class
Reply
Meg Russell
Meg Russell     Russell Thrasher
This comment was minimized by the moderator on the site
This is not working for me either. I have Excel 2013 and I simply need a straightforward piece of code that will insert images besides the urls that are in a column in my spreadsheet. Every single solution I have tried that is available via Google Search or via YouTube has rendered a pictures insert value error. What am I doing wrong??? Are the urls supposed to NOT be hyperlinks? Are they SUPPOSED to be hyperlinks? Am I supposed to identify the column range that the output goes to? I can't find a simple to follow set of instructions anywhere, please help. When I use YOUR code, I get "Unable to get the insert property of the Pictures" class:
Reply
Peter
Peter     Meg Russell
This comment was minimized by the moderator on the site
I had the same problem and it turns out you need to check your url. if it is HTTPS that means it is a secure connection and VBA will not be able to extract it. if it is a regular HTTP there should be no problem
Reply
Moses
Moses
This comment was minimized by the moderator on the site
Thank you. It worked for me How do i change/modify the syntax to display image url's that are password protected
Reply
ZAFAR KHAN
ZAFAR KHAN
This comment was minimized by the moderator on the site
Hi everyone, I have modified these code so that it works for the selected range instead of entering the specific range, However, can anyone suggest me how to modify these code to have the image in the centre of the cell. Currently the image is showing in the top left corner

Sub URLPictureInsert()

Dim Pshp As Shape

Dim rCell As Range

On Error Resume Next

Application.ScreenUpdating = False

For Each rCell In Selection

filenam = rCell ActiveSheet.Pictures.Insert(filenam).Select

Set Pshp = Selection.ShapeRange.Item(1)

With Pshp

.LockAspectRatio = msoTrue

.Width = 100

.Height = 100

.Cut

End With

Cells(rCell.Row, rCell.Column + 1).PasteSpecial

Next Application.ScreenUpdating = True

End Sub
Reply
Report
belliney
belliney
This comment was minimized by the moderator on the site
AMAZING! this is so great! i had 350 rows of hyperlink and it took only 5 mins to pull all images!
Reply
Naresh
Naresh     belliney
This comment was minimized by the moderator on the site
hey, can anyone attach a link of excel sheet with macro code enabled it so that i can download and experiment, I'm new to VB and I badly need to do this.
Reply
Jeremy
Jeremy
This comment was minimized by the moderator on the site
For me, all that shows up in each cell is the name of the method. They all say "Sub URLPictureInsert()"
Reply
Florent
Florent
This comment was minimized by the moderator on the site
Works perfectly but can anyone help me add something to check if the image exists and if it doesn't, post a text in the cell like "Image unavailable" ??
Reply
Mark
Mark
This comment was minimized by the moderator on the site
In Excel 2010 I had to add these declarations

Dim Rng As Range
Dim cell As Range
Dim filenam As String
Reply
Mark Boucher
Mark Boucher
This comment was minimized by the moderator on the site
To get this to work in Excel 2010 I had to add these declarations.


Dim Rng As Range
Dim cell As Range
Dim filenam As String
Reply
Report
Taylor Hays
Taylor Hays
This comment was minimized by the moderator on the site
Is there any way to run this so that it is across a row instead of a column? I have tried simply changing the range and the final location of the image but it only ever produces the first cell.
Reply
skyyang
skyyang     Taylor Hays
This comment was minimized by the moderator on the site
Hi, Taylor,
May be the following VBA code can help you to extract the real pictures in a row URLs:


Sub URLPictureInsert()
Dim Pshp As Shape
On Error Resume Next
Application.ScreenUpdating = False
Set Rng = ActiveSheet.Range("A1:E1")
For Each cell In Rng
filenam = cell
ActiveSheet.Pictures.Insert(filenam).Select
Set Pshp = Selection.ShapeRange.Item(1)
With Pshp
.LockAspectRatio = msoTrue
.Width = 100
.Height = 100
.Cut
End With
Cells(cell.Row + 1, cell.Column).PasteSpecial
Next
Application.ScreenUpdating = True
End Sub

Please try it, hope it can help you. Thank you!
Reply
Report
Iris De Smedt
Iris De Smedt
This comment was minimized by the moderator on the site
Hi guys,
It would love to get this working but when I copy this in VBA & click on run I only get this text:
Sub URLPictureInsert()
Can you please help me to get the picture thumbnail?
Reply
skyyang
skyyang     Iris De Smedt
This comment was minimized by the moderator on the site
Hello,
When applying the code, you can change the width and height size of the picture to match you cells.
Thank you!
Reply
Report
DKcrm
DKcrm
This comment was minimized by the moderator on the site
Works great for pulling images, but they are put randomly and not in the expected cells... Excel 2016 from Office365...
Reply
skyyang
skyyang     DKcrm
This comment was minimized by the moderator on the site
Hello, DKcrm,
Thank you for your comment, the code in this article has been updated, please try the new one, hope it can help you!
Reply
Report
Zafar Khan
Zafar Khan
This comment was minimized by the moderator on the site
Thanks for your post. How I can get the pictures in the center of the cell. With the current codes the pictures are showing in the top left corner
Reply
skyyang
skyyang     Zafar Khan
This comment was minimized by the moderator on the site
Hello, Khan,
the following VBA code can help you to extract the picture and put them into the center of the cells, please try it.

Sub URLPictureInsert()
Dim Pshp As Shape
Dim xRg As Range
Dim xCol As Long
On Error Resume Next
Application.ScreenUpdating = False
Set Rng = ActiveSheet.Range("A2:A6")
For Each cell In Rng
filenam = cell
ActiveSheet.Pictures.Insert(filenam).Select
Set Pshp = Selection.ShapeRange.Item(1)
If Pshp Is Nothing Then GoTo lab
xCol = cell.Column + 1
Set xRg = Cells(cell.Row, xCol)
With Pshp
.LockAspectRatio = msoFalse
If .Width > xRg.Width Then .Width = xRg.Width * 2 / 3
If .Height > xRg.Height Then .Height = xRg.Height * 2 / 3
.Top = xRg.Top + (xRg.Height - .Height) / 2
.Left = xRg.Left + (xRg.Width - .Width) / 2
End With
lab:
Set Pshp = Nothing
Range("A2").Select
Next
Application.ScreenUpdating = True
End Sub
Reply
Report
Justin
Justin     skyyang
This comment was minimized by the moderator on the site
Couldn't get this to work can you show the completed code for this request?
Reply
Vipin Sahu
Vipin Sahu     skyyang
This comment was minimized by the moderator on the site
Hello,
I want to download the images from the URL's (Column A) in a specific folder and rename the same images with a code/number which is there in Column B.
Reply
Report
skyyang
skyyang     Vipin Sahu
This comment was minimized by the moderator on the site
Hello, Vipin,
To save the URL'S images into a folder and rename them with the new names in Column B, may be the following VBA code can help you:

Note: Column A contains the URL addresses and Column B has the new names.

#If VBA7 Then
Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" (ByVal pCaller As Long, ByVal szURL As String, ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long
#Else
Private Declare Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" (ByVal pCaller As Long, ByVal szURL As String, ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long
#End If
Sub URLPictureInsert()
Dim I As Integer
Dim xStr As String
Dim xFd As FileDialog
Dim xArr, xFdItem As Variant
Application.ScreenUpdating = False
Set xFd = Application.FileDialog(msoFileDialogFolderPicker)
If xFd.Show = -1 Then
xFdItem = xFd.SelectedItems.Item(1)
xArr = Range("A2:B5").Value
For I = 1 To UBound(xArr)
If xArr(I, 1) <> "" Then
xStr = Mid(xArr(I, 1), InStrRev(xArr(I, 1), "."), Len(xArr(I, 1)))
URLDownloadToFile 0, xArr(I, 1), xFdItem & "\" & xArr(I, 2) & I & xStr, 0, 0
End If
Next
End If
Application.ScreenUpdating = True
End Sub

Please try it, hope this can help you!
Reply
Report
David
David     skyyang
This comment was minimized by the moderator on the site
the renaming part on column B uploads into the folder but is adding a number count next to the name of the picture, how I can omit that?

Thank you
Reply
skyyang
skyyang     David
This comment was minimized by the moderator on the site
Hi, David,
May be the below vba code can help you to extract the iimages and rename them wihtout the subfix number, please try. Hope it can help you!

#If VBA7 Then
Private Declare PtrSafe Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" (ByVal pCaller As Long, ByVal szURL As String, ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long
#Else
Private Declare Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" (ByVal pCaller As Long, ByVal szURL As String, ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long
#End If
Sub URLPictureInsert()
Dim I As Integer
Dim xStr As String
Dim xFd As FileDialog
Dim xArr, xFdItem As Variant
Application.ScreenUpdating = False
Set xFd = Application.FileDialog(msoFileDialogFolderPicker)
If xFd.Show = -1 Then
xFdItem = xFd.SelectedItems.Item(1)
xArr = Range("A2:B5").Value
For I = 1 To UBound(xArr)
If xArr(I, 1) <> "" Then
xStr = Mid(xArr(I, 1), InStrRev(xArr(I, 1), "."), Len(xArr(I, 1)))
URLDownloadToFile 0, xArr(I, 1), xFdItem & "\" & xArr(I, 2) & xStr, 0, 0
End If
Next
End If
Application.ScreenUpdating = True
End Sub
Report
david
david     skyyang
This comment was minimized by the moderator on the site
Omg!! You just provided a miracle, It works like a charm. Thank you so much.
Sam Ruppenthal
Sam Ruppenthal
This comment was minimized by the moderator on the site
range is set, filenam = url and changes with each pass, Pshp is always nothing. Any Ideas
Reply
Report
SamR
SamR
This comment was minimized by the moderator on the site
I'm using an Intel I5 processor, windows 7 professional 64bit, Office 2016 64 bit. I assume that the problem is a setting in Excel. Any help would be greatly appreciated.
range is set, filenam = url and changes with each pass, Pshp is always nothing, URL's are verified, Any Ideas
Reply
Shozib
Shozib
This comment was minimized by the moderator on the site
Hi skyyang, first for such an informative post. I have a question, could you please help me. Using this code of your's I want to extract just one pic and want to place it in a specific cell. Is it possible??
Reply
Report
skyyang
skyyang     Shozib
This comment was minimized by the moderator on the site
Hello, Shozib,
To put the images to any other cells as you want, please apply the following VBA code:

Sub URLPictureInsert1()
'Updateby Extendoffice 20180608
Dim Pshp As Shape
Dim xRg As Range
Dim xCol As Long
On Error Resume Next
Set Rng = Application.InputBox("Please select the url cells：", "KuTools for excel", Selection.Address, , , , , 8)
If Rng Is Nothing Then Exit Sub
Set xRg = Application.InputBox("Please select a cell to put the image：", "KuTools for excel", , , , , , 8)
If xRg Is Nothing Then Exit Sub
Application.ScreenUpdating = False
For I = 1 To Rng.Count
filenam = Rng(I)
ActiveSheet.Pictures.Insert(filenam).Select
Set Pshp = Selection.ShapeRange.Item(1)
If Pshp Is Nothing Then GoTo lab
xCol = cell.Column + 1
Set xRg = xRg.Offset(I - 1, 0)
With Pshp
.LockAspectRatio = msoFalse
.Width = 80
.Height = 80
.Top = xRg.Top + (xRg.Height - .Height) / 2
.Left = xRg.Left + (xRg.Width - .Width) / 2
End With
lab:
Set Pshp = Nothing
Range("A2").Select
Next
Application.ScreenUpdating = True
End Sub

Please try it, hope it can help you!
Reply
Report
Ed
Ed
This comment was minimized by the moderator on the site
Hi all, This Macro is great. However, it does not really add pictures to the file but links that generate pictures each time I open the file. Thus I cannot compress pictures as they are not really inside the file. Could you please help me to really save pictures inside the file?
Reply
enrico rinero
enrico rinero     Ed
This comment was minimized by the moderator on the site
Hi! I have the same question.
There is a way to save the image effectively in the excel? (and not linked outside)
Reply
There are no comments posted here yet
Load More
Leave your comments
Guest
Posting as Guest
Login
×
Rate this post:
Reset
0   Characters
Suggested Locations