I'm having issues getting exported access data to go to different excel work pages.
this is what I am using on the on click property of my export function
Private Sub exportcmd_Click()
On Error GoTo err_Handler
Const cTabTwo As Byte = 1
' Excel object variables
Dim appExcel As Excel.Application
Dim wbk As Excel.Workbook
Dim wks As Excel.Worksheet
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim rstOutput As DAO.Recordset
Dim sOutput As String
Dim sqlString As String
Dim shtArray As Variant
Dim siteArray As Variant
Dim I As Integer
Set db = CurrentDb
Set qdf = db.QueryDefs("paraquery")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rstOutput = qdf.OpenRecordset
DoCmd.Hourglass True
' set to break on all errors
Application.SetOption "Error Trapping", 0
' start with a clean file built from the template file
sOutput = CurrentProject.Path & "\salary recovery template.xls"
' Create the Excel Applicaiton, Workbook and Worksheet and Database object
Set appExcel = Excel.Application
Set wbk = appExcel.Workbooks.Open(sOutput)
Set wks = appExcel.Worksheets(cTabTwo)
appExcel.Visible = True
'Take the records in the current form and dump to Excel
siteArray = Array(Pick - A, Pick - B, Darl, Bruce, OVERHEAD)
shtArray = Array(PickA549, PickB349, Darl348, Bruce347, Other)
For I = 0 To 4
sqlString = "SELECT * FROM paraquery WHERE [jobsite] = " & siteArray(I)
Set rstOutput = CurrentDb.OpenRecordset(sqlString)
wks.Worksheets.Add.Name = shtArray(I)
wks.Worksheets(shtArray(I)).Activate
wks.Range("A11").CopyFromRecordset rstOutput
I = I + 1
Loop
exit_Here:
' Cleanup all objects (resume next on errors)
On Error Resume Next
Set wks = Nothing
'You may want to save the workbook here
Set wbk = Nothing
Set appExcel = Nothing
rstOutput.Close
Set rstOutput = Nothing
DoCmd.Hourglass False
' Exit exportcmd_Click
err_Handler:
ExportRequest = Err.Description
' Me.lblMsg.Caption = Err.Description
'Resume exit_Here
End Sub
currently the error I receive is a compile error, method or data is not found. the debugger highlights wks.worksheetsdoes anyone see what I am missing?
your help would be greatly appreciated!
thanks
Sean