Excel tip: Insert filename in a cell
Date: 6 february 2002The following formulas will include the filename, filepath, of filepath and name in a cell in your workbook.
If you've installed ASAP Utilities, you already have these formulas. Just type one of the formula names, i.e. "=ASAPFileName()" in a cell.
Copy-paste friendly code:
Public Function ASAPFullFileName() As String
' returns the path and file name
' for example D:\Projects\testfile.xls
ASAPFullFileName = ActiveWorkbook.FullName
End Function
Public Function ASAPFileName() As String
' returns the file name without its path
' for example testfile.xls
ASAPFileName = ActiveWorkbook.Name
End Function
Public Function ASAPFilePath() As String
' returns the path without the file name
' for example D:\Projects
ASAPFilePath = ActiveWorkbook.Path
End Function
' returns the path and file name
' for example D:\Projects\testfile.xls
ASAPFullFileName = ActiveWorkbook.FullName
End Function
Public Function ASAPFileName() As String
' returns the file name without its path
' for example testfile.xls
ASAPFileName = ActiveWorkbook.Name
End Function
Public Function ASAPFilePath() As String
' returns the path without the file name
' for example D:\Projects
ASAPFilePath = ActiveWorkbook.Path
End Function
If you have the international version of Excel you can also use the following worksheet function:
=SUBSTITUTE(LEFT(CELL("filename",A1),FIND("]",CELL("filename",A1),1)-1),"[","")
Chip Pearson has written some nice examples on his website about how to return a file name.
« back