If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Microsoft Excel > Linking Photo to excel

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Jan 2008
Location: Philadelphia
Posts: 8
Linking Photo to excel

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.
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Feb 2004
Posts: 533
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.
__________________
~

Bill
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Jan 2008
Location: Philadelphia
Posts: 8
Thanks Much

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

Be Blessed
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Jan 2008
Location: Philadelphia
Posts: 8
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
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Feb 2004
Posts: 533
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
__________________
~

Bill
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Jan 2008
Location: Philadelphia
Posts: 8
Thanks alot

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)
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
Join Date: Feb 2004
Posts: 533
Could your folder path have changed? What is the error code and error message?
__________________
~

Bill
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Jan 2008
Location: Philadelphia
Posts: 8
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
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
Join Date: Jan 2008
Posts: 1
I tried savbill's script too, worked fine.

Thanks!
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On