Export a Query

[slaga9]

I have a parameter query where the user can select the filters to each field through combo boxes.

I need to be able to send the results of the query to a template I have in excel.

I have been messing around with some code I have found online, but so far I am only able to export results of queries which I have built with the wizard, and that don't depend on combo boxes, but I need to be able to take that next step.

Below is the current code I am using, does anyone know what changes are necessary for me to be able to achieve my goal? Thanks- also I should mention I am a really elementary level user!

-Sean


Public Function ExportRequest() As String
On Error GoTo err_Handler

' Excel object variables
Dim appExcel As Excel.Application
Dim wbk As Excel.Workbook
Dim wks As Excel.Worksheet

Dim sTemplate As String
Dim sTempFile As String
Dim sOutput As String

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim sSQL As String
Dim lRecords As Long
Dim iRow As Integer
Dim iCol As Integer
Dim iFld As Integer

Const cTabTwo As Byte = 1
Const cStartRow As Byte = 11
Const cStartColumn As Byte = 1

DoCmd.Hourglass True

' set to break on all errors
Application.SetOption "Error Trapping", 0

' start with a clean file built from the template file
' sTemplate = CurrentProject.Path & "\salary recovery template.xls"
sOutput = CurrentProject.Path & "\salary recovery template.xls"
' If Dir(sOutput) <> "" Then Kill sOutput
' FileCopy sTemplate, sOutput

' 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)


sSQL = "SELECT Employee_ID.[Recovery No], Employee_ID.[Account Name], Employee_ID.[Gen Ledg Acnt No], "
sSQL = sSQL & "Timesheettable1.[Job Number], Employee_ID.Type, Employee_ID.unknown, "
sSQL = sSQL & "Employee_ID.[Ref No], Timesheettable1.Employee, "
sSQL = sSQL & "Employee_ID.Rate, Timesheettable1.[Hours Worked], Timesheettable1.[Hours Paid]"
sSQL = sSQL & " FROM Employee_ID INNER JOIN Timesheettable1 ON Employee_ID.Employee = Timesheettable1.Employee"
' sSQL = sSQL & " WHERE (((Timesheettable1.PayPeriodEnd)=[Forms]![searchform]![PayPeriodEnd]));"
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(sSQL, dbOpenSnapshot)



' For this template, the data must be placed on the 11th row, first column.
' (these values are set to constants for easy future modifications)
iCol = cStartColumn
iRow = cStartRow


Do Until rst.EOF
iFld = 0
lRecords = lRecords + 1
' Me.lblMsg.Caption = "Exporting record #" & lRecords & " to salary recovery template.xls"
' Me.Repaint

For iCol = cStartColumn To cStartColumn + (rst.Fields.Count - 1)
wks.Cells(iRow, iCol) = rst.Fields(iFld)

If InStr(1, rst.Fields(iFld).Name, "Date") > 0 Then
wks.Cells(iRow, iCol).NumberFormat = "mm/dd/yyyy"
End If

wks.Cells(iRow, iCol).WrapText = False
iFld = iFld + 1
Next

wks.Rows(iRow).EntireRow.AutoFit
iRow = iRow + 1
rst.MoveNext
Loop

ExportRequest = "Total of " & lRecords & " rows processed."
' Me.lblMsg.Caption = "Total of " & lRecords & " rows processed."

exit_Here:
' Cleanup all objects (resume next on errors)
On Error Resume Next
Set wks = Nothing
Set wbk = Nothing
Set appExcel = Nothing
Set rst = Nothing
Set dbs = Nothing
DoCmd.Hourglass False
Exit Function

err_Handler:
ExportRequest = Err.Description
' Me.lblMsg.Caption = Err.Description
Resume exit_Here

End Function

Private Sub cmdsearch_Click()
On Error GoTo err_Handler

MsgBox ExportRequest, vbInformation, "Finished"
Application.FollowHyperlink CurrentProject.Path & "\salary recovery template.xls"

exit_Here:
Exit Sub
err_Handler:
MsgBox Err.Description, vbCritical, "Error"
Resume exit_Here
End Sub

 

[lameid]

The code is building a query with an SQL statement using sSQL.

Access shows code that is commented as green by default and comments are usually done with a single quote.

So the following line looks good it is just commented so that it is not being used. Now whether the table, field, form and control on the form are all correct, I don't know but the syntax looks fine.

   ' sSQL = sSQL & " WHERE (((Timesheettable1.PayPeriodEnd)=[Forms]![searchform]![PayPeriodEnd]));"

 

[slaga9]

thank you for the response.

the tables and forms have all actually changed,
this was the code i was using with my first query.

When I ran it before, the where clause didnt work, so I put it in quotes, the query worked fine after that.

however now I want to apply the same code to my new query. However my new query is a parameter query, where the user selects its own filters via combo boxes on a form.

I would like to know the proper steps to take in order to export the results of the users query into an excel template I hate set up.

as of right now I have a button that runs the parametric query, how do I go about hooking up an export comand to that? Id imagine my original code will need a few changes in addition to having to change the field names.

thanks
Sean

thanks
Sean

 

[lameid]

'Set rst = dbs.OpenRecordset(sSQL, dbOpenSnapshot)

Set rst = dbs.OpenRecordset(<Query Name as string>, dbOpenSnapshot)

Query Name as string means put double quotes around the queryname.

 

[lameid]

You can also comment out anything to do with sSQL.

 

[slaga9]

hey Lameid, I am still having issues. however I just thought of something else.

would it be possible to just have my form with combo boxes and a button to run the query,

and then have a seperate comand button to export the query?
I guess what im asking is, everytime I click "run query" would the second button export the updated query results?

 

[lameid]

When you execute

Set rst = dbs.OpenRecordset(<Query Name as string>, dbOpenSnapshot)

it runs the query and whatever it would return as if you hit the button that just runs it is the same records it is going to see.