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" <(E-Mail Removed)> wrote in message
news:802D9FE7-F54F-4EDD-AE75-(E-Mail Removed)...
>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
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:802D9FE7-F54F-4EDD-AE75-(E-Mail Removed)...
>>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
> news:(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> > news:802D9FE7-F54F-4EDD-AE75-(E-Mail Removed)...
> >>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
> > news:(E-Mail Removed)...
> > > 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" <(E-Mail Removed)> wrote in message
> > > news:802D9FE7-F54F-4EDD-AE75-(E-Mail Removed)...
> > >>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