Inserting an Online Picture to Excel with VBA

Question

I'm currently working on a project and need to fill in cells with pictures via URLs. All URLs are in one column, and I'd like to load the images in an adjacent column. I'm no VBA expert, but I found some code that worked, but for some reason I get an error (usually 5 images in) that says:

Run-time error '1004': Unable to get the Insert property of the Pictures Class

Again, I'm using a system where URLs are in one column i.e.:

xxxx.com/xxxx1.jpg

xxxx.com/xxxx2.jpg

xxxx.com/xxxx3.jpg

xxxx.com/xxxx4.jpg

Through some searching, I found that it could be linked to my Excel version (using 2010), though I'm not completely sure.

Here's the current code I'm using:

Sub URLPictureInsert()
Dim cell, shp As Shape, target As Range
    Set Rng = ActiveSheet.Range("a5:a50") ' range with URLs
    For Each cell In Rng
       filenam = cell
       ActiveSheet.Pictures.Insert(filenam).Select

  Set shp = Selection.ShapeRange.Item(1)
   With shp
      .LockAspectRatio = msoTrue
      .Width = 100
      .Height = 100
      .Cut
   End With
   Cells(cell.Row, cell.Column + 1).PasteSpecial
Next

End Sub

Any help would be much appreciated!

Original code source: http://www.mrexcel.com/forum/excel-questions/659968-insert-image-into-cell-url-macro.html

Works for me... When you get an error what is the value of `filenam`? — Siddharth Rout, Apr 19 '13 at 21:45
I answered a very similar question recently. let me see if I can find it. — David Zemens, Apr 20 '13 at 02:53
Try: http://stackoverflow.com/questions/15588995/excel-vba-insert-images-from-image-name-in-column/15589423#15589423 — David Zemens, Apr 20 '13 at 02:58

score 7 · Accepted Answer · edited May 23 '17 at 12:19

7

This is an almost identical solution that I posted about a month ago:

Excel VBA Insert Images From Image Name in Column

Sub InsertPic()
Dim pic As String 'file path of pic
Dim myPicture As Picture 'embedded pic
Dim rng As Range 'range over which we will iterate
Dim cl As Range 'iterator

Set rng = Range("B1:B7")  '<~~ Modify this range as needed. Assumes image link URL in column A.
For Each cl In rng
pic = cl.Offset(0, -1)

    Set myPicture = ActiveSheet.Pictures.Insert(pic)
    '
    'you can play with this to manipulate the size & position of the picture.
    ' currently this shrinks the picture to fit inside the cell.
    With myPicture
        .ShapeRange.LockAspectRatio = msoFalse
        .Width = cl.Width
        .Height = cl.Height
        .Top = Rows(cl.Row).Top
        .Left = Columns(cl.Column).Left
    End With
    '

 Next

 End Sub

edited May 23 '17 at 12:19

Community

1
1

answered Apr 20 '13 at 03:00

David Zemens

53,033
11
81
130

David, thanks for the help! Everything seems to be working now. Although a bit embarrassing, it looks like the main component missing was the bit of code to go over any errors: On Error Resume Next After that, it looks like everything's running smoothly. Only obstacle here is that if I run this for more than 200 rows, excel freezes up. However, that's a quick workaround and could be a problem with my computer setup. – Richard Oppenheimer Apr 22 '13 at 18:19
2

`On Error Resume Next` should probably be avoided. It is a better practice to trap the errors and code for potential exceptions, rather than to simply *ignore* them, which is what that statement does. – David Zemens Apr 22 '13 at 18:28

score 1 · Answer 2 · answered Aug 08 '18 at 22:05

I know this thread is 5 years old but just wanted to say it really helped me with a project. I'm using VBA to bring in data from an orders database. When I click on an order from those results it brings in more details about the orders including an image URL. The problem I had was that the code above was designed to add the image in place of the URL. I wanted to replace the image from a previous query with the image from the new query. After some tweaks I got it working but it was just laying a new image on top of the old image. In time my Excel file could get really big so here's my solution. The only problem I have right now is that it deletes my company's logo that I put on the sheet. There may be a way to be more selective, or I could just change the procedure to insert the logo from another sheet in the workbook every time it deletes pictures but that seems a bit cheesy.

Sub InsertPic()

Dim productImageUrl As String
Dim productImage As Picture     'Declare image picture object
Dim productImageUrlRng As Range 'Declare range object to contain image URL
Dim productImageRng As Range    'Location image will be placed
'Delete any existing pictures:


Set productImageRng = ActiveSheet.Range("J1:J15") 'Where I want to put the image
Set productImageUrlRng = Range("BA2")  'Cell containing image URL
productImageUrl = productImageUrlRng

productImageRng.Select
'productImageRng.Delete --Does not delete pictures in range
ActiveSheet.Pictures.Delete     'Delete existing images
Set productImage = ActiveSheet.Pictures.Insert(productImageUrl)

With productImage
    .ShapeRange.LockAspectRatio = msoTrue
    '.Width = productImageRng.Width
    .Height = productImageRng.Height
    ' .Top = Rows(cl.Row).Top
    ' .Left = Columns(cl.Column).Left
End With
End Sub

don't delete all pics.to delete selective picture with in a range do this. https://www.mrexcel.com/board/threads/delete-pictures-within-a-range-vba.937804/ (try) — ihightower, Feb 27 '21 at 01:48

Inserting an Online Picture to Excel with VBA

2 Answers2

Linked