Current Workbook Path

[Terry]

Instead of using CurDir is there a command to use to get
the path of the current Workbook. CurDir doesn't always
work if the person has minimized xcel and traveled around
to other places and then openned the excel file again.

I know Workbook has a path property but can not figure out
how to use it.

Thanks...

 

[Guest]

application.path

HTH

 

[Terry]

Found it and I've been doing it with CurDir and suffering
for a long time. Just took putting it out here for others
for it to click in.
this is a great newsgroup..
Just for your info we use this as a toolbar macro to put
the file name in A1. I wonder if it could be a function
of the save command? But there might be a case where
there is something in A1. thinking out load. sorry.


Dim MyPath As String
MyPath = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name

ActiveCell.Value = MyPath

 

[Tom Ogilvy]

activeworkbook.Path & "\" & activeworkbook.Name

can be replaced with

Activeworkbook.FullName

 

[Guest]

Look at the Cells formula in the Excel Help and it will show you haow to
insert the File name and path without using a Macro.

HTH

 

[windsurferLA]

Tom Ogilvy suggested

Activeworkbook.FullName

I've also seen suggestion to use the object

"thisworkbook."

what is the difference between "thisworkbook." and "activeworkbook." ???

WindsurferLA

 

[Guest]

if you want the function to work for a single/specific workbook use
ThisWorkbook.Path. If you want to find the path of the activeworkbook use
ActiveWorkbook.Path.

 

[Tom Ogilvy]

Thisworkbook refers to the workbook where the executing code is located.

Activeworkbook is the workbook that has the focus in the Excel application.

 

[Guest]

There is a drawback to using the ActiveWorkbook.FullName or anything else
that relies on the ActiveWorkbook object. If you have more than one
spreadsheet open and force a recalculation for all open workbooks, then any
function that uses ActiveWorkbook will point at the active workbook, not the
workbook that the function resides in. So if Book1 has the
ActiveWorkbook.FullName, and Book2 has focus when the forced recalc is
invoked, Book1 will get the ActiveWorkbook.FullName for Book2.
The other alternatives are to use the CELL function, but there are some
messages on this board that indicates the CELL function can cause Excel to
crash. Not a good thing.
Using the ThisWorkbook is another alternative, but then the user will have
to enter the same code in every workbook that needs the path information.
Again, not a good thing.
The last option is to create a User function that is passed a Range object
that will retrieve the Workbook Path from the passed cell. Ex.

Public Function MyWorkbookPath(PassedCell as Range)
MyWorkbookPath = PassedCell.WorkSheet.Parent.FullName
End Function

This can be placed in an addin, which will allow you to use it wherever you
feel the need, plus, it will not fail since it does not use the
ActiveWorkbook object.

Good luck
Mark