Are there any NON-Visual Basic solutions for inserting a picture based a derived or called filename?

[The Great Attractor]

Any standard cell functions that would allow a picture to be placed in a
cell based on the data fed into that cell?

Is the only solution to utilize VB macros?

 

[Bernie Deitrick]

Is the only solution to utilize VB macros?

Yes, that is the only solution, at least through XL 2003

HTH,
Bernie
MS Excel MVP

 

[The Great Attractor]

After receiving my initial assistance, and knowing what I had to look
for, I did some hunting, and came up with some code segments that make
direct file calls. This will work, because I can list the filenames tied
to the ID code/key field I have been using, and use the VLOOKUP function
to grab the filename, and the VB code to paste the image.

Here is what I found:

http://www.exceltip.com/st/Insert_pictures_using_VBA_in_Microsoft_Excel/486.html

To quote:

» Insert pictures using VBA in Microsoft Excel

VBA macro tip contributed by Erlandsen Data Consulting offering Microsoft
Excel Application development, template customization, support and
training solutions

CATEGORY: General Topics in VBA

VERSIONS: All Microsoft Excel Versions

With the macro below you can insert pictures at any range in a worksheet.
The picture can be centered horizontally and/or vertically.

Sub TestInsertPicture()
InsertPicture "C:\FolderName\PictureFileName.gif", _
Range("D10"), True, True
End Sub

Sub InsertPicture(PictureFileName As String, TargetCell As Range, _
CenterH As Boolean, CenterV As Boolean)
' inserts a picture at the top left position of TargetCell
' the picture can be centered horizontally and/or vertically
Dim p As Object, t As Double, l As Double, w As Double, h As Double
If TypeName(ActiveSheet) <> "Worksheet" Then Exit Sub
If Dir(PictureFileName) = "" Then Exit Sub
' import picture
Set p = ActiveSheet.Pictures.Insert(PictureFileName)
' determine positions
With TargetCell
t = .Top
l = .Left
If CenterH Then
w = .Offset(0, 1).Left - .Left
l = l + w / 2 - p.Width / 2
If l < 1 Then l = 1
End If
If CenterV Then
h = .Offset(1, 0).Top - .Top
t = t + h / 2 - p.Height / 2
If t < 1 Then t = 1
End If
End With
' position picture
With p
.Top = t
.Left = l
End With
Set p = Nothing
End Sub

With the macro below you can insert pictures and fit them to any range in
a worksheet.

Sub TestInsertPictureInRange()
InsertPictureInRange "C:\FolderName\PictureFileName.gif", _
Range("B510")
End Sub

Sub InsertPictureInRange(PictureFileName As String, TargetCells As Range)
' inserts a picture and resizes it to fit the TargetCells range
Dim p As Object, t As Double, l As Double, w As Double, h As Double
If TypeName(ActiveSheet) <> "Worksheet" Then Exit Sub
If Dir(PictureFileName) = "" Then Exit Sub
' import picture
Set p = ActiveSheet.Pictures.Insert(PictureFileName)
' determine positions
With TargetCells
t = .Top
l = .Left
w = .Offset(0, .Columns.Count).Left - .Left
h = .Offset(.Rows.Count, 0).Top - .Top
End With
' position picture
With p
.Top = t
.Left = l
.Width = w
.Height = h
End With
Set p = Nothing
End Sub

In case anyone else is interested.

Thank you for your help, and the need for VB qualification.
This gives the terms "Visual Basic" a whole new meaning... or not. :-]

 

[JackShepherd]

Sub TestInsertPicture()
InsertPicture "C:\FolderName\PictureFileName.gif", _
Range("D10"), True, True
End Sub

This is the macro I call this picture popup with, but how to I replace
the hard coded filename given here with a cell contents reference?

I keep getting the syntax wrong. The code wants a string, but I should
be able to inject that string based on a cell's contents, no?

Can anyone show me how =CELL("contents", H7) can be plugged in to that
code segment in place of the filename string, or an even quicker call to
that cell's contents? That cell (H7) gets filled by a VLOOKUP function
based on which film I have up in my view panel at the time. I then stack
the image on top of that via the macro.

 

[Bernie Deitrick]

It depends on what is in cell H7.

Just the file name, no extension:

InsertPicture "C:\FolderName\" & Range("H7").Value & ".gif", _
Range("D10"), True, True

Filename and extension:
InsertPicture "C:\FolderName\" & Range("H7").Value, _
Range("D10"), True, True


Full path and name with extension:
InsertPicture Range("H7").Value, _
Range("D10"), True, True

HTH,
Bernie
MS Excel MVP

 

[JackShepherd]

It depends on what is in cell H7.

Full path and name with extension:
InsertPicture Range("H7").Value, _
Range("D10"), True, True

HTH,
Bernie
MS Excel MVP

OK, that worked, except I had to go through and remove the quotes from
all the cell contents. (Thank You, btw).

Now, I need a routine that will clear the picture from that cell,
("D10"), but not other pictures in the sheet, just before running the
picture popup code, so that they don't simply stack up on top of each
other, as they do now. Clearing the cell contents fails as it leaves the
picture untouched. There are three other pictures in the sheet... I
wonder if simply clearing "Picture 4" will work.

 

[Bernie Deitrick]

Jack,

Something like this, where you delete the picture first: note the name
"PictureD10". You need to change the InsertPicture sub that you are using
as below, to name the picture when it is inserted, and change how you call
the function.

Sub InsertPic()
On Error Resume Next
ActiveSheet.Shapes("PictureD10").Delete
InsertPicture Range("H7").Value, _
Range("D10"), True, True, "PictureD10"
End Sub

Sub InsertPicture(PictureFileName As String, TargetCell As Range, _
CenterH As Boolean, CenterV As Boolean, picName As String)
' inserts a picture at the top left position of TargetCell
' the picture can be centered horizontally and/or vertically
Dim p As Object, t As Double, l As Double, w As Double, h As Double
If TypeName(ActiveSheet) <> "Worksheet" Then Exit Sub
If Dir(PictureFileName) = "" Then Exit Sub
' import picture
Set p = ActiveSheet.Pictures.Insert(PictureFileName)
'Name the picture so you can delete it later....
p.Name = picName
' determine positions
'then the rest of your code here....


HTH,
Bernie
MS Excel MVP

 

[JackShepherd]

Jack,

Something like this, where you delete the picture first: note the name
"PictureD10".

Yes. The code works perfectly. I renamed the sub "Pop", and the
picture name "Popped", but everything else remains the same.

I think I can do the one remaining function I seek, which is to scale
the picture to fit my area. This way, I don't have to scale them by hand
as I add them to my image archive.


Thank you for all of your help.

You Da Man!