I’ve been making a lot of reports and dashboards combining my love of SQL and Excel lately, so I’ve put together a neat guide of fully customizable, ultra-commented VBA code to show you how to make a VBA Macro do your SQL bidding. Check it out, and let me know what you think in the comments here or on Twitter! I’m happy to help if you have any questions at all.
Open a new Excel workbook and save it as a Macro-Enabled workbook (make sure there aren’t any spaces in the name!
Press ALT + F11 to open the VBA Editor and paste in the below code in full:
(The comments in the code will let you know what to do next!)
' | Brooke Clarke / girlvsdata.com / Oct 2017 |
' | |
' | ** ¯\_(^-^)_/¯ ** |
'Create the variables
' (The first two: ADODB.Connection & ADODB.Recordset are from the Microsoft ActiveX Data Objects (ADO) library.
' To use them, first make sure you have the ADO library setup in Excel:
' - In the VBA editor (the screen you are inputting your VBA code) go to Tools > References
' - Click to select the most recent version of the Microsoft ActiveX Data Objects Library check box.)
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim SQL As String
Dim dvr As String
Dim svr As String
Dim db As String
Dim wb As Workbook
Dim ws As Worksheet
Dim rng As String
'Assign strings to variables
'Workbook - What is your workbook called? (.xls is standard Excel workbook, .xlsm is Macro Enabled)
Set wb = Workbooks("YOUR_WORKBOOK_NAME_HERE.xls")
'Worksheet - Which sheet did you want your query to be pasted into?
'(Set to Sheet1 as default)
Set ws = wb.Worksheets("Sheet1")
'Driver (default is SQL Server, this should work for most cases:
dvr = "SQL Server"
'Server - Your server connection string:
svr = "YOUR_SERVER_HERE"
'Database - Your database connection string:
db = "YOUR_DATABASE_HERE"
'This code creates a new database connection and assigns your server connection through the ConnectionString
Set cnn = New ADODB.Connection
cnn.ConnectionString = "DRIVER=" & dvr & ";SERVER=" & svr & ";DATABASE=" & db & ";Trusted_Connection=Yes"
On Error GoTo SQL_ConnectionError
'Opens the connection
On Error GoTo 0
'Creates a new recordset to hold the SQL query in
Set rst = New ADODB.Recordset
'Assigns the SQL code to a variable
'(NOTE: Before you change the SQL Query:
' - I'd highly recommend running the query as is and making sure you don't have any connection errors before you change it.
' It's much easier to troubleshoot connection errors when you are only pulling a single integer!
' - The current query shows the number pulled in a pop-up message. If you are pulling more than a single integer,
' head on over to step (8) and switch between code (a) and code (b) by commenting/uncommenting the codes
' - Code (a) (the default) will show the integer in a pop-up message
' - Code (b) will paste the results of the query (without titles) in cell A1)
'SQL Query goes here:
SQL = "set nocount on; select 1"
'Runs the SQL query
rst.ActiveConnection = cnn
On Error GoTo SQL_StatementError
On Error GoTo 0
'This is what happens when the query runs.
'Instead of the first messagebox, you could paste this query into a cell on the worksheet.
If Not rst.EOF And Not rst.BOF Then
'(If the query contains at least one record, then):
'-*(a)*- (if using code (b), comment this code, uncomment code (b))
'Show a messagebox of the result of the query:
MsgBox "Connection worked. Server returned: " & rst.Fields(0).Value, , "Success! ¯\_('-^)-\_ "
'-*(b)*- (if using code (a), comment this code, uncomment code (a))
'Paste the query result into cell A1
' rng = "A1"
' ws.Range(rng).CopyFromRecordset rst
' MsgBox "Connection worked. SQL Query pasted into " & rng, , "Success! ¯\_('-^)-\_ "
'(If the query contains no records):
MsgBox "Connection worked. The server did not return any value.", , "Error"
'(What is EOF and BOF?
' - If you open a Recordset object containing no records, the BOF and EOF properties are set to True
' - When you open a Recordset object that contains at least one record, the first record is the current record and
' the BOF and EOF properties are False.
' - The BOF property returns True (-1) if the current record position is before the first record and
' False (0) if the current record position is on or after the first record. +
' - The EOF property returns True if the current record position is after the last record and
' False if the current record position is on or before the last record.)
'These are the possible error Messages:
MsgBox "Problems connecting to the server." & Chr(10) & "Aborting...", , "Error"
MsgBox "Connection established. Yet, there is a problem with the SQL syntax." & Chr(10) & "Aborting...", , "Error"
- This question from Stack Overflow: user Ralph posted a very helpful answer with some excellent VBA syntax I was able to build my own Macro from. Full credit to them for the base of this code.
- I used Microsoft’s ActiveX Data Objects help page here to find out how to setup the ADO references library in my copy of Excel.
- EOF and BOF definitions are from here.