Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

excel export VBA

Status
Not open for further replies.

slaga9

Technical User
Jun 8, 2007
46
CA
I am trying to move the results of a parameter query to a formatted excel file. I use 2 command buttons on a form, the first to run the query (works) the second to export the data to excel (driving me insane)

I am having problems having the information go to different work pages based on some criteria in the query. (specifically there are 5 possible sites in the access file, each entry has one, I want each site to have its own tab in the excel file)

I assume it is a syntax error of some sorts that I am getting.

If anyone here has time to look over it and notice what I am doing wrong It is greatly appreciated.
If I have posted this in the wrong area please let me know, and I apologize for the inconvenience.
thanks
Sean


This is my code:
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("PickA", "PickB", "Darl", "Bruce", "Other", "Overhead")
shtArray = Array("PickA549", "PickB349", "Darl348", "Bruce347", "Other", "Overhead")
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

I am getting the error 3061- too few parameters, expected 6.
the debugger is highliteing this line
Set rstOutput = CurrentDb.OpenRecordset(sqlString)

my sqlsatement looks like this
SELECT [Employee_ID].[Recovery No], [Employee_ID].[Account Name], [Employee_ID].[Gen Ledg Acnt No], [Timesheettable1].[JobNumbers], [Employee_ID].[Type], [Employee_ID].[unknown], [Employee_ID].[Ref No], [Timesheettable1].[Employee], [Employee_ID].[Rate], [Timesheettable1].[Hours Worked], [Timesheettable1].[Hours Paid]
FROM [Job Number] INNER JOIN (Employee_ID INNER JOIN Timesheettable1 ON [Employee_ID].[Employee]=[Timesheettable1].[Employee]) ON [Job Number].[Job Number]=[Timesheettable1].[JobNumbers]
WHERE (([Employee_ID].[Recovery No]) Like '*' & [Forms]![srchfrm]![recovnumber] & '*') And ([Timesheettable1].[JobNumbers]) Like '*' & [Forms]![srchfrm]![jobnumbersrchfrm] & '*' And ([Timesheettable1].[Employee]) Like '*' & [Forms]![srchfrm]![employee] & '*' And ([Timesheettable1].[PayPeriodEnd]) Like '*' & [Forms]![srchfrm]![payday] & '*' And ([Job Number].[Job Site]) Like '*' & [Forms]![srchfrm]![site] & '*';
 
I should also mention that upon hitting the export to excel command, the excel file does open. just no data gets passed...I'm assuming my problem is in identifying the record set? and by that i mean I really dont know

also...sorry about the lack of TGML on that last post!
 
It would be better to post in forum705. "Too few parameters .." indicates an error in the SQL. It seems that you are using an SQL statement in VBA that references a form, you cannot do this, subsitute the actual values:

[tt]"<...>
WHERE [Employee_ID].[Recovery No] Like '*" _
& [Forms]![srchfrm]![recovnumber] _
& "*') And ([Timesheettable1].[JobNumbers]) Like '*" _
& [Forms]![srchfrm]![jobnumbersrchfrm] _
& "*' And ([Timesheettable1].[Employee]) Like '*" _
& [Forms]![srchfrm]![employee] _
& "*' And ([Timesheettable1].[PayPeriodEnd]) Like '*" _
& [Forms]![srchfrm]![payday] _
& "*' And ([Job Number].[Job Site]) Like '*" _
& [Forms]![srchfrm]![site] & "*'"[/tt]
 
thank you for the response as well as the direction for later posts.

I have decided on taking this project a different route, however I have another question for you.

I have broken down each site into its own query. and given each query an export command button.

the very first option opens the excel template.
now I need the rest of the Query’s to go to the open excel file. (without trying to open it again)
if you have any advice on this it would be greatly appreciated!
thanks
Sean
 
Is this template essential? Have you considered TransferSpreadsheet? It is possible to Data->Get External Data in Excel to return data from Access. You will find a number of posts regarding Access and Excel in the forum I mentioned, and in the five other dedicated Access fora, as well as forum707.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top