VBA to read spreadsheet's filename?

[Jon]

Hi,

I'm a novice at Excel VBA and looking for a way to retrieve the
filename (e.g. asdfasdf.xls) of the current spreadsheet. Is there a
way to do this?

Thanks
Jon

 

[Chip Pearson]

Jon,

Try something like

Dim S As String
S = ActiveWorkbook.Name
' or
S = ThisWorkbook.Name

Use FullName instead of Name if you want the full name (drive, folder, and
file) of the file.

 

[losmac]

MsgBox ThisWorkbook.FullName

losmac

 

[David McRitchie]

Which is probably what Jon really would wanted but not what he asked for.
More details on my http://www.mvps.org/dmcritchie/excel/pathname.htm
Page also has worksheet solutions as well as main emphasis on headers/footers..

Code in VBA
Description VBA codeExample
Full Name: Application.ActiveWorkbook.FullName D:\TestFolder\test.xls
Path: Application.ActiveWorkbook.Path C:\MSOffice\Excel
Filename: Application.ActiveWorkbook.Name test.xls
Sheetname: application.activesheet.name Sheet1