Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Inserting an Online Picture to Excel with VBA

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

like image 422
Richard Oppenheimer Avatar asked Apr 19 '13 21:04

Richard Oppenheimer


People also ask

How do I display an image in Excel VBA?

Press Alt + F11 to start the Visual Basic Editor (VBE). Press with left mouse button on "Insert" on the menu, see image above. Press with left mouse button on "Module". Paste VBA code to window.


1 Answers

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
like image 103
David Zemens Avatar answered Nov 06 '22 09:11

David Zemens