How to Build an SQL Query on the Fly for Data Export to Excel

D

Don

Currently we are using a query to export some data to an Excel
spreadsheet:

Private Sub ExportPositions_Click()
DoCmd.OpenQuery "QRY_REPORT_EXCEL_AllPositions", acViewNormal,
acEdit
DoCmd.RunCommand acCmdExportExcel
DoCmd.Close acQuery, "QRY_REPORT_EXCEL_AllPositions"
End Sub

The customer likes the format of the resulting Excel spreadsheet, but
we need a bit more flexibility in building the query on the fly, The
"obvious" solution would be to replace the DoCmd.OpenQuery with a
DoCmd.RunSQL and provide an appropriate SQL string making a temporary
table and deleting it after use. However, the option making an
intermediate table:

DoCmd.RunSQL "SELECT * INTO tblTemp FROM tblTestData WHERE
tblTestData.Status=""Filled"""

causes a pop-up appears asking if you would like to add xx rows to
tblTemp.

Is there anyway to suppress the pop-up by somehow defaulting to
accepting the creation of the table?

Is there a more elegant way to approach this problem?

Thanks!

Don
 
M

Marshall Barton

Don said:
Currently we are using a query to export some data to an Excel
spreadsheet:

Private Sub ExportPositions_Click()
DoCmd.OpenQuery "QRY_REPORT_EXCEL_AllPositions", acViewNormal,
acEdit
DoCmd.RunCommand acCmdExportExcel
DoCmd.Close acQuery, "QRY_REPORT_EXCEL_AllPositions"
End Sub

The customer likes the format of the resulting Excel spreadsheet, but
we need a bit more flexibility in building the query on the fly, The
"obvious" solution would be to replace the DoCmd.OpenQuery with a
DoCmd.RunSQL and provide an appropriate SQL string making a temporary
table and deleting it after use. However, the option making an
intermediate table:

DoCmd.RunSQL "SELECT * INTO tblTemp FROM tblTestData WHERE
tblTestData.Status=""Filled"""

causes a pop-up appears asking if you would like to add xx rows to
tblTemp.

Is there anyway to suppress the pop-up by somehow defaulting to
accepting the creation of the table?

Is there a more elegant way to approach this problem?


Better to set an existing query's SQL property and then use
the TransferSpreadsheet method (see VBA Help for details).
The code could be something like:

Dim db As Database
Dim qdf As DAO.QueryDef
Dim strWhere As String
Const strBaseQuery As String = "SELECT * FROM tblTestData "
Set db = CurrentDb()
Set qdf = db.QueryDefs!QRY_REPORT_EXCEL
'construct the query's where clause
strWhere = "WHERE tblTestData.Status='Filled' "

qdf.SQL = strBaseQuery & strWhere
Set qdf = Nothing
Set db = Nothing
DoCmd.TransferSpreadsheet acExport, , QRY_REPORT_EXCEL, _
"path\workbookfile", ...
 
D

Don

Don said:
Currently we are using a query to export some data to an Excel
spreadsheet:
Private Sub ExportPositions_Click()
   DoCmd.OpenQuery "QRY_REPORT_EXCEL_AllPositions", acViewNormal,
acEdit
   DoCmd.RunCommand acCmdExportExcel
   DoCmd.Close acQuery, "QRY_REPORT_EXCEL_AllPositions"
End Sub
The customer likes the format of the resulting Excel spreadsheet, but
we need a bit more flexibility in building the query on the fly, The
"obvious" solution would be to replace the DoCmd.OpenQuery with a
DoCmd.RunSQL and provide an appropriate SQL string making a temporary
table and deleting it after use.  However, the option making an
intermediate table:
DoCmd.RunSQL "SELECT * INTO tblTemp FROM tblTestData WHERE
tblTestData.Status=""Filled"""
causes a pop-up appears asking if you would like to add xx rows to
tblTemp.
Is there anyway to suppress the pop-up by somehow defaulting to
accepting the creation of the table?
Is there a more elegant way to approach this problem?

Better to set an existing query's SQL property and then use
the TransferSpreadsheet method (see VBA Help for details).
The code could be something like:

Dim db As Database
Dim qdf As DAO.QueryDef
Dim strWhere As String
Const strBaseQuery As String = "SELECT * FROM tblTestData "
Set db = CurrentDb()
Set qdf = db.QueryDefs!QRY_REPORT_EXCEL
        'construct the query's where clause
strWhere = "WHERE tblTestData.Status='Filled' "

qdf.SQL = strBaseQuery & strWhere
Set qdf = Nothing
Set db = Nothing
DoCmd.TransferSpreadsheet acExport, , QRY_REPORT_EXCEL, _
                   "path\workbookfile", ...

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -

Awesome!

Thanks Marsh!!

Don
 
M

Mike Painter

Don wrote:
causes a pop-up appears asking if you would like to add xx rows to
tblTemp.

See SetWarnings in help to elimianate these questions
DoCmd SetWarnings False
DoCmd SetWarnings True
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top