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" wrote in message
...
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

"Ted M H" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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?