[AccessD] Code to Copy a query to Excel and Open said file

I haven't been following the replies to this, but to open a query in
Excel, I use:

Private Sub QueryToExcel(ByVal theQuery As String)
    DoCmd.Echo False
    DoCmd.OpenQuery theQuery, acViewPreview, acReadOnly
    DoCmd.RunCommand acCmdOutputToExcel
    DoCmd.Close acQuery, theQuery
    DoCmd.Echo True
    
End Sub

Turning off echo avoids the 'flash' when the query opens.

Another handy option is acCmdOutputToRTF, which sends the query to Word
nicely formatted as a Word table.

Phil Jewett
Phil Jewett Consulting
pjewett at bayplace.com
(619) 318-4899

Arthur Fuller wrote:
>I want to open an Access query and have its contents sent to an Excel 
>file, and have Excel open automatically with said file visible. I have 
>played around a little with TransferSpreadsheet, and if this method 
>works, then I am clearly doing something wrong. I have a sample file 
>resembling the desired result in Excel, so I could use that as a 
>template in the "Word" sense, but I need some guidance as to how to 
>achieve this, because my experiments have all failed. I checked Helen 
>Feddema's site but her
>Access->Excel heading says "Coming Soon".
>Have you any suggestions in the interim?
>TIA,
>Arthur