Referencing images across Workbooks

I'm wondering if anyone can assist me with an image problem (no jokes please).

I'm trying to get a system setup with a workbook acting as a reference document, sort of read only and on a separate server. Which is then referenced by another "working" workbook, no problem so far other than I want to be able to view/show images from within the main reference workbook in the destination working workbook. I want this to happen when a user inputs a code, it should populate a series of cells including showing a small jpeg of said item.

For example if I use the camera function it doesn't do as desired when the main reference workbook is not currently open.

I was hoping to avoid macro's or vb if at all possible.

I'm a bit of an Excel novice though have limited programming experience in other languages / applications. I'm also a complete novice when it comes to forums and threads etc. so excuse me if I mess up protocol at all.

Anyway can anybody offer any pearls of wisdom on this connundrum?

Cheers

How are the pictures stored in the reference workbook?

At the moment they're just jpeg's inserted, but I'm open to any suggestions that may help facilitate this idea working?

Sorry I still do not understand. Can you post sample workbooks?

These attachments just indicate how using the Camera doesn't seem to work.

This is not a method I'm necessarily wedded to, if there is a better alternative that achieves the same basic result then I'm all ears!

I used code from http://en.allexperts.com/q/Excel-105...preadsheet.htm that inserted image based on file location in another cell. I modified it to use a lookup to the Reference workbook which lists possible image file locations. The lookup function is hidden beneath the retrieved picture in the Working worksheet. Open vba and you will see where the picure size can be adjusted. Here is the code, which I have added to your sample Working worksheet.
Function ShowPicD(PicFile As String) As Boolean
'Same as ShowPic except deletes previous picture when picfile changes
Dim AC As Range
Static P As Shape
On Error GoTo Done
Set AC = Application.Caller
If PicExists(P) Then
P.Delete
Else
'look for a picture already over cell
For Each P In ActiveSheet.Shapes
If P.Type = msoLinkedPicture Then
If P.Left >= AC.Left And P.Left < AC.Left + AC.Width Then
If P.Top >= AC.Top And P.Top < AC.Top + AC.Height Then
P.Delete
Exit For
End If
End If
End If
Next P
End If
'adjust picture size by changeing last 2 numbers
Set P = ActiveSheet.Shapes.AddPicture(PicFile, True, True, AC.Left, AC.Top, 200, 200)
ShowPicD = True
Exit Function
Done:
ShowPicD = False
End Function

Function PicExists(P As Shape) As Boolean
'Return true if P references an existing shape
Dim ShapeName As String
On Error GoTo NoPic
If P Is Nothing Then GoTo NoPic
ShapeName = P.Name
PicExists = True
NoPic:
PicExists = False
End Function

Call from a cell:
=ShowPicD(E9)

where cell E9 contains a picture file path like "C:\My Documents\123456.jpg".

This is excellent (Mr?) MHuston in Iowa, thanks for your assistance. One question though, is it possible with vba to establish the existence of an image in the cell already and to arbitrarily delete it before replacing it with the new image?

Am I interpreting the vba code in your workbook correctly that it memorises the image and only deletes it prior to inserting a new image IF it remembers the image is there?

Even better is it possible to interrogate the image and establish where it was sourced from (it's file location or file name)?

Basically I think you've solved my problem, I'm just trying to learn a bit now and add bells and whistles.

Oh and of course the whole world is watching Iowa in 12or13 days!

The function PicExists checks to see if there is already a picture in the cell where the formula =ShowPicD(E9) is placed. It does not actually memorize it, but interrogates each cell and if there is a picture checks which file it came from. If there is no change it leaves it there; otherwise it changes the picture. The file location is shown in the contents of E9 in my example. A change in the the reference page changes the associated picture in the workbook.

Yes the world watches us, every 4 years...otherwise we are just farmers.

btw, the =ShowPicD(E9) formula should probably be done as =ShowPicD($E$9) so it pastes absolute reference to the calling cell.