Inserting Pictures Based On File name
 

I have this code from another thread posted by Joel, and I want
to find out if it will work for me

On my sheet I have a file name in column A and this is the name of
the file I want to insert automatically into that cell or on top of that
cell.

The number of rows changes from sheet to sheet, and to allow room
for the picture, the rows may be, the first three, then the next three then
the next three

So my sheet may look like this;

ball.bmp | 1 | "need image inserted into cell A1"
|.750 |
| 3.0 |
bull.bmp | 1 | "need image inserted into cell A4"
|.500 |
| 3.5 |
squ .bmp | 1 | "need image inserted into cell A7"
|.375 |
| 3.2 |

etc, etc as long as the sheet might be...

I think, I would be checking every fourth row for a file name

Would this code work or would I be better of using something else?

Thanks

Jeff W.

.................................................. .................................................. ...................
The code deletes all old pictures and then adds all the pictures. It is not
easy to only delete the pictures that have changed.

Sub add_pictures()

Const PictureHeight = 25

'delete pictures
For Each shp In ActiveSheet.Shapes
If shp.Type = msoPicture Then
shp.Delete
End If
Next shp

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Rows(2).RowHeight = PictureHeight

For Each cell In Range("B1:K1")
If cell < "" Then
Set pict = ActiveSheet.Pictures. _
Insert(cell.Value)
pict.ShapeRange.LockAspectRatio = msoTrue
pict.ShapeRange.Height = PictureHeight
pict.Top = cell.Offset(1, 0).Top
pict.Left = cell.Offset(1, 0).Left
End If
Next cell

End Sub

.................................................. .................................................. ...................

Inserting Pictures Based On File name
 

Not sure if will assist or hinder, but i use the following code to input a picture into a sheet
based on Cell(A1):

' Photo 1
Application.ScreenUpdating = False
Dim WB As Workbook
Dim SH As Worksheet
Dim rng As Range
Dim mypic As Picture
Dim res As Variant
Set WB = ActiveWorkbook
res = sheet1.Range("A1").value
If res = False Then Exit Sub
Set SH = ActiveSheet
Set rng = ActiveCell
Set mypic = SH.Pictures.Insert(res)
With mypic
.Top = rng.Top
.Left = rng.Left
.Locked = False
mypic.ShapeRange.LockAspectRatio = msoFalse
mypic.ShapeRange.Height = 213.1
mypic.ShapeRange.Width = 249.2
mypic.ShapeRange.Rotation = 0#
End With
End If

Corey....

"Jeff W." wrote in message ...
I have this code from another thread posted by Joel, and I want
to find out if it will work for me

On my sheet I have a file name in column A and this is the name of
the file I want to insert automatically into that cell or on top of that
cell.

The number of rows changes from sheet to sheet, and to allow room
for the picture, the rows may be, the first three, then the next three then
the next three

So my sheet may look like this;

ball.bmp | 1 | "need image inserted into cell A1"
|.750 |
| 3.0 |
bull.bmp | 1 | "need image inserted into cell A4"
|.500 |
| 3.5 |
squ .bmp | 1 | "need image inserted into cell A7"
|.375 |
| 3.2 |

etc, etc as long as the sheet might be...

I think, I would be checking every fourth row for a file name

Would this code work or would I be better of using something else?

Thanks

Jeff W.

.................................................. .................................................. ...................
The code deletes all old pictures and then adds all the pictures. It is not
easy to only delete the pictures that have changed.

Sub add_pictures()

Const PictureHeight = 25

'delete pictures
For Each shp In ActiveSheet.Shapes
If shp.Type = msoPicture Then
shp.Delete
End If
Next shp

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Rows(2).RowHeight = PictureHeight

For Each cell In Range("B1:K1")
If cell < "" Then
Set pict = ActiveSheet.Pictures. _
Insert(cell.Value)
pict.ShapeRange.LockAspectRatio = msoTrue
pict.ShapeRange.Height = PictureHeight
pict.Top = cell.Offset(1, 0).Top
pict.Left = cell.Offset(1, 0).Left
End If
Next cell

End Sub

.................................................. .................................................. ...................

Inserting Pictures Based On File name
 

Thank you for the responce Cory, although I do have a concern or two.

I have my image files in a sub folder of the workbook folder, for example

c:\setup This is where the wookbook is stored
c:\setup\images This is where the images files are

Would I need something to point to this folder in order for it
to find the images files?

I'm assuming that the code you supplied would need to be in a sub routine
then ran when I wanted to get the pictures inserted, is this correct?


Thanks,

<Jeff


"Corey" wrote in message
...
Not sure if will assist or hinder, but i use the following code to input a
picture into a sheet
based on Cell(A1):

' Photo 1
Application.ScreenUpdating = False
Dim WB As Workbook
Dim SH As Worksheet
Dim rng As Range
Dim mypic As Picture
Dim res As Variant
Set WB = ActiveWorkbook
res = sheet1.Range("A1").value
If res = False Then Exit Sub
Set SH = ActiveSheet
Set rng = ActiveCell
Set mypic = SH.Pictures.Insert(res)
With mypic
.Top = rng.Top
.Left = rng.Left
.Locked = False
mypic.ShapeRange.LockAspectRatio = msoFalse
mypic.ShapeRange.Height = 213.1
mypic.ShapeRange.Width = 249.2
mypic.ShapeRange.Rotation = 0#
End With
End If

Corey....

"Jeff W." wrote in message
...
I have this code from another thread posted by Joel, and I want
to find out if it will work for me

On my sheet I have a file name in column A and this is the name of
the file I want to insert automatically into that cell or on top of that
cell.

The number of rows changes from sheet to sheet, and to allow room
for the picture, the rows may be, the first three, then the next three
then
the next three

So my sheet may look like this;

ball.bmp | 1 | "need image inserted into cell A1"
|.750 |
| 3.0 |
bull.bmp | 1 | "need image inserted into cell A4"
|.500 |
| 3.5 |
squ .bmp | 1 | "need image inserted into cell A7"
|.375 |
| 3.2 |

etc, etc as long as the sheet might be...

I think, I would be checking every fourth row for a file name

Would this code work or would I be better of using something else?

Thanks

Jeff W.

.................................................. .................................................. ..................
The code deletes all old pictures and then adds all the pictures. It is
not
easy to only delete the pictures that have changed.

Sub add_pictures()

Const PictureHeight = 25

'delete pictures
For Each shp In ActiveSheet.Shapes
If shp.Type = msoPicture Then
shp.Delete
End If
Next shp

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Rows(2).RowHeight = PictureHeight

For Each cell In Range("B1:K1")
If cell < "" Then
Set pict = ActiveSheet.Pictures. _
Insert(cell.Value)
pict.ShapeRange.LockAspectRatio = msoTrue
pict.ShapeRange.Height = PictureHeight
pict.Top = cell.Offset(1, 0).Top
pict.Left = cell.Offset(1, 0).Left
End If
Next cell

End Sub

.................................................. .................................................. ..................

Inserting Pictures Based On File name
 

Jeff,
The code i pasted uses (res)

I have a cell below the picture location with the address of the image/pic like :
\\Server\server\My Pictures\ABC123.jpg

This way it does not matter where the pictures are stored provided they are not renamed or moved.

Corey....
"Jeff W." wrote in message ...
Thank you for the responce Cory, although I do have a concern or two.

I have my image files in a sub folder of the workbook folder, for example

c:\setup This is where the wookbook is stored
c:\setup\images This is where the images files are

Would I need something to point to this folder in order for it
to find the images files?

I'm assuming that the code you supplied would need to be in a sub routine
then ran when I wanted to get the pictures inserted, is this correct?


Thanks,

<Jeff


"Corey" wrote in message
...
Not sure if will assist or hinder, but i use the following code to input a
picture into a sheet
based on Cell(A1):

' Photo 1
Application.ScreenUpdating = False
Dim WB As Workbook
Dim SH As Worksheet
Dim rng As Range
Dim mypic As Picture
Dim res As Variant
Set WB = ActiveWorkbook
res = sheet1.Range("A1").value
If res = False Then Exit Sub
Set SH = ActiveSheet
Set rng = ActiveCell
Set mypic = SH.Pictures.Insert(res)
With mypic
.Top = rng.Top
.Left = rng.Left
.Locked = False
mypic.ShapeRange.LockAspectRatio = msoFalse
mypic.ShapeRange.Height = 213.1
mypic.ShapeRange.Width = 249.2
mypic.ShapeRange.Rotation = 0#
End With
End If

Corey....

"Jeff W." wrote in message
...
I have this code from another thread posted by Joel, and I want
to find out if it will work for me

On my sheet I have a file name in column A and this is the name of
the file I want to insert automatically into that cell or on top of that
cell.

The number of rows changes from sheet to sheet, and to allow room
for the picture, the rows may be, the first three, then the next three
then
the next three

So my sheet may look like this;

ball.bmp | 1 | "need image inserted into cell A1"
|.750 |
| 3.0 |
bull.bmp | 1 | "need image inserted into cell A4"
|.500 |
| 3.5 |
squ .bmp | 1 | "need image inserted into cell A7"
|.375 |
| 3.2 |

etc, etc as long as the sheet might be...

I think, I would be checking every fourth row for a file name

Would this code work or would I be better of using something else?

Thanks

Jeff W.

.................................................. .................................................. ..................
The code deletes all old pictures and then adds all the pictures. It is
not
easy to only delete the pictures that have changed.

Sub add_pictures()

Const PictureHeight = 25

'delete pictures
For Each shp In ActiveSheet.Shapes
If shp.Type = msoPicture Then
shp.Delete
End If
Next shp

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Rows(2).RowHeight = PictureHeight

For Each cell In Range("B1:K1")
If cell < "" Then
Set pict = ActiveSheet.Pictures. _
Insert(cell.Value)
pict.ShapeRange.LockAspectRatio = msoTrue
pict.ShapeRange.Height = PictureHeight
pict.Top = cell.Offset(1, 0).Top
pict.Left = cell.Offset(1, 0).Left
End If
Next cell

End Sub

.................................................. .................................................. ..................

Inserting Pictures Based On File name
 

Cory, I'm sorry but I don't understand

I'm not really a VBA programmer so there are many things that I don't
to change or add in order to get this to work.

On my sheet, in cell A1 I have ball.gif in cell A4 I have bore.gif
in cell A7 I have bull.gif and in cell A10 I have center.gif

I pasted that code into a module inside a sub and it didn't run when I
called
that sub, I have to rem out the "end if" line but then when I run it , it
error's
out on the following line;

Set mypic = SH.Pictures.Insert(res)

I mean this is the line the debugger high lights and I don't really know why
or
where to go from here...

<Jeff


"Corey" wrote in message
...
Jeff,
The code i pasted uses (res)

I have a cell below the picture location with the address of the image/pic
like :
\\Server\server\My Pictures\ABC123.jpg

This way it does not matter where the pictures are stored provided they
are not renamed or moved.

Corey....
"Jeff W." wrote in message
...
Thank you for the responce Cory, although I do have a concern or two.

I have my image files in a sub folder of the workbook folder, for example

c:\setup This is where the wookbook is stored
c:\setup\images This is where the images files are

Would I need something to point to this folder in order for it
to find the images files?

I'm assuming that the code you supplied would need to be in a sub routine
then ran when I wanted to get the pictures inserted, is this correct?


Thanks,

<Jeff


"Corey" wrote in message
...
Not sure if will assist or hinder, but i use the following code to input
a
picture into a sheet
based on Cell(A1):

' Photo 1
Application.ScreenUpdating = False
Dim WB As Workbook
Dim SH As Worksheet
Dim rng As Range
Dim mypic As Picture
Dim res As Variant
Set WB = ActiveWorkbook
res = sheet1.Range("A1").value
If res = False Then Exit Sub
Set SH = ActiveSheet
Set rng = ActiveCell
Set mypic = SH.Pictures.Insert(res)
With mypic
.Top = rng.Top
.Left = rng.Left
.Locked = False
mypic.ShapeRange.LockAspectRatio = msoFalse
mypic.ShapeRange.Height = 213.1
mypic.ShapeRange.Width = 249.2
mypic.ShapeRange.Rotation = 0#
End With
End If

Corey....

"Jeff W." wrote in message
...
I have this code from another thread posted by Joel, and I want
to find out if it will work for me

On my sheet I have a file name in column A and this is the name of
the file I want to insert automatically into that cell or on top of that
cell.

The number of rows changes from sheet to sheet, and to allow room
for the picture, the rows may be, the first three, then the next three
then
the next three

So my sheet may look like this;

ball.bmp | 1 | "need image inserted into cell A1"
|.750 |
| 3.0 |
bull.bmp | 1 | "need image inserted into cell A4"
|.500 |
| 3.5 |
squ .bmp | 1 | "need image inserted into cell A7"
|.375 |
| 3.2 |

etc, etc as long as the sheet might be...

I think, I would be checking every fourth row for a file name

Would this code work or would I be better of using something else?

Thanks

Jeff W.

.................................................. .................................................. ..................
The code deletes all old pictures and then adds all the pictures. It is
not
easy to only delete the pictures that have changed.

Sub add_pictures()

Const PictureHeight = 25

'delete pictures
For Each shp In ActiveSheet.Shapes
If shp.Type = msoPicture Then
shp.Delete
End If
Next shp

LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Rows(2).RowHeight = PictureHeight

For Each cell In Range("B1:K1")
If cell < "" Then
Set pict = ActiveSheet.Pictures. _
Insert(cell.Value)
pict.ShapeRange.LockAspectRatio = msoTrue
pict.ShapeRange.Height = PictureHeight
pict.Top = cell.Offset(1, 0).Top
pict.Left = cell.Offset(1, 0).Left
End If
Next cell

End Sub

.................................................. .................................................. ..................