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

[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

 

[Marshall Barton]

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", ...

 

[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?

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

 

[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

 

[Don]

Don wrote:

<snip>




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

Also awesome!

Thanks Mike!!


Don