Get path of open workbook

[Guest]

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.

 

[Tom Ogilvy]

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.

 

[Guest]

Selina,

You can use the following:

Dim strFullName as String
strFullName = ActiveWorkbook.FullName
MsgBox strFullName

HTH

 

[Guest]

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.

 

[Steve]

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