I have tried to save the name of the opened file to variable, so that I can
use it in other modules and worksheets. I have the following auto_open sub:


Sub auto_open()
Global filename As Variant
filename = Application.ThisWorkbook.Name

End Sub

This doesn't work, I have the Compile error message: Invalid attribute in
Sub or Function. Auto_open sub in the module, so what is wrong in this code?
Or is there any other way to do this?

try this, but i usually put all of the public variables in a separate module

Option Explicit
Public filename As String
Sub auto_open()

filename = Application.ThisWorkbook.Name

End Sub



--


Gary


"VBA beginner" wrote in message
...
I have tried to save the name of the opened file to variable, so that I can
use it in other modules and worksheets. I have the following auto_open sub:


Sub auto_open()
Global filename As Variant
filename = Application.ThisWorkbook.Name

End Sub

This doesn't work, I have the Compile error message: Invalid attribute in
Sub or Function. Auto_open sub in the module, so what is wrong in this code?
Or is there any other way to do this?

oh, and don't use filename, use fname or some other variant. filename is valid
property name in vba.

--


Gary


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
try this, but i usually put all of the public variables in a separate module

Option Explicit
Public filename As String
Sub auto_open()

filename = Application.ThisWorkbook.Name

End Sub



--


Gary


"VBA beginner" wrote in message
...
I have tried to save the name of the opened file to variable, so that I can
use it in other modules and worksheets. I have the following auto_open sub:


Sub auto_open()
Global filename As Variant
filename = Application.ThisWorkbook.Name

End Sub

This doesn't work, I have the Compile error message: Invalid attribute in
Sub or Function. Auto_open sub in the module, so what is wrong in this code?
Or is there any other way to do this?

Unfortunately I can't make this work. I have this public variable in separate
module and still the value of the fname variable is empty in the other
module.

"Gary Keramidas" kirjoitti:

oh, and don't use filename, use fname or some other variant. filename is valid
property name in vba.

--


Gary


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
try this, but i usually put all of the public variables in a separate module

Option Explicit
Public filename As String
Sub auto_open()

filename = Application.ThisWorkbook.Name

End Sub



--


Gary


"VBA beginner" wrote in message
...
I have tried to save the name of the opened file to variable, so that I can
use it in other modules and worksheets. I have the following auto_open sub:


Sub auto_open()
Global filename As Variant
filename = Application.ThisWorkbook.Name

End Sub

This doesn't work, I have the Compile error message: Invalid attribute in
Sub or Function. Auto_open sub in the module, so what is wrong in this code?
Or is there any other way to do this?

Did you run the Auto_Open procedure after you made a change to your code?

If no, then run that Auto_Open. Then do some experimentation with your other
procedure.

ps. I hope that this is a learning exercise. You'd really want to use
ThisWorkbook.Name in other procedures.



VBA beginner wrote:

Unfortunately I can't make this work. I have this public variable in separate
module and still the value of the fname variable is empty in the other
module.

"Gary Keramidas" kirjoitti:

oh, and don't use filename, use fname or some other variant. filename is valid
property name in vba.

--


Gary


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
try this, but i usually put all of the public variables in a separate module

Option Explicit
Public filename As String
Sub auto_open()

filename = Application.ThisWorkbook.Name

End Sub



--


Gary


"VBA beginner" wrote in message
...
I have tried to save the name of the opened file to variable, so that I can
use it in other modules and worksheets. I have the following auto_open sub:


Sub auto_open()
Global filename As Variant
filename = Application.ThisWorkbook.Name

End Sub

This doesn't work, I have the Compile error message: Invalid attribute in
Sub or Function. Auto_open sub in the module, so what is wrong in this code?
Or is there any other way to do this?








--

Dave Peterson