Relative path to linked excel sheets

[Patrick Braunschweig]

Hi together,

I have an Access DB File which is several linked excel sheets as data
sources. Currently the paths to these external linked data sources is
absolute. How can I establish a relative path, so that the Access file and
the Excel source data sheets are portable to others computers (e.g. via
e-mail). I don't want my colleagues to always update several links to.

Thanks in advance and best regards,
Patrick

 

[pietlinden]

Hi together,

I have an Access DB File which is several linked excel sheets as data
sources. Currently the paths to these external linked data sources is
absolute. How can I establish a relative path, so that the Access file and
the Excel source data sheets are portable to others computers (e.g. via
e-mail). I don't want my colleagues to always update several links to.

Thanks in advance and best regards,
Patrick

Depends on where your linked data sources are. If they're on your
network, you can use the absolute path, e.g.
\\servername\directory\subdirectory\filename

If they're on the user's local machine, you can use
CurrentProject.Path
to get the current directory the files are in and then build a
relative path in code, e.g.
CurrentProject.Path & "\SomeSubDirectory\SomeFile.xls

Then to tie all this together you could use a table to hold these
values and then loop through it and do an Import/Link in code to do
all this...

Okay, how about something I know works... try this:
http://www.mvps.org/access/tables/tbl0009.htm

(Knew it was here... must not be awake yet!)