Getting/using the File path of a spreadsheet

[B Baggins]

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.

 

[xxx]

Hi,

The PATH attribute for thisworkbook gives you the info you want
Then u may either change directory to that path, or maybe better, explicitly
append the path to the workbooks you want to summarize

Here after an example on how to change the current path to the workbook path

Private Sub Workbook_Open()
MsgBox "Before : Current path is " & CurDir()
Set fs = CreateObject("Scripting.FileSystemObject")
' getting the workbook path (Note : it is the workbook path and not the
link Path)
wbpath = ThisWorkbook.Path
' gets the drive out of the path
drive = fs.getdrivename(wbpath)
' need to both change drive and change path
ChDrive drive
ChDir wbpath
MsgBox "After : Current path is " & CurDir(drive)
End Sub

Truly yours,

René