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

On Feb 5, 11:50*am, Marshall Barton <marshbar...@wowway.com> wrote:
> Don wrote:
> >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

Don wrote:
<snip>
>
> 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

On Feb 5, 10:53*pm, "Mike Painter" <md.pain...@sbcglobal.net> wrote:
> Don wrote:
>
> <snip>
>
>
>
> > 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

Also awesome!

Thanks Mike!!


Don