Linking Photo to excel

tcpearsall · #1 (**permalink**) 01-09-08, 10:57

I need help linking pictures to excel. I am making a spreadsheet to track a list of about 2500 items, and I would like to click on a link in excel and have the photo of the item come up with the specifications of that item which is found in the excel spreadsheet. is this possible?

I seen a simulare question http://www.dbforums.com/archive/inde...t-1613036.html but dont think he got an answer.

savbill · #2 (**permalink**) 01-09-08, 21:59

There is a couple approaches you could take to do this.

1. Without programming you could make a hyperlink on the sheet to each photo. Clicking the link will open the photo in your default program for the file type.
Downside: Slow, if you path changes you need to update all links

2. Use a shell function to open the picture file in your desired program.
Downside: script required, more work to compile data and display with image.

3. Use script with Pictures.insert method to insert your picture on an excel spreadsheet for viewing. Script can format specifications to display with the picture in a cell or text box.
Downside: time to develop script. either size picture for viewing or define with script.

4. generate an HTML file from Excel using a script to write the file. Then use a shell command to open the file in your browser.
Downside: need knowledge of HTML and how to write text files using a script.

If I were to do this I think I would do it with #3 or 4 method. The HTML file can give you the best control over the display of the picture and data in the same document. You would determine your HTML text and then insert the picture name and data using variables when generating it with a VB Script. Having all the same size and file type images will simplify this process.

tcpearsall · #3 (**permalink**) 01-10-08, 10:00

I think number 3 or 4 are more within my skill set
I appreciate you help

Be Blessed

tcpearsall · #4 (**permalink**) 01-10-08, 16:04

I tried the last two options and it did not produce the results I wanted. I got a macros that would generate the html but it seemed to be a bit unfaithfull in the reproduction ( some time right some times not)

And it seemd to effect the whole excel file and the person I'm doing this for will not be able to keep it updated.

Is there a way I can take data from one cell, use it as a variable for the name of the photo (minus the .jpg) and create a link to that photo in excel?
My goal is that as the data in the first cell changes the corresponding photo would change also.
Is there a formula that work with external links? in excel

savbill · #5 (**permalink**) 01-10-08, 22:05

This is a very simple example. It considers a list with the filenames in column 3 and I used column 4 for the file type. It opens a new workbook and inserts the picture.
--------------
Index Num | Description | File Name | File Type
1 | This is a Funny Picture | 221410152RL302447038 | JPG
--------------

For this example I set a path to my image folder in the script. With your application you may want to provide a way for the user to set this. For example use the 'getfolder' dialog to allow the user to select a folder. Then save the folder path in the Custom Properties of the file where you can read it in your script and assign it to the folderpath variable. You could set it up to run the process from a button on a toolbar or if the user double clicks in the row. I like to make a custom toolbar and attach it to my code file.

Code:

Sub DisplayImg()
   ' Define Path to images
   strFolder = "C:\Documents and Settings\MyPC\My Documents\My Pictures\"
   
   ' Get data from record of selected Row
   iRow = Selection.Row
   strName = Cells(iRow, 3)
   strType = Cells(iRow, 4)
   strFile = strName & "." & strType
   
   ' Lets do some validation
   If Trim(Len(strName)) = 0 Then
      msg = "There is not an image file listed on the selected Row" & vbCrLf & _
            "Please select a cell in the row with the image you want to view."
      MsgBox msg, vbExclamation + vbOKOnly, "My Photo DB"
      GoTo DisplayImg_Exit
   End If
   
   ' Open a new Workbook to disply the Image in
   Workbooks.Add
   
   ' Insert the picture on in the Active workbook
   ActiveSheet.Pictures.Insert (strFolder & strFile)
   
   ' Set saved state to true to prevent save file warning when closed
   ActiveWorkbook.Saved = True
   
DisplayImg_Exit:

End Sub

tcpearsall · #6 (**permalink**) 01-24-08, 16:42

This worked fine but for some reason
after about a wee I got and error at this line

I'm not sure how why this is happen all of a sudden

ActiveSheet.Pictures.Insert (strFolder & strFile)

savbill · #7 (**permalink**) 01-24-08, 23:51

Could your folder path have changed? What is the error code and error message?

tcpearsall · #8 (**permalink**) 01-25-08, 00:35

Thank you for responding
I found the problem
I changed the strType to jpg but forgot to put "" around it

You were a big help I hope that I can return the favor
Thank you
God Bless

JackGopher · #9 (**permalink**) 01-29-08, 08:15

I tried savbill's script too, worked fine.

Thanks!

Internet Services

Web Development

Digital Marketing

Consumer Tech