Import data from SQL Server to Excel using VBA

Excel is a great tools for reporting, many users prefer data can be provided in Excel format, so that they can further manipulate it. Here is an simple example demonstrating how to import the result set of a SQL Server stored procedure into Excel using VBA code.

Sub DataExtract()
    ' Create a connection object.
    Dim conn As ADODB.Connection
    Set conn = New ADODB.Connection

    ' Provide the connection string.
    Dim strConn As String

    ' Use the SQL Server OLE DB Provider.
    strConn = "PROVIDER=SQLOLEDB;"
    ' Connect to the database.
    strConn = strConn & "DATA SOURCE=YourSqlServer;INITIAL CATALOG=YourDB;"

    ' Open the connection.
    conn.Open strConn, "YourSqlAccount", "YourSqlPassword"

    ' Create a recordset object.
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset

    With rs
        ' Assign the Connection object.
        .ActiveConnection = conn
        ' Extract the required records by a select statement or execute a stored proc.
        .Open "EXEC YourStoredProc;"
        ' Print field headers on 1st row
        For i = 1 To .Fields.Count
            Cells(1, i).Value = .Fields(i - 1).Name
        Next i
        ' Copy the records into cell A2 on Sheet1.
        Worksheets("Sheet1").Range("A2").CopyFromRecordset rs
        ' Tidy up
        .Close
    End With

    conn.Close
    Set rs = Nothing
    Set conn = Nothing
End Sub