Current Workbook Path

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

application.path

HTH

"Terry" wrote:

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

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

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

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

activeworkbook.Path & "\" & activeworkbook.Name

can be replaced with

Activeworkbook.FullName

--
Regards,
Tom Ogilvy

"Terry" <[email protected]> wrote in message
news:[email protected]...
> 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...
> >.
> >

Tom Ogilvy suggested
>
> Activeworkbook.FullName
>
I've also seen suggestion to use the object

"thisworkbook."

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

WindsurferLA

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.

"Terry" wrote:

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

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

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

--
Regards,
Tom Ogilvy

"windsurferLA" <[email protected]> wrote in message
news:[email protected]...
> Tom Ogilvy suggested
> >
> > Activeworkbook.FullName
> >
> I've also seen suggestion to use the object
>
> "thisworkbook."
>
> what is the difference between "thisworkbook." and "activeworkbook." ???
>
> WindsurferLA

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