Excel Export to a Specific Worksheet

[clk]

I have the following code :

Set appExcel = GetObject(, "Excel.Application")
strTemplatePath = "C:\ywca\january 2009\"
strWorksheet = "CountyTemplate.xlt"
strWorksheetPath = strTemplatePath & strWorksheet
strEmpty = Chr$(34) & Chr$(34)

Set bks = appExcel.Workbooks

'Open the workbook
bks.Add strWorksheetPath



'set reference to a query/table
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblCountyTemplate")
lngCount = rst.RecordCount
If lngCount = 0 Then
MsgBox "No Records to Export"
Exit Sub
Else
MsgBox lngCount & " records to export to Excel"
End If

This works great with one worksheet in a workbook. I need it to go to
a specific worksheet. This particular template has three sheets.

Any help would be appreciated.

 

[Ken Snell \(MVP\)]

Your code doesn't show how you actually do the export.... it would be
helpful to us if you post that code as well so that we can advise you about
how to send the data to a specific worksheet.

 

[Keith Wilby]

I have the following code :

Set appExcel = GetObject(, "Excel.Application")
strTemplatePath = "C:\ywca\january 2009\"
strWorksheet = "CountyTemplate.xlt"
strWorksheetPath = strTemplatePath & strWorksheet
strEmpty = Chr$(34) & Chr$(34)

Set bks = appExcel.Workbooks

'Open the workbook
bks.Add strWorksheetPath

Just declare a worksheet object and tell Excel to make it the active one.
My object variable names are different to yours but this snippet should
point you in the right direction:

Set objSht = objWkb.Worksheets("Sheet1")
objSht.Activate

Keith.
www.keithwilby.co.uk