I'm trying to find the property that returns the current / active file path
in Excel. This is not the Application DefualtFilePath property and it's not
the fullname property of any one workbook.
It's the file path that is set temporarily if you open or save as a workbook
in a folder other than the application default file path. After you open a
file in such a folder, it becomes the new current / active path when you do
another file Open or save as command. I thought at first that the
Application.DefaultFilePath would change in this situation, but it does not.
I can't find anything called the current or active file path in the Object
model refernece, but that property has to stored somewhere.
What I'm trying to do is to store this property in a string variable, then
change the directory a few times and then use the string variable to reset
the file path to its value before the macro ran.

Any ideas?

CurrentPath = ThisWorkbook.Path


"Ted M H" <(E-Mail Removed)> wrote in message
news:7F2BF7BB-B420-48C1-B8E9-(E-Mail Removed)...
> I'm trying to find the property that returns the current / active file
> path
> in Excel. This is not the Application DefualtFilePath property and it's
> not
> the fullname property of any one workbook.
> It's the file path that is set temporarily if you open or save as a
> workbook
> in a folder other than the application default file path. After you open
> a
> file in such a folder, it becomes the new current / active path when you
> do
> another file Open or save as command. I thought at first that the
> Application.DefaultFilePath would change in this situation, but it does
> not.
> I can't find anything called the current or active file path in the Object
> model refernece, but that property has to stored somewhere.
> What I'm trying to do is to store this property in a string variable, then
> change the directory a few times and then use the string variable to reset
> the file path to its value before the macro ran.
>
> Any ideas?

Will this do?
Sub whatpath()
MsgBox ActiveWorkbook.Path
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(E-Mail Removed)
"Ted M H" <(E-Mail Removed)> wrote in message
news:7F2BF7BB-B420-48C1-B8E9-(E-Mail Removed)...
> I'm trying to find the property that returns the current / active file
> path
> in Excel. This is not the Application DefualtFilePath property and it's
> not
> the fullname property of any one workbook.
> It's the file path that is set temporarily if you open or save as a
> workbook
> in a folder other than the application default file path. After you open
> a
> file in such a folder, it becomes the new current / active path when you
> do
> another file Open or save as command. I thought at first that the
> Application.DefaultFilePath would change in this situation, but it does
> not.
> I can't find anything called the current or active file path in the Object
> model refernece, but that property has to stored somewhere.
> What I'm trying to do is to store this property in a string variable, then
> change the directory a few times and then use the string variable to reset
> the file path to its value before the macro ran.
>
> Any ideas?

Maybe you're writing about the activeworkbook.path.

But it won't become the current path if you save a different workbook.



Ted M H wrote:
>
> I'm trying to find the property that returns the current / active file path
> in Excel. This is not the Application DefualtFilePath property and it's not
> the fullname property of any one workbook.
> It's the file path that is set temporarily if you open or save as a workbook
> in a folder other than the application default file path. After you open a
> file in such a folder, it becomes the new current / active path when you do
> another file Open or save as command. I thought at first that the
> Application.DefaultFilePath would change in this situation, but it does not.
> I can't find anything called the current or active file path in the Object
> model refernece, but that property has to stored somewhere.
> What I'm trying to do is to store this property in a string variable, then
> change the directory a few times and then use the string variable to reset
> the file path to its value before the macro ran.
>
> Any ideas?

--

Dave Peterson

Hi guys and thanks for the responses.

ThisWorkbook.path and ActiveWorkbook.path are not what I'm looking for. The
following should explain more clearly what I need.

Say the default path is C:\documents and settings\user\my documents

Using the Immediate window…

?application.DefaultFilePath returns C:\documents and settings\user\my
documents
I create a new blank workbook and save it as test.xls to the default path

?thisworkbook.path returns C:\documents and settings\user\my documents
?activeworkbook.path returns C:\documents and settings\user\my documents
?activeworkbook.fullname returns C:\documents and settings\user\my
documents\test.xls

I type ChDir “C:\ documents and settings\user\Desktop\TestFolder
Now in Excel when I do File > Open, the default folder is
…Desktop\TestFolder (this is the path I need)
But when I redo the path statements, they all still return C:\documents and
settings\user\my documents--these are the wrong objects/properties.

Excel obviously is storing the path I changed to with ChDir, but where?

Many thanks if you can solve this one...

"Ted M H" wrote:

> I'm trying to find the property that returns the current / active file path
> in Excel. This is not the Application DefualtFilePath property and it's not
> the fullname property of any one workbook.
> It's the file path that is set temporarily if you open or save as a workbook
> in a folder other than the application default file path. After you open a
> file in such a folder, it becomes the new current / active path when you do
> another file Open or save as command. I thought at first that the
> Application.DefaultFilePath would change in this situation, but it does not.
> I can't find anything called the current or active file path in the Object
> model refernece, but that property has to stored somewhere.
> What I'm trying to do is to store this property in a string variable, then
> change the directory a few times and then use the string variable to reset
> the file path to its value before the macro ran.
>
> Any ideas?

You would need to assign the ChDir path to a variable

MyPath = ChDir "C:\ documents and settings\user\Desktop\TestFolder

Now, MyPath will return the new path.


"Ted M H" <(E-Mail Removed)> wrote in message
news:0772EADA-886F-40E5-9024-(E-Mail Removed)...
> Hi guys and thanks for the responses.
>
> ThisWorkbook.path and ActiveWorkbook.path are not what I'm looking for.
> The
> following should explain more clearly what I need.
>
> Say the default path is C:\documents and settings\user\my documents
>
> Using the Immediate window.
>
> ?application.DefaultFilePath returns C:\documents and settings\user\my
> documents
> I create a new blank workbook and save it as test.xls to the default path
>
> ?thisworkbook.path returns C:\documents and settings\user\my documents
> ?activeworkbook.path returns C:\documents and settings\user\my documents
> ?activeworkbook.fullname returns C:\documents and settings\user\my
> documents\test.xls
>
> I type ChDir "C:\ documents and settings\user\Desktop\TestFolder
> Now in Excel when I do File > Open, the default folder is
> .Desktop\TestFolder (this is the path I need)
> But when I redo the path statements, they all still return C:\documents
> and
> settings\user\my documents--these are the wrong objects/properties.
>
> Excel obviously is storing the path I changed to with ChDir, but where?
>
> Many thanks if you can solve this one...
>
> "Ted M H" wrote:
>
>> I'm trying to find the property that returns the current / active file
>> path
>> in Excel. This is not the Application DefualtFilePath property and it's
>> not
>> the fullname property of any one workbook.
>> It's the file path that is set temporarily if you open or save as a
>> workbook
>> in a folder other than the application default file path. After you open
>> a
>> file in such a folder, it becomes the new current / active path when you
>> do
>> another file Open or save as command. I thought at first that the
>> Application.DefaultFilePath would change in this situation, but it does
>> not.
>> I can't find anything called the current or active file path in the
>> Object
>> model refernece, but that property has to stored somewhere.
>> What I'm trying to do is to store this property in a string variable,
>> then
>> change the directory a few times and then use the string variable to
>> reset
>> the file path to its value before the macro ran.
>>
>> Any ideas?

Let's try this again:

'Fist assign your variable to your new path
MyPath ="C:\ documents and settings\user\Desktop\TestFolder"
'Then change directory
ChDir MyPath

Now MyPath will return your new Directory path. It has to be done backward.

"Ted M H" <(E-Mail Removed)> wrote in message
news:0772EADA-886F-40E5-9024-(E-Mail Removed)...
> Hi guys and thanks for the responses.
>
> ThisWorkbook.path and ActiveWorkbook.path are not what I'm looking for.
> The
> following should explain more clearly what I need.
>
> Say the default path is C:\documents and settings\user\my documents
>
> Using the Immediate window.
>
> ?application.DefaultFilePath returns C:\documents and settings\user\my
> documents
> I create a new blank workbook and save it as test.xls to the default path
>
> ?thisworkbook.path returns C:\documents and settings\user\my documents
> ?activeworkbook.path returns C:\documents and settings\user\my documents
> ?activeworkbook.fullname returns C:\documents and settings\user\my
> documents\test.xls
>
> I type ChDir "C:\ documents and settings\user\Desktop\TestFolder
> Now in Excel when I do File > Open, the default folder is
> .Desktop\TestFolder (this is the path I need)
> But when I redo the path statements, they all still return C:\documents
> and
> settings\user\my documents--these are the wrong objects/properties.
>
> Excel obviously is storing the path I changed to with ChDir, but where?
>
> Many thanks if you can solve this one...
>
> "Ted M H" wrote:
>
>> I'm trying to find the property that returns the current / active file
>> path
>> in Excel. This is not the Application DefualtFilePath property and it's
>> not
>> the fullname property of any one workbook.
>> It's the file path that is set temporarily if you open or save as a
>> workbook
>> in a folder other than the application default file path. After you open
>> a
>> file in such a folder, it becomes the new current / active path when you
>> do
>> another file Open or save as command. I thought at first that the
>> Application.DefaultFilePath would change in this situation, but it does
>> not.
>> I can't find anything called the current or active file path in the
>> Object
>> model refernece, but that property has to stored somewhere.
>> What I'm trying to do is to store this property in a string variable,
>> then
>> change the directory a few times and then use the string variable to
>> reset
>> the file path to its value before the macro ran.
>>
>> Any ideas?

I think this is what you are looking for:


Sub dk()
MsgBox CurDir("C")
End Sub


"Ted M H" <(E-Mail Removed)> wrote in message
news:0772EADA-886F-40E5-9024-(E-Mail Removed)...
> Hi guys and thanks for the responses.
>
> ThisWorkbook.path and ActiveWorkbook.path are not what I'm looking for.
> The
> following should explain more clearly what I need.
>
> Say the default path is C:\documents and settings\user\my documents
>
> Using the Immediate window.
>
> ?application.DefaultFilePath returns C:\documents and settings\user\my
> documents
> I create a new blank workbook and save it as test.xls to the default path
>
> ?thisworkbook.path returns C:\documents and settings\user\my documents
> ?activeworkbook.path returns C:\documents and settings\user\my documents
> ?activeworkbook.fullname returns C:\documents and settings\user\my
> documents\test.xls
>
> I type ChDir "C:\ documents and settings\user\Desktop\TestFolder
> Now in Excel when I do File > Open, the default folder is
> .Desktop\TestFolder (this is the path I need)
> But when I redo the path statements, they all still return C:\documents
> and
> settings\user\my documents--these are the wrong objects/properties.
>
> Excel obviously is storing the path I changed to with ChDir, but where?
>
> Many thanks if you can solve this one...
>
> "Ted M H" wrote:
>
>> I'm trying to find the property that returns the current / active file
>> path
>> in Excel. This is not the Application DefualtFilePath property and it's
>> not
>> the fullname property of any one workbook.
>> It's the file path that is set temporarily if you open or save as a
>> workbook
>> in a folder other than the application default file path. After you open
>> a
>> file in such a folder, it becomes the new current / active path when you
>> do
>> another file Open or save as command. I thought at first that the
>> Application.DefaultFilePath would change in this situation, but it does
>> not.
>> I can't find anything called the current or active file path in the
>> Object
>> model refernece, but that property has to stored somewhere.
>> What I'm trying to do is to store this property in a string variable,
>> then
>> change the directory a few times and then use the string variable to
>> reset
>> the file path to its value before the macro ran.
>>
>> Any ideas?

Bingo!

Thanks a million... it was the CurDir function I was looking for. That
gives me exactly what I need--Excel's current path, independent of the active
workbook, this workbook and the application default paths. I knew it had to
be out there somewhere.

Looks like you spent some cycles to help me with this--I'm much obliged.

"JLGWhiz" wrote:

> I think this is what you are looking for:
>
>
> Sub dk()
> MsgBox CurDir("C")
> End Sub
>
>
> "Ted M H" <(E-Mail Removed)> wrote in message
> news:0772EADA-886F-40E5-9024-(E-Mail Removed)...
> > Hi guys and thanks for the responses.
> >
> > ThisWorkbook.path and ActiveWorkbook.path are not what I'm looking for.
> > The
> > following should explain more clearly what I need.
> >
> > Say the default path is C:\documents and settings\user\my documents
> >
> > Using the Immediate window.
> >
> > ?application.DefaultFilePath returns C:\documents and settings\user\my
> > documents
> > I create a new blank workbook and save it as test.xls to the default path
> >
> > ?thisworkbook.path returns C:\documents and settings\user\my documents
> > ?activeworkbook.path returns C:\documents and settings\user\my documents
> > ?activeworkbook.fullname returns C:\documents and settings\user\my
> > documents\test.xls
> >
> > I type ChDir "C:\ documents and settings\user\Desktop\TestFolder
> > Now in Excel when I do File > Open, the default folder is
> > .Desktop\TestFolder (this is the path I need)
> > But when I redo the path statements, they all still return C:\documents
> > and
> > settings\user\my documents--these are the wrong objects/properties.
> >
> > Excel obviously is storing the path I changed to with ChDir, but where?
> >
> > Many thanks if you can solve this one...
> >
> > "Ted M H" wrote:
> >
> >> I'm trying to find the property that returns the current / active file
> >> path
> >> in Excel. This is not the Application DefualtFilePath property and it's
> >> not
> >> the fullname property of any one workbook.
> >> It's the file path that is set temporarily if you open or save as a
> >> workbook
> >> in a folder other than the application default file path. After you open
> >> a
> >> file in such a folder, it becomes the new current / active path when you
> >> do
> >> another file Open or save as command. I thought at first that the
> >> Application.DefaultFilePath would change in this situation, but it does
> >> not.
> >> I can't find anything called the current or active file path in the
> >> Object
> >> model refernece, but that property has to stored somewhere.
> >> What I'm trying to do is to store this property in a string variable,
> >> then
> >> change the directory a few times and then use the string variable to
> >> reset
> >> the file path to its value before the macro ran.
> >>
> >> Any ideas?
>
>
>

Just took me a while to grasp exactly what it was you were looking for.
Glad to help.


"Ted M H" <(E-Mail Removed)> wrote in message
news:6B15C57B-8D58-4DB1-B737-(E-Mail Removed)...
> Bingo!
>
> Thanks a million... it was the CurDir function I was looking for. That
> gives me exactly what I need--Excel's current path, independent of the
> active
> workbook, this workbook and the application default paths. I knew it had
> to
> be out there somewhere.
>
> Looks like you spent some cycles to help me with this--I'm much obliged.
>
> "JLGWhiz" wrote:
>
>> I think this is what you are looking for:
>>
>>
>> Sub dk()
>> MsgBox CurDir("C")
>> End Sub
>>
>>
>> "Ted M H" <(E-Mail Removed)> wrote in message
>> news:0772EADA-886F-40E5-9024-(E-Mail Removed)...
>> > Hi guys and thanks for the responses.
>> >
>> > ThisWorkbook.path and ActiveWorkbook.path are not what I'm looking for.
>> > The
>> > following should explain more clearly what I need.
>> >
>> > Say the default path is C:\documents and settings\user\my documents
>> >
>> > Using the Immediate window.
>> >
>> > ?application.DefaultFilePath returns C:\documents and settings\user\my
>> > documents
>> > I create a new blank workbook and save it as test.xls to the default
>> > path
>> >
>> > ?thisworkbook.path returns C:\documents and settings\user\my documents
>> > ?activeworkbook.path returns C:\documents and settings\user\my
>> > documents
>> > ?activeworkbook.fullname returns C:\documents and settings\user\my
>> > documents\test.xls
>> >
>> > I type ChDir "C:\ documents and settings\user\Desktop\TestFolder
>> > Now in Excel when I do File > Open, the default folder is
>> > .Desktop\TestFolder (this is the path I need)
>> > But when I redo the path statements, they all still return C:\documents
>> > and
>> > settings\user\my documents--these are the wrong objects/properties.
>> >
>> > Excel obviously is storing the path I changed to with ChDir, but where?
>> >
>> > Many thanks if you can solve this one...
>> >
>> > "Ted M H" wrote:
>> >
>> >> I'm trying to find the property that returns the current / active file
>> >> path
>> >> in Excel. This is not the Application DefualtFilePath property and
>> >> it's
>> >> not
>> >> the fullname property of any one workbook.
>> >> It's the file path that is set temporarily if you open or save as a
>> >> workbook
>> >> in a folder other than the application default file path. After you
>> >> open
>> >> a
>> >> file in such a folder, it becomes the new current / active path when
>> >> you
>> >> do
>> >> another file Open or save as command. I thought at first that the
>> >> Application.DefaultFilePath would change in this situation, but it
>> >> does
>> >> not.
>> >> I can't find anything called the current or active file path in the
>> >> Object
>> >> model refernece, but that property has to stored somewhere.
>> >> What I'm trying to do is to store this property in a string variable,
>> >> then
>> >> change the directory a few times and then use the string variable to
>> >> reset
>> >> the file path to its value before the macro ran.
>> >>
>> >> Any ideas?
>>
>>
>>