Current Workbook Path
 

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

"Terry" wrote:

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

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