Const AFile as string = "C:\xxxxx\xxxxx\xxxx.mdb" placed at the top of a
standard module but fails to show in the procedures other than the top
procedure.

Any ideas why??

TIA johnb

On May 19, 9:48 am, johnb wrote:
Const AFile as string = "C:\xxxxx\xxxxx\xxxx.mdb" placed at the top of a
standard module but fails to show in the procedures other than the top
procedure.

Any ideas why??

TIA johnb

Both of the following subs work fine for me. Can you be more specific
about what's happening for you?


Option Explicit

Const AFile As String = "C:\xxxxx\xxxxx\xxxx.mdb"

Public Sub Test()
MsgBox "1: " & AFile
End Sub

Public Sub Test2()
MsgBox "2: " & AFile
End Sub


-Bryan

Hi Bryan,
I'm building, using vba, 10 pivottables within a workbook but on seperate
sheets. The procedures are all in one standard module. The code that fails is
the const DBFile. The DBFile just returns "" Except in the top Procedure!

Set PTcache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlExter nal)
'connect to database
con = "ODBC;DSN=MS Access Database;DBQ=" & DBFile

Regard johnb


"Bryan Loeper" wrote:

On May 19, 9:48 am, johnb wrote:
Const AFile as string = "C:\xxxxx\xxxxx\xxxx.mdb" placed at the top of a
standard module but fails to show in the procedures other than the top
procedure.

Any ideas why??

TIA johnb

Both of the following subs work fine for me. Can you be more specific
about what's happening for you?


Option Explicit

Const AFile As String = "C:\xxxxx\xxxxx\xxxx.mdb"

Public Sub Test()
MsgBox "1: " & AFile
End Sub

Public Sub Test2()
MsgBox "2: " & AFile
End Sub


-Bryan

John,
Assuming that statement is in the Declares section of the module, the only
other reason would be a misspelt variable name.
Are you using Option Explicit ?

NickHK

"johnb" wrote in message
...
Hi Bryan,
I'm building, using vba, 10 pivottables within a workbook but on seperate
sheets. The procedures are all in one standard module. The code that fails
is
the const DBFile. The DBFile just returns "" Except in the top Procedure!

Set PTcache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlExter nal)
'connect to database
con = "ODBC;DSN=MS Access Database;DBQ=" & DBFile

Regard johnb


"Bryan Loeper" wrote:

On May 19, 9:48 am, johnb wrote:
Const AFile as string = "C:\xxxxx\xxxxx\xxxx.mdb" placed at the top of
a
standard module but fails to show in the procedures other than the top
procedure.

Any ideas why??

TIA johnb

Both of the following subs work fine for me. Can you be more specific
about what's happening for you?


Option Explicit

Const AFile As String = "C:\xxxxx\xxxxx\xxxx.mdb"

Public Sub Test()
MsgBox "1: " & AFile
End Sub

Public Sub Test2()
MsgBox "2: " & AFile
End Sub


-Bryan

Hi Nick

It smacks you on the nosedoes it not? Option Explicit is the answer. Const
now works perfectly.

Many thanks

johnb



"NickHK" wrote:

John,
Assuming that statement is in the Declares section of the module, the only
other reason would be a misspelt variable name.
Are you using Option Explicit ?

NickHK

"johnb" wrote in message
...
Hi Bryan,
I'm building, using vba, 10 pivottables within a workbook but on seperate
sheets. The procedures are all in one standard module. The code that fails
is
the const DBFile. The DBFile just returns "" Except in the top Procedure!

Set PTcache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlExter nal)
'connect to database
con = "ODBC;DSN=MS Access Database;DBQ=" & DBFile

Regard johnb


"Bryan Loeper" wrote:

On May 19, 9:48 am, johnb wrote:
Const AFile as string = "C:\xxxxx\xxxxx\xxxx.mdb" placed at the top of
a
standard module but fails to show in the procedures other than the top
procedure.

Any ideas why??

TIA johnb

Both of the following subs work fine for me. Can you be more specific
about what's happening for you?


Option Explicit

Const AFile As String = "C:\xxxxx\xxxxx\xxxx.mdb"

Public Sub Test()
MsgBox "1: " & AFile
End Sub

Public Sub Test2()
MsgBox "2: " & AFile
End Sub


-Bryan

John,
For me, there is no reason not to use Option Explicit.
You will avoid wasting time (and hair) on these spelling mistakes.

NickHK

"johnb" wrote in message
...
Hi Nick

It smacks you on the nosedoes it not? Option Explicit is the answer. Const
now works perfectly.

Many thanks

johnb



"NickHK" wrote:

John,
Assuming that statement is in the Declares section of the module, the
only
other reason would be a misspelt variable name.
Are you using Option Explicit ?

NickHK

"johnb" wrote in message
...
Hi Bryan,
I'm building, using vba, 10 pivottables within a workbook but on
seperate
sheets. The procedures are all in one standard module. The code that
fails
is
the const DBFile. The DBFile just returns "" Except in the top
Procedure!

Set PTcache = ActiveWorkbook.PivotCaches.Add(SourceType:=xlExter nal)
'connect to database
con = "ODBC;DSN=MS Access Database;DBQ=" & DBFile

Regard johnb


"Bryan Loeper" wrote:

On May 19, 9:48 am, johnb wrote:
Const AFile as string = "C:\xxxxx\xxxxx\xxxx.mdb" placed at the
top of
a
standard module but fails to show in the procedures other than the
top
procedure.

Any ideas why??

TIA johnb

Both of the following subs work fine for me. Can you be more
specific
about what's happening for you?


Option Explicit

Const AFile As String = "C:\xxxxx\xxxxx\xxxx.mdb"

Public Sub Test()
MsgBox "1: " & AFile
End Sub

Public Sub Test2()
MsgBox "2: " & AFile
End Sub


-Bryan