How to: obtain the filename that a module exists in, via VBA?

[Guest]

TWIMC,

When my Auto_Open() kicks off I need to be able to establish the file name.
The reason for this is that when I'm designing my Excel Add in my file name
uses the .xls extension so i refer to my file as FileName.xls but when I want
to save my file as an Excel Add-in, the file extension changes to .xla, thus
I need to update my reference in the code. Therefore it would good if I can
obtain the file name when the Auto_Open() starts, then I don't have to
manualy change the reference.

Any ideas on how to accomplish this would be create.

TIA
KM

 

[Tom Ogilvy]

thisworkbook.Path
Thisworkbook.Fullname
Thisworkbook.Name

 

[Guest]

Hi,
you can use

ThisWorkbook.FullName
ThisWorkbook.Name
thisworkbook.Path

eg:
Dim wkb as Workbook
Set Wkb = ThisWorkbook
msgbox wkb.fullname

 

[ch]

Hi Tom,

How can we return a workbook's file name with and without extension?

Thanks in advance.

 

[Rick Rothstein]

Is this what you want?


With Extension:
=============================
ThisWorkbook.Name


Without Extension
=============================
Left(ThisWorkbook.Name, InStrRev(ThisWorkbook.Name, ".") - 1)