macro to retrieve path to opened file

[windsurferLA]

Help would be appreciated getting the syntax correct in an XL97 macro
under WinXP. I working on building code that is to be part of a larger
macro that will save the current version of a spreadsheet and ripple
down prior versions of that spreadsheet, yet not be dependent on the
spreadsheet having a specific name or location.

If one opens an Excel file by selecting (double clicking) its name in a
directory, how does one find the path to that file using a command
within a macro associated with that Excel file?

Ultimately I want to place a SaveAs command with provisions for a backup
in an Auto_Close() macro, but the SaveAs command saves the file to the
“current directory” which is not necessarily the directory containing
the original file.

Thanks .. WindsurferLA

 

[Glen Mettler]

One way:

Sub Auto_Open
MyPath = ActiveWorkbook.Path
ChDir MyPath
End Sub

Glen

 

[windsurferLA]

Glen ... once I saw your reply, I said to myself "obvious," but I have
not found it in any of my Excel VBA books.

Thanks .. WindsurferLA

One way:

Sub Auto_Open
MyPath = ActiveWorkbook.Path
ChDir MyPath
End Sub

Glen

Thanks...