0

I have an Excel workbook (Excel 2013, Windows 10) containing a column of paths to jpg files on my computer (column "B") and I'd like to have a macro which will work through all of the rows inserting a thumbnail of the picture into the cell to the left of the path, ie in column "A".

I'm no macro VBA coder and usually record macros close to what I want and then edit the code it records for me. So I know that I can write a macro which simply records me doing this and the macro runs. However when I try to generalise it, I get an error, although the error is on the same line as ran fine previously.

So, this macro works -

Sub Macro1()
'
' Macro1 Macro
'
    Range("A4").Select
    ActiveSheet.Pictures.Insert("D:\My Documents HDD\...(path)...\Filename.jpg").Select
End Sub

However this doesn't work -

Sub Insert_picture_from_URL()
'
' Insert_picture_from_URL Macro
'
    Dim i As Long, v As String, w As String
        For i = 1 To 200
            v = ("B" & i)
            w = ("A" & i)
            Range(w).Select
            ActiveSheet.Pictures.Insert(v).Select
        Next i
End Sub

I get "Run-time error '1004': Unable to get the Insert property of the Pictures class"

Please can you tell me where I'm going wrong? I realise I'll need to add code to resize the thumbnails to a reasonable size, but I need to get this to work first.

Thanks.

Gary's Student
  • 95,722
  • 10
  • 59
  • 99
Stuart O
  • 13
  • 1
  • 1
  • 5
  • Possible duplicate of [Inserting an Online Picture to Excel with VBA](https://stackoverflow.com/questions/16113876/inserting-an-online-picture-to-excel-with-vba). – BigBen Oct 03 '18 at 16:45
  • 1
    `v = ("B" & i)` - so `ActiveSheet.Pictures.Insert(v)`.... is equivalent to `ActiveSheet.Pictures.Insert("B1")`, `"B2"` and so on. `"B1"` is not a reference to a `Range`, but `Range("B1")` is. – BigBen Oct 03 '18 at 16:47

1 Answers1

1

Untested:

Sub Insert_picture_from_URL()
'
' Insert_picture_from_URL Macro
'
    Dim i As Long, v As String, w As String
        For i = 1 To 200
            With ActiveSheet.Pictures.Insert(Cells(i, 2).Value)
                .Top = Cells(i, 1).Top
                .Left = Cells(i, 1).Left
            End With
        Next i
End Sub
Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • Unfortunately this returns the error "Run-time error 1004. Unable to get the Insert property of the Pictures class" as before. – Stuart O Oct 04 '18 at 06:36
  • I've followed the link at the top of this question and the code there work well enough for me to modify for my need. Thanks, I did search before posting but failed to find that answer. – Stuart O Oct 04 '18 at 06:50