Inserting Photos into Excel

[garrett1483]

Hello All - First time using a forum but I thought this may be the only way to get the answer I need. I have an excel sheet and in Column A, I've got item#'s e.g. (A1007.jpg, A1008.jpg etc...). I have the corresponding photos in a folder on my desktop. I'd like a macro that will auto populate all the photos into the excel sheet in Column A, so the item#'s and the photos match up. Is there a way to do this? Any/All help would be appreciated.

Thanks
Garrett

 

[garrett1483]

The error says

Run-time error '1004':
Method 'Range' of object '_Worksheet' failed

The code I used is below:

Public Sub Test()
Dim sPath As String
Dim oCell As Range
Dim oRange As Range
Dim oPicture As Shape
Dim oSheet As Worksheet
sPath = "C:\Documents and Settings\gtsuyuki\Desktop\Crazy Close Out Photos\" 'Change to suit
Set oSheet = ActiveSheet
Set oRange = oSheet.Range("A1:A104" & oSheet.Range("A" & Rows.Count).End(xlUp).Row)
For Each oCell In oRange
If Dir(sPath & oCell.Text) <> "" And oCell.Value <> "" Then
Set oPicture = oSheet.Shapes.AddPicture(Filename:=sPath & oCell, LinktoFile:=msoFalse, savewithdocument:=msoTrue, Left:=oCell.Left, Top:=oCell.Top, Width:=1, Height:=1)
oPicture.ScaleHeight 1, True
oPicture.ScaleWidth 1, True
oCell.RowHeight = oPicture.Height
oCell.ColumnWidth = oPicture.Width / 4
Else
oCell.Offset(0, 1).Value = "Image file not found"
End If
Next oCell
End Sub

Highlighted in red is the error -

thanks
garrett

 

[repairman615]

Have you givin the code I posted a shot?

change your path and first cell in column A:A and you shoud be ready to test.

Jeff

 

[Gary McMaster]

You've messed up the following line:

Set oRange = oSheet.Range("A1:A104" & oSheet.Range("A" & Rows.Count).End(xlUp).Row)

It should be either hard coded like this:

Set oRange = oSheet.Range("A1:A104")

It was originally finding the last used row in column A and looked like this:

Set oRange = oSheet.Range("A1:A" & oSheet.Range("A" & Rows.Count).End(xlUp).Row)

You have it trying to do both at the same time. Try setting that straight first.

Gary

 

[garrett1483]

OMG!!! You guys are great - Thanks so much for your help. I didn't try the other version as i'm a total Newb to macros but I got the solution down. Thanks again guys!!!