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.

ExcelVBA01 ExcelVBA02

Name “Sheet1” as “Data” and Create a second Sheet called “Request”

ExcelVBA09

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

ExcelVBA04

Once you have the Developer Ribbon Select it and then Select “Visual Basic”

ExcelVBA05

This opens the VB, Select Tools -> References

ExcelVBA06

Scroll down and tick the latest “Microsoft ActiveX Data Objects” at present this is the 6.1 Library. and press OK.

ExcelVBA07

Right click on the “Microsoft Excel Objects” Select “insert” -> “Module” ExcelVBA08

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

ExcelVBA15

Go to the “Request” sheet and create a button via: Select Insert Tab -> Recently Used Shapes. Size it as needed.

ExcelVBA10

Right Click on the Button and “Assign MAcro…”

ExcelVBA11

Select “GetDataFromADO” and press OK.

ExcelVBA12

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.

ExcelVBA13

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.

ExcelVBA14

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.

Advertisements