macro to retrieve path to opened file

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

One way:

Sub Auto_Open
MyPath = ActiveWorkbook.Path
ChDir MyPath
End Sub

Glen

"windsurferLA" <buczacz@yahoo.com> wrote in message
news:I7idnXeNrIkZXY7fRVn-pg@comcast.com...
> 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 ... 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

Glen Mettler wrote:
> One way:
>
> Sub Auto_Open
> MyPath = ActiveWorkbook.Path
> ChDir MyPath
> End Sub
>
> Glen
>
> "windsurferLA" <buczacz@yahoo.com> wrote in message
> news:I7idnXeNrIkZXY7fRVn-pg@comcast.com...
>
>>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
>
>
>
Thanks...

Next time, try Help <g>. Specifically, in the immediate window, you could have
typed 'path' (without the quotes) and pressed F1 to find several relevant
topics.


On Thu, 17 Feb 2005 07:49:35 -0800, windsurferLA <buczacz@yahoo.com> wrote:

>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
>
>Glen Mettler wrote:
>> One way:
>>
>> Sub Auto_Open
>> MyPath = ActiveWorkbook.Path
>> ChDir MyPath
>> End Sub
>>
>> Glen
>>
>> "windsurferLA" <buczacz@yahoo.com> wrote in message
>> news:I7idnXeNrIkZXY7fRVn-pg@comcast.com...
>>
>>>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
>>
>>
>>
>Thanks...