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

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

--
Li
-----------------------------------------------------------------------
Lil's Profile: http://www.msusenet.com/member.php?userid=181
View this thread: http://www.msusenet.com/t-187050397

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

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 wrote:

Hi,

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

I have a worksheet in which one column holds the file name. I need to
find this file in a directory on the C:/drive and display this picture
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 local
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 Photo
Available"
Else
Worksheets("Sheet2").Cells(b, 3).Pictures.Insert
(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.Insert
(strFilePath)", the error is: "Object does not support this property or
method"

Any suggestions would be great.

Many thanks in advance for your help,
Lillian.

--
Lil
------------------------------------------------------------------------
Lil's Profile: http://www.msusenet.com/member.php?userid=1810
View this thread: http://www.msusenet.com/t-1870503977

--

Dave Peterson

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

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

--
Li
-----------------------------------------------------------------------
Lil's Profile: http://www.msusenet.com/member.php?userid=181
View this thread: http://www.msusenet.com/t-187050397

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

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.

Lil wrote:

Hi Dave,

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

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

Many thanks,
Lillian.

--
Lil
------------------------------------------------------------------------
Lil's Profile: http://www.msusenet.com/member.php?userid=1810
View this thread: http://www.msusenet.com/t-1870503977

--

Dave Peterson