Get path of open workbook

How do I get the full path of the Excel workbook that is open and active ?
File System Object requires the full path before opening, creating and
manipulating files. What if the path is "unknown" ? I am trying to put the
path and workbook name and worksheetname into the footer of each sheet of the
active workbook. Thank you.

activeworkbook.FullName

However, in View=>Header Footer, choose custom, the button with the folder
icon will put in the full path and Name (in xl2003 and as I recall, it was
there in earlier versions as well - but I could be wrong).

Not sure how this relates to filesystemobject.

--
Regards,
Tom Ogilvy


--
Regards,
Tom Ogilvy

"Selina" <Selina@discussions.microsoft.com> wrote in message
news:82D73904-FDE4-40A8-B2B3-8B4B1AA6E30B@microsoft.com...
> How do I get the full path of the Excel workbook that is open and active ?
> File System Object requires the full path before opening, creating and
> manipulating files. What if the path is "unknown" ? I am trying to put the
> path and workbook name and worksheetname into the footer of each sheet of
the
> active workbook. Thank you.

Selina,

You can use the following:

Dim strFullName as String
strFullName = ActiveWorkbook.FullName
MsgBox strFullName

HTH

"Selina" wrote:

> How do I get the full path of the Excel workbook that is open and active ?
> File System Object requires the full path before opening, creating and
> manipulating files. What if the path is "unknown" ? I am trying to put the
> path and workbook name and worksheetname into the footer of each sheet of the
> active workbook. Thank you.

I prefer the cheat to win method. This example only works if the document you
are working on is the last file that was opened. This Sub is meant to save
the currently opened text file (CSV, TAB, etc.) into an Excel file by the
originating name of the file.

Public Sub Save_Workbook()
Dim sPath as String
'Get full file path and name from recent files list, #1 is the most
recently opened
sPath = Application.RecentFiles.Item(1).Name
'Find current directory (designated by "\" as right most character), path
is on left, file name is to the right.
For s = Len(sPath) To 1 Step -1
If Mid(sPath, s, 1) = "\" Then
sPath = Left(sPath, s)
Exit For
End If
Next s
'Save the workbook as last worksheet name
Activeworkbook.SaveAs sPath & Worksheets(Worksheets.Count).Name & ".xls",
xlWorkbookNormal
End Sub

That should do it.



"Selina" wrote:

> How do I get the full path of the Excel workbook that is open and active ?
> File System Object requires the full path before opening, creating and
> manipulating files. What if the path is "unknown" ? I am trying to put the
> path and workbook name and worksheetname into the footer of each sheet of the
> active workbook. Thank you.

Or:

Private Function MyName() As String
MyName = ThisWorkbook.Name
End Function

Private Function MyFullName() As String
'---------------------------------------------------------------------------------------
' Procedure : MyFullName
'return name and path of current workbook
MyFullName = ThisWorkbook.Fullname
End Function

Function fPath()
' Purpose : Compares two above to strips the workbook name and return the
filepath only
' returns the filepath for a file
fPath = Left(MyFullName, Len(MyFullName) - Len(MyName))
End Function





"Peridox" <Peridox@discussions.microsoft.com> wrote in message
news:4C487E38-2618-4DE1-8249-45AEA954037C@microsoft.com...
>I prefer the cheat to win method. This example only works if the document
>you
> are working on is the last file that was opened. This Sub is meant to save
> the currently opened text file (CSV, TAB, etc.) into an Excel file by the
> originating name of the file.
>
> Public Sub Save_Workbook()
> Dim sPath as String
> 'Get full file path and name from recent files list, #1 is the most
> recently opened
> sPath = Application.RecentFiles.Item(1).Name
> 'Find current directory (designated by "\" as right most character),
> path
> is on left, file name is to the right.
> For s = Len(sPath) To 1 Step -1
> If Mid(sPath, s, 1) = "\" Then
> sPath = Left(sPath, s)
> Exit For
> End If
> Next s
> 'Save the workbook as last worksheet name
> Activeworkbook.SaveAs sPath & Worksheets(Worksheets.Count).Name &
> ".xls",
> xlWorkbookNormal
> End Sub
>
> That should do it.
>
>
>
> "Selina" wrote:
>
>> How do I get the full path of the Excel workbook that is open and active
>> ?
>> File System Object requires the full path before opening, creating and
>> manipulating files. What if the path is "unknown" ? I am trying to put
>> the
>> path and workbook name and worksheetname into the footer of each sheet of
>> the
>> active workbook. Thank you.