this is a simple alternative that gives all responses, Fullname, Path, filename.
Dim FilePath, FileOnly, PathOnly As String
FilePath = ThisWorkbook.FullName
FileOnly = ThisWorkbook.Name
PathOnly = Left(FilePath, Len(FilePath) - Len(FileOnly))
There is a universal way to get this:
Function FileName() As String
FileName = Mid(Application.Caption, 1, InStrRev(Application.Caption, "-") - 2)
End Function
ActiveWorkbook.FullName would be better I think, in case you have the VBA Macro stored in another Excel Workbook, but you want to get the details of the Excel you are editing, not where the Macro resides.
If they reside in the same file, then it does not matter, but if they are in different files, and you want the file where the Data is rather than where the Macro is, then ActiveWorkbook is the one to go for, because it deals with both scenarios.
strScriptFullname = WScript.ScriptFullName
strScriptPath = Left(strScriptFullname, InStrRev(strScriptFullname,"\"))
If you need path only this is the most straightforward way:
PathOnly = ThisWorkbook.Path
Source: Stackoverflow.com