Having trouble establishing relative links between Excel spreadsheet and external file of images

Occasional Visitor
  • Mac mini (Late 2014) 16GB RAM, 1TB SSD
  • macOS 12.1 (Monterey)
  • Excel 16.56 (Office for Mac 365)

BACKGROUND:

I am a Mac user and have almost 30 years of relational database experience with FileMaker Pro, from version 2 to the current version 19. I’ve occasionally exported FileMaker data into Excel — which can be done quite easily — but have very little practical experience actually setting up Excel spreadsheets from scratch. 

 

CURRENT CHALLENGE:

  • I have a client for whom I’ve installed a small museum collection numbering (so far) 450 separate items, located in five different rooms.  
  • My client wants me to create an Excel spreadsheet for insurance purposes that details — for each item — its Name, Value, Location, ID Number, and a link to a single JPEG photo located in one external folder that will appear when a cell is clicked. 
  • Both the Excel file/worksheet and the folder of images are located in a single folder. 
  • For each item I want to as easily and quickly as possible create a hyperlink in a cell — hopefully without having to manually establish each link — that will simply reference the image name in the spreadsheet and open up the image from the external file when clicked. 
  • I’ve already tried inserting a photo directly into each cell, but the amount of work required to resize and position each image is more time than I have available, and since the photos vary in size quite a bit they waste too much space when displayed. I also prefer an external folder containing all of the images, rather than somehow embedding them in another worksheet. 
  • So far I have been able to get the behavior I want with the hyperlinked cell. I’m developing this on my Mac but will be delivering the final result on a flash drive or email attachment that will be transferred to a Windows computer. So I need all of the hyperlinks to use a relative not absolute path so the hyperlinks are never broken. The only path I want to stay absolute is the name of the enclosing folder that contains the Excel spreadsheet and the folder inside of it with all of the images. This single folder needs to always works with no adjustment all when installed on any other Mac or Windows computer.  
  • I tested my solution by copying the master folder to a flash drive, but of course when I opened the spreadsheet from that drive all of the hyperlinks were broken.
  • I’m not sure where to go now or what to do next in figuring out how to solve this problem. If anyone can point me to references or solutions, I would be most grateful!

Thanks! 

1 Reply

@balanwilliamson 

 

  • It's that second bullet in your long (and well-written) description that I want to question: WHY Excel? I can understand why Excel for insurance, in that it easily produces a total of the values, etc., but why does the client specifically specify that it has to be Excel to do all the rest of it, images in particular?
  • What the client needs is a system that produces a given result. If a different system produced that result with greater facility, greater simplicity of user-interface, wouldn't that be a better solution?
  • Another question: how dynamic is the underlying data, the movement of pieces into/out of the museum, or between its various rooms? 

Others here may have experience making Excel jump through the hoops you're describing, and I certainly would defer to them if that's the case, but you have had a lot of views and no other responses.

 

It sounds to me as if Excel is less than the ideal software for handling all those images. If in fact the data are relatively static, I even wonder if a plain ol' Word document mightn't suffice. More realistically, assuming that what's needed is the ability to dynamically retrieve and/or update the specifics (including images) of any given item, why isn't FileMaker the solution?

 

You may have thought through all of these questions, I realize, and I certainly am not wanting to just be difficult....it's possible that others will have a solution for you. I love Excel myself, but it's not the right solution to every situation.