MSSQL – Export data from SQL Server to Excel with VBA

Posted on December 21, 2015 by

So, after importing data to SQL Server from Excel now it is time for the reversing policy. Actually, much of the code was available in the Microsoft site here, but still, it was not all that I needed. I wanted to make it a little nicer, with headers.

Thus for the headers, it was really a 15 minute challenge, until I found the way to take them and write them at the excel sheet. Thus, at the end I had something workable.

Let’s see the code here:

Option Explicit

'The part extracting the body is taken from here
'https://support.microsoft.com/en-us/kb/306125

Sub GetData()
    
    Dim cnLogs              As New ADODB.Connection
    Dim rsHeaders           As New ADODB.Recordset
    Dim rsData              As New ADODB.Recordset
    
    Dim l_counter           As Long: l_counter = 0
    Dim strConn             As String
    
    Sheets(1).UsedRange.Clear
    strConn = "PROVIDER=SQLOLEDB;"
    strConn = strConn & "DATA SOURCE=(local);INITIAL CATALOG=LogData;"
    strConn = strConn & " INTEGRATED SECURITY=sspi;"
    
    cnLogs.Open strConn
    
    With rsHeaders
        .ActiveConnection = cnLogs
        
        .Open "SELECT * FROM syscolumns WHERE id=OBJECT_ID('LogTable')"
        '.Open "SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'LogTable'"
        '.Open "SELECT * FROM LogData.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'LogTable'"
        '.Open "SELECT * FROM SYS.COLUMNS WHERE object_id = OBJECT_ID('dbo.LogTable')"
        
        Do While Not rsHeaders.EOF
            Cells(1, l_counter + 1) = rsHeaders(0)
            l_counter = l_counter + 1
            rsHeaders.MoveNext
        Loop
        .Close
    End With

    With rsData
        .ActiveConnection = cnLogs
        .Open "SELECT * FROM LogTable"
        Sheet1.Range("A2").CopyFromRecordset rsData
        .Close
    End With
    
    cnLogs.Close
    Set cnLogs = Nothing
    Set rsHeaders = Nothing
    Set rsData = Nothing
    
    Sheets(1).UsedRange.EntireColumn.AutoFit

End Sub

Option Explicit

'The part extracting the body is taken from here

'https://support.microsoft.com/en-us/kb/306125

Sub GetData()

Dim cnLogs As New ADODB.Connection

Dim rsHeaders As New ADODB.Recordset

Dim rsData As New ADODB.Recordset

Dim l_counter As Long: l_counter = 0

Dim strConn As String

Sheets(1).UsedRange.Clear

strConn = "PROVIDER=SQLOLEDB;"

strConn = strConn & "DATA SOURCE=(local);INITIAL CATALOG=LogData;"

strConn = strConn & " INTEGRATED SECURITY=sspi;"

cnLogs.Open strConn

With rsHeaders

.ActiveConnection = cnLogs

.Open "SELECT * FROM syscolumns WHERE id=OBJECT_ID('LogTable')"

'.Open "SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'LogTable'"

'.Open "SELECT * FROM LogData.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'LogTable'"

'.Open "SELECT * FROM SYS.COLUMNS WHERE object_id = OBJECT_ID('dbo.LogTable')"

Do While Not rsHeaders.EOF

Cells(1, l_counter + 1) = rsHeaders(0)

l_counter = l_counter + 1

rsHeaders.MoveNext

Loop

.Close

End With

With rsData

.ActiveConnection = cnLogs

.Open "SELECT * FROM LogTable"

Sheet1.Range("A2").CopyFromRecordset rsData

.Close

End With

cnLogs.Close

Set cnLogs = Nothing

Set rsHeaders = Nothing

Set rsData = Nothing

Sheets(1).UsedRange.EntireColumn.AutoFit

End Sub

What the code does? Pretty much it deletes anything you have in the active sheet, then writes the headers through a separate recordset called rdHeaders. Then it writes the rest table. As far as I am using early binding, the code would not work, if you do not add MS ActiveX Data Objects 2.8 Library to the References from Tools>References in the Visual Basic Editor. The differences between the early and the late biding is explained here.

The code is also available in GitHub here.

That’s all! Enjoy it! 🙂