Help Please - Global Vars

[graviz]

I'm trying to improve my programming skills and I decided to try and use a global variable. When I run my code I receive the error "Invalid outside procedure" I know it must be something simple but I just can't figure it out.

Option Compare Database
Dim ExcelTemplate As String
ExcelTemplate = "\\Mer2-corpfs1\dnsc\Resource Management\Auto Reports\JBR\JBR Template.xls"

Public Function DoAll()
'Create connection to Database
Dim cnn As ADODB.Connection
Dim MyRecordset As New ADODB.Recordset
Dim MySQL As String
Dim MySheetPath As String


'Variables to refer to Excel and Objects
Dim Xl As Excel.Application
Dim XlBook As Excel.Workbook
Dim XlSheet As Excel.Worksheet
Set cnn = CurrentProject.Connection
MyRecordset.ActiveConnection = cnn
'SQL statements to extract the data required for the report - From a query
MySQL = "SELECT * From "
MySQL = MySQL & "05_Output_JBR_All_Types"
MyRecordset.Open MySQL
' Tell it location of actual Excel file
MySheetPath = ExcelTemplate
'Open Excel and the workbook
Set Xl = CreateObject("Excel.Application")
Set XlBook = GetObject(MySheetPath)
'Make sure excel is visible on the screen
Xl.Visible = True
XlBook.Windows(1).Visible = True
'Define the sheet in the Workbook as XlSheet
Set XlSheet = XlBook.Worksheets("Actual")
' Create column names in the spreadsheet
' For iCol = 1 To MyRecordset.Fields.Count
' XlSheet.Cells(2, iCol).Value = MyRecordset.Fields(iCol - 1).Name
' Next
' 'Insert the Recordset in the excel sheet starting at specified cell
XlSheet.Range("A2").CopyFromRecordset MyRecordset
'Clean up and end with worksheet visible on the screen
MyRecordset.Close
Set cnn = Nothing
Set Xl = Nothing
XlBook.Save
XlBook.Close
Set XlBook = Nothing
Set XlSheet = Nothing
End Function

[RuralGuy]

The 3rd line is outside any procedure and will not execute.

Option Compare Database
Dim ExcelTemplate As String
ExcelTemplate = "\\Mer2-corpfs1\dnsc\Resource Management\Auto Reports\JBR\JBR Template.xls"

Is this in a Standard Module or in a Class module of a form?

[graviz]

The 3rd line is outside any procedure and will not execute.

Option Compare Database
Dim ExcelTemplate As String
ExcelTemplate = "\\Mer2-corpfs1\dnsc\Resource Management\Auto Reports\JBR\JBR Template.xls"

Is this in a Standard Module or in a Class module of a form?

I figured it out. Instead of using the dim statement I would have to use "CONST". It was in a standard module by the way.

[RuralGuy]

That's great! Are you ready to follow the link in my sig and mark this thread as Solved?