Adding Pictures to Excel from a Directory, based on a cell value 1

[jmarkus]

I have written this simple code to insert an image in a specific cell in an excel worksheet:

Private Sub Workbook_Open()
Set oPic = Application.ActiveSheet.Shapes.AddPicture("C:\TEMP\image-name.jpg", False, True, 1, 1, 1, 1)
oPic.ScaleHeight 0.5, True
oPic.ScaleWidth 0.5, True
oPic.LockAspectRatio = msoFalse
oPic.Rotation = 0#
oPic.Top = Range("A5").Top
oPic.Left = Range("A5").Left
End Sub

However, what I really want to do is look in cell B5 and use that for my "image-name.jpg" and then go down to A6 and use B6, and so on, until there is no more rows (based on the populated rows of column B or perhaps the number of files in C:\TEMP - I haven't figured out which).

How do I recurse this code?

Thanks,
Jeff

 

[SkipVought]

hi,

Sub ImageImport()
    Dim r As Range
    
    For Each r In Range([B5], [B5].End(xlDown))
        oPic = ActiveSheet.Shapes.AddPicture("C:\TEMP\" & r.Value, False, True, 1, 1, 1, 1)
        oPic.ScaleHeight 0.5, True
        oPic.ScaleWidth 0.5, True
        oPic.LockAspectRatio = msoFalse
        oPic.Rotation = 0#
        With r.Offset(0, -1)
            oPic.Top = .Top
            oPic.Left = .Left
        End With
    Next
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

sorry I missed the Set

        Set oPIC = ActiveSheet.Shapes.AddPicture("C:\TEMP\" & r.Value, False, True, 1, 1, 1, 1)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jmarkus]

Thanks - that'll get the ball rolling...

Jeff