Access SQL Statement to Excel 1

[scotttom]

HI,

I need to push the data that results from a SQL statement to Excel. I can do this in an access report using the DoCmd.OpenReport and the where clause, but I'm stumped by how to get it directly into Excel.

Thanks in advance for any help.

Scott

 

[PHV]

One way is to play with the CopyFromRecordset method of the Excel.Range object.
Another way is to pull the data with an Excel.QueryTable object.
Yet another way is to create a QueryDef and play with the DoCmd.TransferSpreadsheet method.
What have you tried so far ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[nice95gle]

You can get the data straight to xl via ado.

quick example connecting to SQL Server from excel macro.

Dim MySQL As String
Dim rs As ADODB.Recordset
Dim adoConn As ADODB.Connection

Set adoConn = New ADODB.Connection

adoConn.ConnectionString = "provider=sqloledb;data source=your_Server;Initial Catalog=your_database;user id=your_user;pwd=your_password"

adoConn.Open

MySQL = "Select field1,field2 From your_database.dbo.TABLE1"

Set rs = adoConn.Execute(MySQL)

Application.Cells(1, 1).Activate
Application.ActiveCell.CopyFromRecordset rs

adoConn.Close