CODEWITHSUNDEEP
excelvba
Veera
Veera

Reputation: 33182

How to get the excel file name / path in VBA

Say, I'm writing a VBA inside my excel file sample.xls. Now I want to get the full path of sample.xls in my VBA. How do I do it?

Upvotes: 51

Views: 433148

Answers (7)

TheAccountant
TheAccountant

Reputation: 51

if you need path only without file name:

ActiveWorkbook.Path

it would return D:\Folder

if you need file path with file name also:

ActiveWorkbook.FullName

it would return D:\Folder\sample.xls

if you need file name only:

ActiveWorkbook.Name

it would return sample.xls

so if you want combine file path and file name to get full directory don't forget to add "" between. otherwise its simpler using .Path

Upvotes: 5

Fionnuala
Fionnuala

Reputation: 91356

If you mean VBA, then you can use FullName, for example:

strFileFullName = ThisWorkbook.FullName

(updated as considered by the comments: the former used ActiveWorkbook.FullName could more likely be wrong, if other office files may be open(ed) and active. But in case you stored the macro in another file, as mentioned by user @user7296559 here, and really want the file name of the macro-using file, ActiveWorkbook could be the correct choice, if it is guaranteed to be active at execution time.)

Upvotes: 62

Louis
Louis

Reputation: 49

If you need path only this is the most straightforward way: 

PathOnly = ThisWorkbook.Path

Upvotes: 5

Riccardo La Marca
Riccardo La Marca

Reputation: 895

There is a universal way to get this:

Function FileName() As String
    FileName = Mid(Application.Caption, 1, InStrRev(Application.Caption, "-") - 2)
End Function

Upvotes: -1

APW
APW

Reputation: 369

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))

Upvotes: 34

user7296559
user7296559

Reputation: 31

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.

Upvotes: 3

Mitch Wheat
Mitch Wheat

Reputation: 300559

   strScriptFullname = WScript.ScriptFullName 
   strScriptPath = Left(strScriptFullname, InStrRev(strScriptFullname,"\"))

Upvotes: 9

Related Questions