ExcelBanter - Dynamic Hyperlinking ?

Second answer first. Yes - on a sheet choose Insert | Object and use the
"From File" tab to pick the file to embed. This is going to make for a HUGE
Excel file as you are aware.

You cannot hyperlink directly to the embedded object but you can fool people
by linking to a cell on the sheet the object is on that is either near or
even hidden behind the object on that sheet. Use the Hyperlink | Place in
this Document option and choose the sheet and type in an appropriate cell
reference if you want something other than the A1 that's usually offered.

Now to the more difficult issue of dynamic hyperlinks. First restriction:
all files you link to using this method must be in the same folder with the
Excel workbook on the CD/DVD in this example. Your problem is that the CD
drive's letter varies from system to system. The solution to this problem is
that we can find out where the Excel file is running from in code, and we can
take that information and use the worksheet HYPERLINK() function to create
your dynamic hyperlink.

Start by creating the function that's going to tell you where your Excel
file is being run from. There is just a single line of code - editor here
will probably break it up more than once:

Private Function GetCurrentPath()

GetCurrentPath = "file:///" & Left(ThisWorkbook.FullNameURLEncoded,
Len(ThisWorkbook.FullNameURLEncoded) - Len(ThisWorkbook.Name))

End Function

Now on the worksheets you can use this formula to create your dynamic
hyperlinks:
=HYPERLINK(getcurrentpath() & "somefile.txt","Open File somefile")
You'll have to change the filenames and text to be displayed as appropriate.

Also, rather than typing in "somefile.txt" and "Open File somefile" for
every entry, you can use cell references that already contain those entries.
That would let you set up a kind of Table of Contents sheet with the
filenames in one column and the text to be displayed in another, and just use
those references in the HYPERLINK() function. You could hide that sheet from
view - it would become a handy way to change the names of the files/text to
display and have that automatically update the links in the future without
having to track down and modify links throughout the workbook.

"Arjun Chowdhry" wrote:

Hi,
I need to burn some files ( Word,PPTs,PDFs, etc) onto a CD.
These are referenced using hyperlinks in excel.
Is there a way to maintain the hyperlink while burning the CD, so that
the hypelinks still work while on the the CD ??

Also, is there a way to embed Word/PDF/PPT files into an Excel Sheet
and then Hyperlink it. So it all is stored in One BIG excel file .

Thanks,
Arjun