converting paths to pictures

[[email protected]]

We currently keep a column in an excel spreadsheet that contains the
network path for the picture of the record. I would like to write a
macro that converts the path to a linked and/or embedded picture and if
possible resizes it to the cell.

I have been able to accomplish such a task in access (using an image
control and some VBA)but unfortunatly can't use access for this
project.

Is this possible?

Any help would be greatly apreciated, thanks in advance.

[Tom Ogilvy]

possibly if you use a control that supports it.

or you could have your macro pickup the path and name and import the
picture.

--
Regards,
Tom Ogilvy



<[email protected]> wrote in message
news:[email protected]...
> We currently keep a column in an excel spreadsheet that contains the
> network path for the picture of the record. I would like to write a
> macro that converts the path to a linked and/or embedded picture and if
> possible resizes it to the cell.
>
> I have been able to accomplish such a task in access (using an image
> control and some VBA)but unfortunatly can't use access for this
> project.
>
> Is this possible?
>
> Any help would be greatly apreciated, thanks in advance.
>

[[email protected]]

Could you give me an example of how i would get the macro to pickup the
pathname and import the picture?

Thanks!

[K Dales]

Function AddPicture(PicCell As Range, PicFilePath As String) As Shape
Dim MyPic As Shape
With PicCell
Set MyPic = .Parent.Shapes.AddShape(msoShapeRectangle, .Left, .Top,
..Width, .Height)
End With
MyPic.Fill.UserPicture PicFilePath
Set AddPicture = MyPic
End Function

Sub ChangePicture(ByVal MyPic As Shape, NewPicFilePath As String)
MyPic.Fill.UserPicture (NewPicFilePath)
End Sub

--
- K Dales


"[email protected]" wrote:

> We currently keep a column in an excel spreadsheet that contains the
> network path for the picture of the record. I would like to write a
> macro that converts the path to a linked and/or embedded picture and if
> possible resizes it to the cell.
>
> I have been able to accomplish such a task in access (using an image
> control and some VBA)but unfortunatly can't use access for this
> project.
>
> Is this possible?
>
> Any help would be greatly apreciated, thanks in advance.
>
>

[Tom Ogilvy]

In general:
ActiveSheet.Pictures.Insert( _
"C:\Documents and Settings\OgilvyTW\My Documents\My
Pictures\Sample.jpg"). _

so you could replace the argument with a cell reference


Range("B9").Select
Activesheet.Pictures.Insert Range("A2").Value

--
Regards,
Tom Ogilvy


<[email protected]> wrote in message
news:[email protected]...
> Could you give me an example of how i would get the macro to pickup the
> pathname and import the picture?
>
> Thanks!
>

[[email protected]]

I tried implementing your code, but when i run the macro nothing seems
to happen. Is there something in the code that i need to tweak? Am i
missing something?

[K Dales]

I developed and tested the code on Win 2000/Excel 2000, and tested it again
to verify it is working. Sorry I didn't clarify, though: the code snippets
are only the building blocks. Here is an entire macro that will illustrate
how it works (in this case I am using a button to create the picture and size
it, then a message box comes up and after clicking the picture source should
change - you can build your macro likewise by selecting the cell value from
your list):

Sub Button1_Click()
Dim NewPic As Shape, PicturePath1 as String, PicturePath2 as String
PicturePath1 = ' Insert path to 1st picture file
PicturePath2 = ' Insert path to 2nd picture file
With Range("A1")
.Width = 100
.Height = 66
End With
Set NewPic = AddPicture(Range("A1"), PicturePath1) ' Creates the picture in A1
MsgBox "PRESS OK TO CHANGE PICTURE:"
ChangePicture NewPic, PicturePath2 ' Changes the picture in A1
End Sub

Function AddPicture(PicCell As Range, PicFilePath As String) As Shape
Dim MyPic As Shape
With PicCell
Set MyPic = .Parent.Shapes.AddShape(msoShapeRectangle, .Left, .Top,
..Width, .Height)
End With
MyPic.Fill.UserPicture PicFilePath
Set AddPicture = MyPic
End Function

Sub ChangePicture(ByVal MyPic As Shape, NewPicFilePath As String)
MyPic.Fill.UserPicture (NewPicFilePath)
End Sub

Note that you can use AddPicture repeatedly to create multiple pictures on
the sheet, or use AddPicture once and then ChangePicture to use the same
rectangle but have the picture change. You could even create the rectangle
(as an autoshape) manually once and then use ChangePicture to dynamically
change it.

Not sure what application you have in mind, but suppose you have a database
list of employees with identifying info, including in one column a path to a
photo of them. You set up a form on a worksheet where the user types in an
employee number and (by using the macro in combination with lookup functions)
the picture changes as the employee info goes into other cells on the sheet.
Things like this are possible using this technique.
--
- K Dales


"[email protected]" wrote:

> I tried implementing your code, but when i run the macro nothing seems
> to happen. Is there something in the code that i need to tweak? Am i
> missing something?
>
>

[[email protected]]

Somehow i missed your reply, this is exactly what i want to do but with
two slight variations.

i want to do

Range("A1").Select
Activesheet.Pictures.Insert Range("A1").Value
&
Range("A2").Select
Activesheet.Pictures.Insert Range("A2").Value
&
Range("A3").Select
Activesheet.Pictures.Insert Range("A3").Value
ect....

i would also like to resize all of the pics to the same size ( lets say
60X30 pixels)

Thanks for the great help... alittle more would be apreciated though

[[email protected]]

got it doing what i wanted it to, here is the code i used:

Range("A2").Select
Set p = ActiveSheet.Pictures.Insert(Range("A2"))
p.Height = Range("A2").Height
p.Width = Range("A2").Width
ect..

Thanks for the help and the quick responces!