Excel Worksheets in Access VBA

[MarkDotNet]

Hi

I am trying to switch Excel worksheets in VBA. I get an error saying "subscript out of range". Please Help- Here is code (Fails on last 2 lines- Note that I ommited the recordset portion of code because it works):

Dim objXLApp As Object
Dim objXLws As Object

Dim strSDocPath As String 'Full path/name of template (Source) file
Dim strTPath As String 'Full path of (Target) file
Dim strTDocPath As String 'Full path/name of (Target) file


strSDocPath = "\\Saturn\Datab ases\Tracer\Tem plates\SigmaTem plate.xls"
strTPath = "C:\AccessExpor t"
strTDocPath = "C:\AccessExpor t\Sigma.xls"
strCurrentRunNo = [Forms]![frmRCESapphire]![RCErunno]
' Create the Excel object
Set objXLApp = CreateObject("E xcel.Applicatio n")

' Open the template workbook file
objXLApp.Workbo oks.Open (strSDocPath)

' Save the template as the file specified by the user
objXLApp.Active Workbook.SaveAs (strTDocPath)

' Select the Worksheet
Set objXLws = objXLApp.Active Workbook.Worksh eets("SIGMA")

' Activate the selected worksheet
objXLws.Activat e

' Ask Excel to copy the data from the recordset starting with Cell A5
objXLws.Range(" B2").Value = strCurrentRunNo
objXLws.Range(" B3").Value = Now()
objXLws.Range(" A6").CopyFromRe cordset rstSigmaExport

' Select the alternate worksheet
objXLApp.Worksh eets("Rates").A ctivate

' Activate the alternate worksheet
Set objXLws = objXLApp.Active Workbook.Worksh eets("Rates")

[janders468]

Just to rule this out quickly, are you sure that worksheet "Rates" exists (isn't misspelled or anything)? This is the error you get when you try to activate a non-existent worksheet.