How to Call a SQL Server Stored Procedure from Excel VBA

Data on a server can be modified by examining the records ‘client-side’ in Excel VBA, changing them as required, and saving them back to the server.
A more efficient way of doing this, particularly if the database is at a remote location and there’s a lot of traffic involved, is to do the work ‘server-side’. This exercise calls a stored procedure from Excel to categorise employees into age-ranges according to their dates of birth (i.e. 18-25 years, 26-35 years, etc.), without a copious exchange of data between the server and Excel.

This article assumes the reader has the Developer ribbon displayed and is familiar with the VBA Editor. If not, please Google “Excel Developer Tab” or “Excel Code Window”.

There are three elements to the exercise:

• A data table tblStaff within a database TestDB;
• A stored procedure spAgeRange;
• An Excel xlsm, which we’ll call xlsm. A sample Excel file can be found here

Data Table

Create a database in SQL Server called DBTest.

Set up the following columns for a table tblStaff.

Copy the following into the table:

2017/05/25	1	Brown	J	1946/12/02			M
2017/05/25	2	Smart	A	1976/03/26			F
2017/05/25	3	Cruise	T	1962/07/03			M
2017/05/25	4	Lohan	L	1986/07/02			F
2017/05/25	5	Fredricksen	F	1964/03/15			M
2017/05/25	6	Snyder	L	1968/07/05			F
2017/05/25	7	Lipnicki	J	1983/11/25			M
2017/05/25	8	Hoover	S	2002/12/08			F
2017/05/25	9	Watson	E	1990/04/15			F

Stored Procedure.

Run this script against TestDB to create the stored procedure:

USE [TestDB]
GO
/****** Object: StoredProcedure [dbo].[spAgeRange] Script Date: 2017/05/10 12:16:28 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[spAgeRange]

    @PayrollDate varchar(50)
 
AS
BEGIN

    SET NOCOUNT ON;
    UPDATE tblStaff SET Age = CONVERT(int, DATEDIFF(day, DateOfBirth, GETDATE()) / 365.25, 0) 
    WHERE tblStaff.PayrollDate = @PayrollDate
 
    Update tblStaff set AgeRange = '>56' where Age >= 56 and PayrollDate = @PayrollDate

    Update tblStaff set AgeRange = '46 to 55' where Age >= 46 and Age < 56 and PayrollDate = PayrollDate 
 
    Update tblStaff set AgeRange = '39 to 45' where Age >= 39 and Age < 46 and PayrollDate = @PayrollDate 
 
    Update tblStaff set AgeRange = '31 to 38' where Age >= 30 and Age < 39 and PayrollDate = @PayrollDate 
 
    Update tblStaff set AgeRange = '25 to 30' where Age >= 25 and Age < 30 and PayrollDate = @PayrollDate 
 
    Update tblStaff set AgeRange = '18 to 24' where Age >= 18 and Age < 25 and PayrollDate = @PayrollDate 
 
    Update tblStaff set AgeRange = '<18' where Age < 18 and PayrollDate = @PayrollDate

END

The stored procedure will saved under “Programmability” in the database.

Excel VBA

All that remains is to call the stored procedure from Excel, providing the PayrollDate parameter of “2017/05/25”. You will note I have simply data-typed  PayrollDate as a string rather than wrestle with varying date formats. It is simple enough to convert a string to a date using the Convert function if PayrollDate is to be used for arithmetic purposes.

Create a new workbook. Open the VBA code window and insert a module.

From the code window’s Tools menu, reference the appropriate Active X 2.nn library to facilitate use of data objects.

Paste the following code into the Code window. This, once activated, will connect to SQL Server, as per the ConnectDatabase sub procedure

'All "public" in case the code is spread over several modules.
Public connDB As New ADODB.Connection
Public rs As New ADODB.Recordset
Public strSQL As String
Public strConnectionstring As String
Public strServer As String
Public strDBase As String
Public strUser As String
Public strPwd As String
Public PayrollDate As String

Sub WriteStoredProcedure()
     PayrollDate = "2017/05/25"
     Call ConnectDatabase
     On Error GoTo errSP
     strSQL = "EXEC spAgeRange '" & PayrollDate & "'"
     connDB.Execute (strSQL)
     Exit Sub
errSP:
MsgBox Err.Description
End Sub

Sub ConnectDatabase()
     If connDB.State = 1 Then connDB.Close
     On Error GoTo ErrConnect
     strServer = "SERVERNAME" ‘The name or IP Address of the SQL Server
     strDBase = "TestDB"
     strUser = "" 'leave this blank for Windows authentication
     strPwd = ""

     If strPwd > "" Then
         strConnectionstring = "DRIVER={SQL Server};Server=" & strServer & ";Database=" & strDBase & ";Uid=" & strUser & ";Pwd=" & strPwd & ";Connection Timeout=30;"
     Else
         strConnectionstring = "DRIVER={SQL Server};SERVER=" & strServer & ";Trusted_Connection=yes;DATABASE=" &    strDBase 'Windows authentication
     End If
     connDB.ConnectionTimeout = 30
     connDB.Open strConnectionstring
Exit Sub
ErrConnect:
     MsgBox Err.Description
End Sub

Add a button to Sheet1 and assign it to sub procedure “WriteStoredProcedure”

The Results

Press the button, then  examine tblStaff, which should be updated with ages and age ranges. The processing has taken place server-side.

Recovering corrupted workbooks

Should Excel crash it might well take your only copy of the workbook down with it. A good percentage of the time Excel is often unable to recover damaged workbooks; in such a case, all  the work done since the creation of the workbook might be irrevocably lost, unless you have a tool to repair Excel xlsx or xlsm files.

Author Introduction:

Felix Hooker is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including rar repair and sql recovery software products. For more information visit www.datanumen.com