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" wrote in message
...
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.