[FAQ's: OD] How do I transfer data from SQL Server to Excel using ADO?

[RobDog888]

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:

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