Export Query to Excel code

[ajolson1964]

I am exporting a query to excel using the following code:
DoCmd.OutputTo acOutputQuery, "AllOut", acFormatXLS, , True

It creates a new excel file called AllOut and places the information in a worksheet that it names AllOut. Where that works I would like to take this to the next level and just cant.

What I would like to do is when the query is exported I want the query exported to a excel file that has already been created. Specifically I would like the query to open up that pre-determined file and place the results of the query in a specified worksheet within the excel file. And if at all possible I would like it to place it in a pre-determined row or cell within that worksheet, although the last is not that critical.

Any help would be appreciated

Thanks
Andy

 

[PHV]

What about the DoCmd.Transfer Spreadsheet method ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[pkj14]

Try this -
Sub Export2xl()
Dim xl As Excel.Application
Dim ws As Excel.WorkSheet
Dim wb As Excel.Workbook

Dim tbl as ADODB.Recordset
Dim x As Long

Set tbl = New ADODB.Recordset
Set xl = New Excel.Application
Set wb = xl.workbooks.Add
Set ws = wb.sheets("Sheet1")

'First row contains Field Names
ws.Cells(1,1) = "ID"
ws.Cells(1,2) = "FName"
ws.Cells(1,3) = "LName"
.
.
'Write data in other rows
x = 2
Do While Not tbl.EOF

ws.Cells(x,1) = tbl.Fields("ID")
ws.Cells(x,2) = tbl.Fields("FName")
ws.Cells(x,3) = tbl.Fields("LName")
.
.
tbl.MoveNext
x = x + 1
Loop

Set ws = Nothing
wb.Close True, "C:\path\file.xls '(OR .xlsx for Excel
2007)
Set ws = Nothing
Set xl = Nothing

End Sub

-PKJ14