Results 1 to 9 of 9
  1. #1
    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.

  2. #2
    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

  3. #3
    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

  4. #4
    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

  5. #5
    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

  6. #6
    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)

  7. #7
    Join Date
    Feb 2004
    Posts
    533
    Could your folder path have changed? What is the error code and error message?
    ~

    Bill

  8. #8
    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

  9. #9
    Join Date
    Jan 2008
    Posts
    1
    I tried savbill's script too, worked fine.

    Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •