I am trying to automate the linking of data in 20 spreadsheets into a
separate spreadsheet.

They are all saved in a single folder which changes each month so I can not
"hard wire" the path name in.

This is not a problem if the user opens the summary spreadsheet using
File/Open as this tells Excel the current file path (the same as where the
data is kept).

However, it is done over a very large network and most users find it easier
to open the summary spreadsheet from an Explorer Window oe desk top shortcut.
The default file path may have changed (normally My Documents first thing in
the morning) and the data will not link.

Is there a way of getting the file path from the Summary spreadsheet when it
is opened and reseting the default path to that, so that the data will link
when the user manually presses a "Link Data" button.(I can then turn off the
startup prompt for linking data which is confusing some users).

Many thanks.