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
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.