How to convert image URLs to actual images in Excel?
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 following screenshot shown. In Excel, how could you extract the actual pictures from the image URLs quickly and easily?
Covert file name to actual images at once in Excel:
With Kutools for Excel's Match Import Pictures feature, you can quickly import multiple images into the corresponding cells based on the file names.
Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!
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() 'Updateby Extendoffice 20161116 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 .Width = 100 .Height = 100 .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
Note: In the above code, A2:A6 is the range of cells which contains the URL addresses you want to extract the images, and also you can change the size of the image width and height to your need in the Width = 100, Height = 100 scripts.
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, see screenshot:
Recommended Productivity Tools
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 19 days agohey! with the Help of the Vba Code the Image has been extracted to the sheet but as i am moving my workbooks location the Image are Corrupted so help me here
To post as a guest, your comment is unpublished.· 2 months agoworking perfect
thanks a lot
To post as a guest, your comment is unpublished.· 3 months agoif the url is in https, for sure there is no way to work around it?
To post as a guest, your comment is unpublished.· 4 months agoHello I'm here asking for your help. My research ended up in 2 different macros that combined will give a good utility for my work.
1. Will insert image as comment, 2. Will fetch Hyperlinks(Local folder path only, not web based URL) and paste them in destination cell
I really tried to combine them to do one job, but I guess I don’t have enough knowledge on this.
I need the images to be inserted as comment as the 1st code does to the destination range user selects.
Dim PicturePath As String
Dim CommentBox As Comment
.AllowMultiSelect = True
.title = "Select Comment Image"
.ButtonName = "Insert Image"
.Filters.Add "Images", "*.png; *.jpg"
'Store Selected File Path
On Error GoTo UserCancelled
PicturePath = .SelectedItems(1)
On Error GoTo 0
'Clear Any Existing Comment
'Create a New Cell Comment
Set CommentBox = Application.ActiveCell.AddComment
'Remove Any Default Comment Text
'Insert The Image and Resize
CommentBox.Shape.ScaleHeight 6, msoFalse, msoScaleFormTopLeft
CommentBox.Shape.ScaleWidth 4.8, msoFalse, msoScaleFromTopLeft
'Ensure Comment is Hidden (Swith to TRUE if you want visible)
CommentBox.Visible = False
'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 as comment:", "KuTools for excel", , , , , , 8)
If xRg Is Nothing Then Exit Sub
Application.ScreenUpdating = False
For i = 1 To Rng.Count
filenam = Rng(i)
Set Pshp = Selection.ShapeRange.Item(1)
If Pshp Is Nothing Then GoTo lab
xCol = cell.Column + 1
Set xRg = xRg.Offset(i - 1, 0)
.LockAspectRatio = msoFalse
.Width = 80
.Height = 80
.Top = xRg.Top + (xRg.Height - .Height) / 2
.Left = xRg.Left + (xRg.Width - .Width) / 2
Set Pshp = Nothing
Application.ScreenUpdating = True
To post as a guest, your comment is unpublished.· 7 months agoHello
I try to get 33 images from URL but only gives me 1, all URL paths to the images are correct... what can that be?