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

Please see the following code for Importing data with SQL.

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

Code

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.
Range("A4:Z100000").Clear  
​
'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;" & _
              "Trusted_connection=yes;"
​
'Provider = Fix variable.
'Data Source = Type in your servername.
'Initial Catalog = Type in your database name.
​
'2 options of connecting to the server.
'1)
    '"Trusted_connection=yes;" = This refers to Windows authentication setting in your database.
'2)
    '"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
     Next
 
     'Pasting in the adodb recordset table to our worksheet.
     ActiveSheet.Range("A5").CopyFromRecordset rs
 
     ' Close the recordset
     rs.Close
 
 
     'Create an excel table of our data. 
     'Not necessary for the import but looks better.
     EndPlace =
     Range("A4").End(xlDown).End(xlToRight).Address(True, 
     True)
    
     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")
 
Else
     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.

SQL_Import

.zip

Download ZIP • 74KB

Learn more about VBA here for all my posts: https://www.pls-fix-thx.com/vba

Learn more about Python here for all my posts: https://www.pls-fix-thx.com/python

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

Coffee Mug - Money Green

Buy Now