Active or current file path

[Ted M H]

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?

 

[JLGWhiz]

CurrentPath = ThisWorkbook.Path

 

[Don Guillett]

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

 

[Dave Peterson]

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]

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...

 

[JLGWhiz]

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.

 

[JLGWhiz]

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.

 

[JLGWhiz]

I think this is what you are looking for:


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

 

[Ted M H]

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]

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