Updated: May 28, 2022
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.
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.
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.