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

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

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

thisworkbook.Path
Thisworkbook.Fullname
Thisworkbook.Name

--
Regards,
Tom Ogilvy

"Kevin McCartney" wrote in
message ...
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

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

Hi,
you can use

ThisWorkbook.FullName
ThisWorkbook.Name
thisworkbook.Path

eg:
Dim wkb as Workbook
Set Wkb = ThisWorkbook
msgbox wkb.fullname
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"Kevin McCartney" wrote:

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

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

Hi Tom,

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

Thanks in advance.

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

Is this what you want?


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


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


--
Rick (MVP - Excel)


"ch" wrote in message
...
Hi Tom,

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

Thanks in advance.