Inserting and coverting hyperlinks

[jp2012]

Hoping someone out there can help...I am using the 2007 Office Professional Suite for the following processes.



I have a very lengthy excel workbook with the hyperlinks to hundreds of Adobe (.pdf) documents. I have the excel workbook in a file folder named "Details" within this folder have a "Documents" folder containing all Adobe documents.

I have tried both manually and auto entering the Hyperlinks in the following manners (only because I could not get any one of them to work properly in the end product):

1. Insert link (ctrl+k) and clicking on each file;
2. The "=HYPERLINK(XXXXXXX,XXXX)" formula (I've tried both a relative link formula and absolute file path); and,
3. Visual Basic Script that will link as many document numbers as I ask (my preferred method for speed and ease).

All link methods work in excel and if I write the formulas in a "relative hyperlink" style the excel ledger will work anywhere I put the "Details" folder. On the right road so I thought...

The Issue:

When I cut and paste the hyperlinked Excel tables to Word 2007 (as I need to incorporate the linked portions of the excel tables into a narrative Word text document then converted to PDF using the PDF Maker add-in) everything works great. The final Adobe document is saved within the "Details" folder. Links are carried over and bookmarks are created still on the right road... as long as the "Details" folder and contents are not moved anywhere.

I can not seem to force the programs (Word 2007 and the converted Adobe document) to keep the links "relative" (ie. update links to have the same directory path as the report then look for and pull from /Documents/123.pdf) as they maintain the original full directory path of where they were created including the originating drive letter.

Once everything is converted to Adobe I need to copy all contents of the "Details" folder to CD and maintain all links regardless of drive letter of the recipient's disc drive on their computer. I need the links to ignore the file path up until the "Details" folder so I can move and copy the file anywhere.

What have I set up wrong or what setting am I missing? I've tried the "update links" options in Word and Excel but still can not get things working.

I hope I've made sense and sorry for the length of the posting.