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?
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.
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
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.
' 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"
' Open a new Workbook to disply the Image in
' 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