export data to multple work pages

[slaga9]

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

 

[VRoscioli]

You need to put "quotes" around the worksheet names. They are strings. Also, in the future please use the code tags (click on the Process TGML link below for more information).


-V

 

[PHV]

I'd try this:

   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)
      Set wks = wbk.Worksheets.Add
      wks.Name = shtArray(I)
      wks.Range("A11").CopyFromRecordset rstOutput
   Next

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[slaga9]

Thanks everyone for your responses,
sorry it took so long to get back to you, I was out of the country.

I have made some changes and there has been some improvement however I am still missing something.

now when I run Query, everything still works fine,
when I click the export the program opens, however no data is transferred...the spreadsheet is blank.
any ideas?


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)
Set wks = wbk.Worksheets.Add
wks.Name = shtArray(I)
wks.Range("A11").CopyFromRecordset rstOutput
Next

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

 

[VRoscioli]

Have you tried stepping through the procedure to see if it is executing as you expect it to?


-V

 

[VRoscioli]

Judging by the looks of things, I'm guessing you are getting an error and it is going into your ErrorHandler and then exitting the subroutine.


-V

 

[slaga9]

this is the line which comes back highlited when I step through the program.

Set rstOutput = CurrentDb.OpenRecordset(sqlString)

However, that was the first time I have ever tried to step through, so I could have done it wrong.

what does this mean? Does that mean that there is something wrong with the sqlstring?

 

[VRoscioli]

I suggest disabling your error handler and seeing what error is being thrown, if any.


-V

 

[slaga9]

Hi VRoscioli, thanks for the reply.

I am not having much luck. The error msg is run time error 3061 "to few paramters"

the line which is returned highlited is
Set rstOutputCurrentDb.OpenRecordset(sqlString) =

however I get the feeling that I am going about this the wrong way.
any thoughts?
I must apologize for my lack of know how!