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

  • Thread starter Thread starter Don
  • Start date Start date
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
 
Don said:
Currently we are using a query to export some data to an Excel
spreadsheet:
Click to expand...
Private Sub ExportPositions_Click()
   DoCmd.OpenQuery "QRY_REPORT_EXCEL_AllPositions", acViewNormal,
acEdit
   DoCmd.RunCommand acCmdExportExcel
   DoCmd.Close acQuery, "QRY_REPORT_EXCEL_AllPositions"
End Sub
Click to expand...
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:
Click to expand...
DoCmd.RunSQL "SELECT * INTO tblTemp FROM tblTestData WHERE
tblTestData.Status=""Filled"""
Click to expand...
causes a pop-up appears asking if you would like to add xx rows to
tblTemp.
Click to expand...
Is there anyway to suppress the pop-up by somehow defaulting to
accepting the creation of the table?
Click to expand...
Is there a more elegant way to approach this problem?
Click to expand...

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 -
Click to expand...

Awesome!

Thanks Marsh!!

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

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