By Example – Using Excel VBA to Export SQL Data Series – 01
This is the first in a series of posts, where I’ll be building out from this first attempt of using VBA to load data into Excel from SQL Server. There are loads of ways to Export SQL Server data to Excel, but I am always on the hunt for better and easier ways and VBA seems a good option to explore!
Below are the steps to build a VBA Excel spreadsheet that connects to SQL Server data and loads the data into a worksheet:
Create a New “Blank workbook” and Save it as a “Excel Macro-Enabled Workbook (*.xlsm)”, name it what you like.
Name “Sheet1” as “Data” and Create a second Sheet called “Request”
Go to the Developer Tab, If you dont have a Developer tab go to File -> Options (this opens the Excel Options Tab) Select the Customize Ribbon – and Then Tick the Developer checkbox on the Customize the Ribbon Main Tabs” .
Once you have the Developer Ribbon Select it and then Select “Visual Basic”
This opens the VB, Select Tools -> References
Scroll down and tick the latest “Microsoft ActiveX Data Objects” at present this is the 6.1 Library. and press OK.
Copy and paste the below code into the Module.
NOTE: the Open Connection Section, this will need to be manually changed to your SQL Server, Database and Login details.
Sub GetDataFromADO() 'Input query' Dim dbQuery As String dbQuery = InputBox("Enter Query") 'Declare variables' Dim objMyConn As ADODB.Connection Dim objMyCmd As ADODB.Command Dim objMyRecordset As ADODB.Recordset Dim iCols As Integer Dim tbl As ListObject Set objMyConn = New ADODB.Connection Set objMyCmd = New ADODB.Command Set objMyRecordset = New ADODB.Recordset 'Open Connection' objMyConn.ConnectionString = "Provider=SQLOLEDB;Data Source=.;Initial Catalog=AdventureWorks2012;User ID=stephen;Password=Pa$$w0rd;" objMyConn.Open 'Set and Excecute SQL Command' Set objMyCmd.ActiveConnection = objMyConn objMyCmd.CommandText = dbQuery objMyCmd.CommandType = adCmdText 'Open Recordset' Set objMyRecordset.Source = objMyCmd objMyRecordset.Open For iCols = 0 To objMyRecordset.Fields.Count - 1 Worksheets("Data").Cells(1, iCols + 1).Value = objMyRecordset.Fields(iCols).Name Next 'Copy Data to Excel' Worksheets("Data").Range("A2").CopyFromRecordset objMyRecordset End Sub
Go to the “Request” sheet and create a button via: Select Insert Tab -> Recently Used Shapes. Size it as needed.
Right Click on the Button and “Assign MAcro…”
Select “GetDataFromADO” and press OK.
And there we go. We should be ready to go.
Go to the Request Tab and press the button. This pop up a new Window, Enter your Query here and press OK.
Depending on the size of the result set returns you might get a spinning wheel for a while, once its finished go to the Data Tab which should now be populated with your results.
and there we have it, Stage 1 of the series. I will be hoping to expand the VBA in later series by:
- Creating a user form to input the “Open Connection” settings as well as the query
- Either lock down the VBA code so its not readable without a password (Since we currently have Username and passwords in there!) or change it to Windows Auth.
- Make it repeatable by deleting all the data in the “Data” sheet before each query
- Format the results
- Any other fun things i can think of! or people suggest!
I hope you find this series fun and Im hoping to keep this updated regularly as i improve on it and clean things up.