How can I insert a picture from a file into a cell?

[Lil]

Hi,

I am new to writing Excel Macros and have been working on this for
couple of days now and can't seem to get it going. Someone pleas
help!!

I have a worksheet in which one column holds the file name. I need t
find this file in a directory on the C:/drive and display this pictur
in a cell in the appropriate row.

Here's some code that I tried but didn't work:

Public Sub DisplayPics()
Dim b As Long
Dim strFilePath As String
Dim pic As Shape

'Start at row 2 as the first row is a header row.
b = 2

Set sheet = Worksheets("Sheet2")
count = sheet.UsedRange.Rows.count

'Insert column to insert picture.
Worksheets("Sheet2").Cells(1, 3).EntireColumn.Insert

'look for the jpg file name in column i, find the file in the loca
drive and insert
'the picture in column C

While b <= count
If Len(sheet.Cells(b, 1)) > 0 Then
strFilePath = "C:/graphics/" & Cells(b, 9).Value

If Dir(strFilePath) = "" Then
Worksheets("Sheet2").Cells(b, 3).Value = "No Phot
Available"
Else
Worksheets("Sheet2").Cells(b, 3).Pictures.Inser
(strFilePath)
Set pic = ActiveSheet.Shapes(Activesheeet.Shape.count)
Selection.ShapeRange.ScaleWidth 0.5, msoFalse
msoScaleFromTopLeft 'This is on a single line
Selection.ShapeRange.ScaleHeight 0.5, msoFalse
msoScaleFromTopLeft 'This is on a single line
Rows(b).RowHeight = 90

End If
End If

b = b + 1

Wend

End Sub

At this line "Worksheets("Sheet2").Cells(b, 3).Pictures.Inser
(strFilePath)", the error is: "Object does not support this property o
method"

Any suggestions would be great.

Many thanks in advance for your help,
Lillian

 

[Dave Peterson]

You insert a picture onto a sheet--not into a cell.

But I made some other changes, too. I don't like variables that look too much
like VBA's reserved words--Sheet and Count scare me.

I also declared Pic to be a picture.

I was also confused about why you checked the length of the value in column "A",
but then use the value in column "I".

If Len(sheet.Cells(b, 1)) > 0 Then
strFilePath = "C:/graphics/" & Cells(b, 9).Value

Option Explicit
Public Sub DisplayPics2()
Dim b As Long
Dim strFilePath As String
Dim pic As Picture
Dim mySheet As Worksheet
Dim myCount As Long

'Start at row 2 as the first row is a header row.
b = 2

Set mySheet = Worksheets("sheet2")
myCount = mySheet.UsedRange.Rows.Count

'Insert column to insert picture.
mySheet.Cells(1, 3).EntireColumn.Insert

'look for the jpg file name in column i,
'find the file in the local drive and insert
'the picture in column C

While b <= myCount
strFilePath = "C:\my documents\my pictures\" _
& mySheet.Cells(b, 9).Value
If Len(Trim(mySheet.Cells(b, 9).Value)) = 0 Then
mySheet.Cells(b, 3).Value = "No Photo Available"
ElseIf Dir(strFilePath) = "" Then
mySheet.Cells(b, 3).Value = "No Photo Available"
Else
Set pic = mySheet.Pictures.Insert(strFilePath)
With pic
.Top = mySheet.Cells(b, 3).Top
.Left = mySheet.Cells(b, 3).Left
.Width = mySheet.Cells(b, 3).Width
.Height = mySheet.Cells(b, 3).Height
End With
pic.ShapeRange.ScaleWidth 0.5, msoFalse, msoScaleFromTopLeft
pic.ShapeRange.ScaleHeight 0.5, msoFalse, msoScaleFromTopLeft
Rows(b).RowHeight = 90
End If

b = b + 1
Wend

End Sub

 

[Lil]

Hi Dave,

Thanks for your quick response. I guess I wan't very clear. I needed t
insert a different picture for each row in the worksheet. These picture
are held in a directory on the c:/ drive and each row has the file nam
of the picture to display.

Would the code you give me do this? Or would it just display on
picture.

Many thanks,
Lillian

 

[Dave Peterson]

That code worked ok for me.

Did you remember to change the folder name? I didn't change it back after I
tested.

And it looks for names of files that are in column 9--after you inserted a new
column. So the names should have been in column 8 (H) before the code starts.