TransferSpreadsheet with Criteria

  • Thread starter Thread starter CJ
  • Start date Start date
C

CJ

Hi Groupies:

I am exporting data to excel using code. The query that I am exporting has
subqueries and although the criteria works fine in the queries when I run
the export the criteria has been ignored. I have done this before with no
trouble but this one instance has this problem.

From what I can tell, the problem seems to be that some of the queries and
subqueries are Total queries and have used grouping. I have put my criteria
in every single query involved but I still have the same issue.

The criteria looks to a form to find the matching EmployeeID, Start and
Finish dates.

Here is the code that I am using:

Dim xlApp As Object
Dim FieldPayrollDetail As String

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qrptExportFieldPay", "FieldPayrollDetail"
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True

With xlApp
.workbooks.Open FileName:="FieldPayrollDetail"
.Cells.Select
With .Selection.Font
.Name = "Arial"
.Size = 10
End With
<snip>

The WHERE statement for all of the queries is something similar to:

WHERE ((tblEmployees.lngEmpID) Like
[Forms]![frmExportFieldEmployeeReport]![cmbEmployee] & "*") AND
(tblCampCosts.dtmDate) Between
[Forms]![frmExportFieldEmployeeReport]![txtStart] And
[Forms]![frmExportFieldEmployeeReport]![txtEnd])

Any ideas?
 
As you were everybody, I got it.
The query that I was trying to export is now a Make Table query, I export
the table and then delete it.

DoCmd.SetWarnings False
DoCmd.OpenQuery "qmktExportCampPay"
DoCmd.OutputTo acOutputTable, "tblExportCampPay", acFormatXLS, , True
DoCmd.DeleteObject acTable, "tblExportCampPay"
DoCmd.SetWarnings True

Not sure what the deal is with TransferSpreadsheet......?!?!?
 
You cannot use a paramater query as the query that you export via
TransferSpreadsheet. You must create a new query that has evaluated the
parameter into the new query's SQL (as hardcoded value), and then export
that new query.

See the web page in my signature for a link to a page about EXCEL
import/export, it contains some ideas on how this can be done.
 
Thanks for the info Ken.

Problem solved.

--
Thanks for taking the time!

CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!
Ken Snell MVP said:
You cannot use a paramater query as the query that you export via
TransferSpreadsheet. You must create a new query that has evaluated the
parameter into the new query's SQL (as hardcoded value), and then export
that new query.

See the web page in my signature for a link to a page about EXCEL
import/export, it contains some ideas on how this can be done.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


CJ said:
Hi Groupies:

I am exporting data to excel using code. The query that I am exporting
has subqueries and although the criteria works fine in the queries when I
run the export the criteria has been ignored. I have done this before
with no trouble but this one instance has this problem.

From what I can tell, the problem seems to be that some of the queries
and subqueries are Total queries and have used grouping. I have put my
criteria in every single query involved but I still have the same issue.

The criteria looks to a form to find the matching EmployeeID, Start and
Finish dates.

Here is the code that I am using:

Dim xlApp As Object
Dim FieldPayrollDetail As String

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qrptExportFieldPay", "FieldPayrollDetail"
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True

With xlApp
.workbooks.Open FileName:="FieldPayrollDetail"
.Cells.Select
With .Selection.Font
.Name = "Arial"
.Size = 10
End With
<snip>

The WHERE statement for all of the queries is something similar to:

WHERE ((tblEmployees.lngEmpID) Like
[Forms]![frmExportFieldEmployeeReport]![cmbEmployee] & "*") AND
(tblCampCosts.dtmDate) Between
[Forms]![frmExportFieldEmployeeReport]![txtStart] And
[Forms]![frmExportFieldEmployeeReport]![txtEnd])

Any ideas?
Click to expand...
Click to expand...
 
Back
Top