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

  • Thread starter Kevin McCartney
  • Start date
K

Kevin McCartney

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
 
S

sebastienm

Hi,
you can use

ThisWorkbook.FullName
ThisWorkbook.Name
thisworkbook.Path

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

ch

Hi Tom,

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

Thanks in advance.
 
R

Rick Rothstein

Is this what you want?


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


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