Source
r/excel•
heado

link_location flexibility?

solved

I'm helping my dad scan his business cards using WorldCard Pro. I'm able to export the read data (name, phone #, etc) from the program into an excel spreadsheet (using Office 2007). I am also able to export the individual jpg's based on their names. However, he wants to be able to look at the image of the card itself through excel. I followed u/excelevator 's method to make this happen. However I noticed that the link_location that populates is based on what I input.

My question: is there a way to accomplish the image linking but make it so that the link_location function populates based on the file address of said image and not the address on my personal computer? e.g. if he opens up the spreadsheet on another computer the function still works because it won't have c:\heado's pc\image folder in the function

Posted Apr 28, 2015, 5:04 AM UTC · edited

26
shopar28•

You can do it with the help of =CELL() formula. Provided that the name of your workbook and worksheet is predetermined; to A1 write:

 =SUBSTITUTE(CELL("fIlename",A1),"[Book1.xlsx]Sheet1","")

In the case above workbook name is Book1.xlsx, sheet name is Sheet1, this will give you the folder your file is in. Provided that you put the images in the same folder, or a subfolder, you can easily get to them.

2
excelevator174•

Ahah.. good old excelevator !!

The answer is easy, use a Relative address in Step 7.

For example, if the file you want to open resides in the sames folder as the source XL file, no additional path is needed, just the filename. The underlyling OS is clever enough to know to look in the same directory first.

The only issue accomplishing what you desire is having a directory structure on each computer that can be used relatively in the address you put in the spreadsheet.. if thats makes sense.

Back to the old days of the command prompt, the path .. means goto the parent directory.

For example c:\heado's pc\image folder\.. takes you back to heado's pc folder

You can use this to your advantage when building relative paths.. but I am gabbling now..

The =CELL("filename") formula in any cell, returns the filename and path of the current file. Using this in a formula with the .. it should be possible to reference the files relatively..

But without knowing the two paths addressess I cannot really help more.

2
headoOP•

Thanks for the advice on the relative address. I used that and hopefully it should work out. Assuming they want to keep the folder with the images separate from the file, I'd have to know the name of the address and point specifically to it?

1
excelevator174•

Yep, but you can also get smart about it.

You can have a Lookup table for paths, with a drop down selector at the top of the page, which fills the file path dynamically for each location you want to use the spreadsheet at. :)

Or even just reference a cell that contains the path for that instance.

1
headoOP•

Solution Verified

1
Clippy_Office_AsstMOD•

You have awarded one point to excelevator

1