Create bulk relative hyperlinks

[Adam10541]

I've been having some trouble with this one and thought I'd seek some help here.

Basically I'm trying to find the easiest way to create thousands of relative hyperlinks to files from a spread sheet. What I would like to be able to do is save this spread sheet and the accompanying folder containing the files to CD or USB drives and have the links work regardless of where and how they are viewed.

Thus far I have been simply using the "=hyperlink(A1)" formula to give me working hyperlinks (the A1 cell containing the local path to the file) which is great when it's on my machine, but once I copy that off elsewhere it's not going to work.

I'm hoping for a relatively simple way to convert the existing links, or even create new ones based on the path information which I already have listed.

Any help much appreciated.

Edit: I should mention I'm using Excel 2010 and my knowledge of Excel when it comes to VBA and other magic tricks is fairly limited so please be gentle....or specific

[oeldere]

but once I copy that off elsewhere it's not going to work.

what is the result using:

=hyperlink($A$1)

[Adam10541]

Hi Oeldere, this didn't make any difference. Presumably because the path to the file listed in A1 is still the local path on my computer not the new location on the USB thumb drive.

[vlady]

What is in A1... what does it looks like.

[Adam10541]

Hi Vlady

A1 contains the full local path to the file, something like M:\JobName\Invoices\Archives\filename.pdf

[oeldere]

Why not change the path in cell A1 to the new path on your USB stick?

[Adam10541]

Changing the path is an option, but lets say my computer assigns D to the USB drive, but my client's computer assigns F, then same problem links are broken. This work may end up travelling onto various computers so my hope is to create links that will simply look for the file within a folder which will always accompany the excel file.

It's easy to do for links one at a time, however this spread sheet will contain over 3000 rows when it's complete so it's not practical to redo the links individually.

[oeldere]

Although I'm lost how your data looks like, this could be a start

d4=CELL("filename")

[vlady]

I think I'll go with post # 6 and #8
say $a$1 contains the path and sub folders then a2 is your file name
or
say $a$1 contains the path with the formula in post # 8 then a2 is your sub folders and file name

=hyperlink("File:////"&$a$1&a2,"Click")

this way only cell A! will be changed according to the drive letter of the computer...

[Adam10541]

I'm not sure I understand correctly, or more accurately I'm probably not explaining clearly. I'll break down the process I've undertaken thus far and hopefully will give a clear indication of my goal.

I'm using software to scan PDF files which are invoices, then producing spreadsheets which capture relevant information from those invoices (invoice number, date, total, tax etc), at the end of this process I will have a master spread sheet which lists all 3000 odd invoices with this information. This software also allows the archival of the original PDF document and it creates a corresponding entry in the spread sheet with the path to where this document is stored on the local computer. In this instance the path in the cell (K2 to be exact) is "M:/Client/SmartSoft/Archives/9134444.pdf".

I'm trying to create a hyperlink for these files that ignores the "M:/Client/Smartsoft" part of the path and simply looks for a folder "Archives" in the same root directory that the spread sheet resides. I can accomplish this using "right click, hyperlink" option for the cells containing the path (k2, k3 etc) one at a time then editing the hyperlink and removing the first part of the path. However doing that one at a time would take many days/weeks, in addition I can't edit hyperlinks in the same fashion that are created using a formula unlike those created with the 'right click' method which can be edited.

I confess I don't understand how to use oeldere's suggested "d4=CELL("filename"), when I use this formula (without the d4) it simply populates the cell with the path and name to the spread sheet.

Your suggestion vlady does indeed produce a working hyperlink which behaves the same as "=hyperlink(k2)" that I'm currently using, so I'm obviously not understanding the process here. However as I didn't have the CELL formula anywhere my use was =hyperlink("File:////"&$k$2,"click").

I'm sorry I'm obviously missing something here, my understanding of this stuff is fairly basic

[oeldere]

You get better result on your question if you add a small excel file, without confidential information.

Please also add the expected results manualy in your file.

[Adam10541]

See attached spread sheet as requested

Thanks for the time and suggestions so far, very much appreciated.

[oeldere]

What is the original file path.

In what cells do I find the expected result?

[Adam10541]

The original file path is listed in the column K
The basic hyperlink is in column L

The wanted result is in Cell L11

I have put some text in the spread sheet showing this.

[JohnTopley]

Some thing like ..

=HYPERLINK(K5 & RIGHT(J2,8) & ".pdf")

k5 = M:/Client/SmartSoft/Archive/

[Adam10541]

That will create a working hyperlink provided I do a few things, like
Change K5 to K2
Edit K2 and remove "91458532.pdf"
Edit J2 and remove "Client_"

However the link is still absolute and will not work once the spread sheet and data are transferred to a new location.

I'm really having trouble getting my head around the fact that excel can do exactly what I need on a one by one basis, but figuring out the bulk process is proving so difficult.

[JohnTopley]

Excel will have no way of knowing you have changed location (it is not that intelligent!!)

You have to organise the Hyperlink creation so the fewest fields need changing.

Perhaps put these on a "cover" sheet and used named ranges to reference them in your formula.

[Adam10541]

Excel will have no way of knowing you have changed location (it is not that intelligent!!)

Excel is clearly capable of doing this as it works perfectly when creating hyperlinks via 'right click' on the cell, create hyperlink then manually edit the resulting hyperlink.

On searching around various forums I can see plenty of people complaining that Excel uses relative hyperlinks by default, however everything I'm seeing shows Excel uses Absolute hyperlinks by default, which is neither here nor there.

I will keep searching for a solution and hope I don't have to do it one at a time.

[Adam10541]

Success!!

Solution turned out to be a lot simpler than I had though.

For those who are interested.

Originally I had a cell with the full path to the file "M:/Client/Smartsoft/Archives/12345.pdf"
In the cell next to it I used the formula "hyperlink(k2)" then filled that down the column to give me live link to the file.

In order to fix this so it would work regardless of location I had to change the format of the cell with the path to "Archives\12345.pdf"
Removing the beginning of the path and changing the direction of the slash had the desired effect.

Now the links work provided the Archives folder sits in the same root directory as the spread sheet.

Thanks for the help peoples, I have no doubt that if you didn't get my thinking juices flowing I'd be battling with this one for some time to come.

[oeldere]

You're welcome. We appreciate the feedback!

If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.

It'd also be appreciated if you were to click the add Reputation button at the foot of any of the posts of those who helped you reach a solution.