[FAQ's: OD] How do I transfer data from SQL Server to Excel using ADO?
You can do this many ways. One way is to create an ADO recordset of the records you wish to export to Excel using Excel's Object Model.
Here is my example of using Excel's Object Model.
VB 6 Code Example:
VB Code:
Option Explicit
'Add a reference to MS Excel xx.0 Object Library
'Add a reference to MS ActiveX Data Objects 2.x Library
Private Sub Command1_Click()
Dim oRs As ADODB.Recordset
Dim oCnn As ADODB.Connection
Dim oApp As Excel.Application
Dim oWB As Excel.Workbook
Dim i As Integer
'Connect to your SQL Server DB
Set oCnn = New ADODB.Connection
oCnn.ConnectionString = "provider=sqloledb;data source=MySQLServer;initial catalog=MyDatabase;integrated security=sspi;"
oCnn.Open
'Create your recordset
Set oRs = New ADODB.Recordset
oRs.Open "SELECT * FROM Table1;", oCnn, adOpenKeyset, adLockReadOnly, adCmdText
'Create an instance of Excel and add a new blank workbook
Set oApp = New Excel.Application
oApp.Visible = False
Set oWB = oApp.Workbooks.Add
'Add the field names as column headers (optional)
For i = 0 To oRs.Fields.Count - 1
oWB.Sheets(1).Cells(1, i + 1).Value = oRs.Fields(i).Name
Next
oWB.Sheets(1).Range("1:1").Font.Bold = True
'Copy the records to the Excel worksheet
oWB.Sheets(1).Cells(2, 1).CopyFromRecordset oRs
oRs.Close
Set oRs = Nothing
oCnn.Close
Set oCnn = Nothing
oWB.Close SaveChanges:=True, FileName:="D:\Test.xls"
Set oWB = Nothing
oApp.Quit
Set oApp = Nothing
End Sub