Click buttons to start queries and export the results to Excel

[Jo Gjessing]

Hi all,

In a database of mine I've created some simple queries. Now I want my users
to be able to click buttons to start the queries and export the results to
Excel spreadsheets. Can anyone of you tell me how I do this? Thank you very
much in advance.

Jo

 

[Brendan Reynolds]

Hi all,

In a database of mine I've created some simple queries. Now I want my
users
to be able to click buttons to start the queries and export the results to
Excel spreadsheets. Can anyone of you tell me how I do this? Thank you
very
much in advance.

Jo

Here's an example that saves a query named "qryTest2" to a file called
"test.xls" in the same folder as the application in which the code is
executed. See "TransferSpreadsheet" in the help file for more information.

Private Sub cmdTest_Click()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryTest2",
CurrentProject.Path & "\test.xls"
End Sub

 

[Jo Gjessing]

Hi Brendan,

Thanky you very much for your rapid response. Now I have inserted it into
the right place in my database. It functions okay except that it doesn't open
the spreadsheet when made. Can you please tell me how I fix that. Thank you
very much in advance ...

Jo


Brendan Reynolds skrev:

 

[Klatuu]

see VBA Help for the Shell statement. It allows you to open another
application from within Access.

 

[Brendan Reynolds]

You could use OutputTo instead of TransferSpreadsheet, it has an AutoStart
argument which, if set to True, opens the exported document. Here's the
previous example modified to use OutputTo ...

Private Sub cmdTest_Click()
'DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qryTest2", CurrentProject.Path & "\test.xls"
DoCmd.OutputTo acOutputQuery, "qryTest2", acFormatXLS,
CurrentProject.Path & "\test.xls", True

End Sub