By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,121 Members | 1,706 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,121 IT Pros & Developers. It's quick & easy.

Excel Worksheets in Access VBA

P: 1
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\Databases\Tracer\Templates\SigmaTemplate .xls"
strTPath = "C:\AccessExport"
strTDocPath = "C:\AccessExport\Sigma.xls"
strCurrentRunNo = [Forms]![frmRCESapphire]![RCErunno]
' Create the Excel object
Set objXLApp = CreateObject("Excel.Application")

' Open the template workbook file
objXLApp.Workbooks.Open (strSDocPath)

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

' Select the Worksheet
Set objXLws = objXLApp.ActiveWorkbook.Worksheets("SIGMA")

' Activate the selected worksheet
objXLws.Activate

' 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").CopyFromRecordset rstSigmaExport

' Select the alternate worksheet
objXLApp.Worksheets("Rates").Activate

' Activate the alternate worksheet
Set objXLws = objXLApp.ActiveWorkbook.Worksheets("Rates")
Aug 13 '08 #1
Share this Question
Share on Google+
1 Reply


Expert 100+
P: 112
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.
Aug 14 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.