Insert online image in Excel using VBA

Question

I am currently working on a project and should fill cells with images at URLs. All urls are in one column, and I would like to load images in a neighboring column. I am not a VBA expert, but I found code that worked, but for some reason I get an error message (usually 5 images) that says:

Runtime Error "1004": Failed to get Insert property of Pictures class

Again, I am using a system in which the URLs are in the same ie column:

xxxx.com/xxxx1.jpg

xxxx.com/xxxx2.jpg

xxxx.com/xxxx3.jpg

xxxx.com/xxxx4.jpg

Through some searches, I found that it could be related to my version of Excel (using 2010), although I'm not quite sure.

Here is 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

!

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

Answer 1

, :

Excel VBA

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