top of page

How to Import a SQL table to excel with "adodb recordset vba"?

Please see the following code for Importing data with SQL.

adodb recordset vba

Step 1 before inserting this code into you VBE (Alt + F11) is to enable the library reference for Outlook so that you have access to Outlook classes for the below code.

Press (Alt + F11) keys, Go to Tools, References, and make sure "Microsoft ActiveX Data Objects 6.1 Library". if you have an earlier or later version than 6.1 that should be fine as well.

How my worksheet looks like

export sql table to excel


We are using the adodb connection to set up a recordset to import the sql data into.

Sub ConnectSqlServer()
'Turns off screenupdating for faster script.
Application.ScreenUpdating = False 
'Clears old data.

'Imports the "SQL" class and library references called ActiveX Data Objects Library.
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sConnString As String
' Create the connection string to our server.
sConnString = "Provider=SQLOLEDB;" & _
              "Data Source=Servername;" & _
              "Initial Catalog=Database;" & _
'Provider = Fix variable.
'Data Source = Type in your servername.
'Initial Catalog = Type in your database name.
'2 options of connecting to the server.
    '"Trusted_connection=yes;" = This refers to Windows authentication setting in your database.
    '"User Id=myUsername;Password=myPassword;" = If you are not using Windows authentication, you need to specify your username and password.

' Create the Connection and Recordset objects.
Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset
'Setting the name of my SQL table.
MyTable = "TestTable" 'Change this to your SQL table name.'Create SQL string
Dim SQLstring As String
'This is your SQL string. same as you write in SQL for selecting all of your data.
SQLstring = "SELECT * FROM " & MyTable & ";" 
' Open the connection and execute.
conn.Open sConnString
Set rs = conn.Execute(SQLstring)

'********Check if we have data in our "TestTable".**********
If Not rs.EOF Then
    'We have data.
    'Importing our column names to our worksheet.
     For iCols = 0 To rs.Fields.Count - 1
         ActiveSheet.Cells(4, iCols + 1).Value 
         = rs.Fields(iCols).Name
     'Pasting in the adodb recordset table to our worksheet.
     ActiveSheet.Range("A5").CopyFromRecordset rs
     ' Close the recordset
     'Create an excel table of our data. 
     'Not necessary for the import but looks better.
     EndPlace =
     Application.CutCopyMode = False
     ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$4:" 
     & EndPlace), xlYes).Name = "SQL_Import"
     'Prepare the message box to the user.
     MsgBox ("Data has been collected")
     MsgBox "Error: No records returned.", vbCritical
End If
'******END - Check if we have data in our "TestTable".********

' Clean up, closing the serverconnection.
If CBool(conn.State And adStateOpen) Then conn.Close
Set conn = Nothing
Set rs = Nothing
'Turns on screenupdating now that we are done.
Application.ScreenUpdating = True 

MsgBox ("Done")

End Sub

Download Excel Workbook here.

Feel free to download my prepared workbook containing this exact code to get a jumpstart on your project.

Download ZIP • 74KB

Learn more about VBA here for all my posts:

Learn more about Python here for all my posts:

If you have found this article or website helpful. Please show your support by visiting the shop below.

816 views0 comments

Recent Posts

See All


bottom of page