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

Stuart,

Sometimes the answer to one question creates a solution for another, for 
example I need to export a simple Excel report once a month or so and have 
been using functions or stored procedures to do this, this worked and is 
very simple, no error protection yet just an idea that may help others.

Public Function test()
  Dim rs As New ADODB.Recordset
  Dim cn As New ADODB.Connection
  Dim strSQl As String
  Set cn = CurrentProject.AccessConnection
  strSQl = "Select * from vUnits where UnitID between 19445 and 19448"
  rs.Open strSQl, cn, adOpenKeyset, adLockOptimistic
  DoCmd.OutputTo acOutputServerView, strSQl, acFormatXLS, "C:\test.xls", 
True
End Function

I tried to use a recordset in and MDB but that didn't work.

Thanks

Bill



----- Original Message ----- 
From: "Stuart McLachlan" <stuart at lexacorp.com.pg>
To: "Access Developers discussion and problem solving" 
<accessd at databaseadvisors.com>
Sent: Friday, March 03, 2006 11:28 PM
Subject: Re: [AccessD] Code to Copy a query to Excel and Open said file?


On 3 Mar 2006 at 23:32, 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.

DoCmd.OutputTo acOutputQuery, "qryTest", acFormatXLS, "Test.XLS", True-- 
Stuart


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com