--- description: This exercise calls a stored procedure from Excel to categorise employees into age-ranges according to their dates of birth. title: How to Call a SQL Server Stored Procedure from Excel VBA image: https://www.datanumen.com/blogs/wp-content/uploads/2017/07/set-up-the-columns-for-a-table-tblStaff.jpg --- - - - [Home](https://www.datanumen.com/blogs/) > [Solutions](https://www.datanumen.com/blogs/category/solutions/) > [Office Solutions](https://www.datanumen.com/blogs/category/solutions/office-solutions/) > [Excel Solutions](https://www.datanumen.com/blogs/category/solutions/office-solutions/excel-solutions/) > How to Call a SQL Server Stored Procedure from Excel VBA # How to Call a SQL Server Stored Procedure from Excel VBA [Excel Solutions](https://www.datanumen.com/blogs/category/solutions/office-solutions/excel-solutions/) October 3, 2025 Share Now: - - - - - - - - *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](https://www.datanumen.com/blogs/wp-content/uploads/2017/07/Calling-a-SQL-Server-Stored-Procedure-from-Excel-VBA.xlsm) ## Data Table Create a database in SQL Server called *DBTest*. Set up the following columns for a table *tblStaff*. ![Set Up The Columns For A Table tblStaff](https://www.datanumen.com/blogs/wp-content/uploads/2017/07/set-up-the-columns-for-a-table-tblStaff.jpg "Set Up The 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.![Reference The Appropriate Active X 2.nn Library To Facilitate Use Of Data Objects]( "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](https://www.datanumen.com/excel-repair/) 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](https://www.datanumen.com/rar-repair/) and sql recovery software products. For more information visit [www.datanumen.com](https://www.datanumen.com/) Share Now: - - - - - - - - [← Previous post](https://www.datanumen.com/blogs/quickly-resend-meeting-invitation-havent-responded-outlook/) [Next post →](https://www.datanumen.com/blogs/2-methods-auto-set-status-day-event-busy-outlook/) Comments are closed. ![Data Recovery Blog]() Follow or like us on Facebook, LinkedIn and Twitter to get all promotions, latest news and updates on our products and company. - - - ### Featured Products - [DataNumen Outlook Repair](https://www.datanumen.com/outlook-repair/) - [DataNumen SQL Recovery](https://www.datanumen.com/sql-recovery/) - [DataNumen Exchange Recovery](https://www.datanumen.com/exchange-recovery/) - [DataNumen Access Repair](https://www.datanumen.com/access-repair/) - [DataNumen Excel Repair](https://www.datanumen.com/excel-repair/) - [DataNumen PDF Repair](https://www.datanumen.com/pdf-repair/) - [DataNumen Word Repair](https://www.datanumen.com/word-repair/) - [DataNumen Zip Repair](https://www.datanumen.com/zip-repair/) ### Popular posts ```json {"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.datanumen.com\/blogs\/call-sql-server-stored-procedure-excel-vba\/#article","isPartOf":{"@id":"https:\/\/www.datanumen.com\/blogs\/call-sql-server-stored-procedure-excel-vba\/"},"author":{"name":"AuthorCCW","@id":"https:\/\/www.datanumen.com\/blogs\/#\/schema\/person\/33ded6c47c77587f9158d99f05e23957"},"headline":"How to Call a SQL Server Stored Procedure from Excel VBA","datePublished":"2017-07-28T06:02:50+00:00","dateModified":"2025-10-03T09:23:36+00:00","mainEntityOfPage":{"@id":"https:\/\/www.datanumen.com\/blogs\/call-sql-server-stored-procedure-excel-vba\/"},"wordCount":490,"commentCount":1,"publisher":{"@id":"https:\/\/www.datanumen.com\/blogs\/#organization"},"image":{"@id":"https:\/\/www.datanumen.com\/blogs\/call-sql-server-stored-procedure-excel-vba\/#primaryimage"},"thumbnailUrl":"https:\/\/www.datanumen.com\/blogs\/wp-content\/uploads\/2017\/07\/set-up-the-columns-for-a-table-tblStaff.jpg","articleSection":["Excel Solutions"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.datanumen.com\/blogs\/call-sql-server-stored-procedure-excel-vba\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.datanumen.com\/blogs\/call-sql-server-stored-procedure-excel-vba\/","url":"https:\/\/www.datanumen.com\/blogs\/call-sql-server-stored-procedure-excel-vba\/","name":"How to Call a SQL Server Stored Procedure from Excel VBA","isPartOf":{"@id":"https:\/\/www.datanumen.com\/blogs\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.datanumen.com\/blogs\/call-sql-server-stored-procedure-excel-vba\/#primaryimage"},"image":{"@id":"https:\/\/www.datanumen.com\/blogs\/call-sql-server-stored-procedure-excel-vba\/#primaryimage"},"thumbnailUrl":"https:\/\/www.datanumen.com\/blogs\/wp-content\/uploads\/2017\/07\/set-up-the-columns-for-a-table-tblStaff.jpg","datePublished":"2017-07-28T06:02:50+00:00","dateModified":"2025-10-03T09:23:36+00:00","description":"This exercise calls a stored procedure from Excel to categorise employees into age-ranges according to their dates of birth.","inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.datanumen.com\/blogs\/call-sql-server-stored-procedure-excel-vba\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.datanumen.com\/blogs\/call-sql-server-stored-procedure-excel-vba\/#primaryimage","url":"https:\/\/www.datanumen.com\/blogs\/wp-content\/uploads\/2017\/07\/set-up-the-columns-for-a-table-tblStaff.jpg","contentUrl":"https:\/\/www.datanumen.com\/blogs\/wp-content\/uploads\/2017\/07\/set-up-the-columns-for-a-table-tblStaff.jpg","width":390,"height":217,"caption":"Set Up The Columns For A Table tblStaff"},{"@type":"WebSite","@id":"https:\/\/www.datanumen.com\/blogs\/#website","url":"https:\/\/www.datanumen.com\/blogs\/","name":"Data Recovery Blog","description":"Discuss every aspect of data recovery","publisher":{"@id":"https:\/\/www.datanumen.com\/blogs\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.datanumen.com\/blogs\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.datanumen.com\/blogs\/#organization","name":"Data Recovery Blog","url":"https:\/\/www.datanumen.com\/blogs\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.datanumen.com\/blogs\/#\/schema\/logo\/image\/","url":"https:\/\/www.datanumen.com\/blogs\/wp-content\/uploads\/2019\/02\/logowithouttext.jpg","contentUrl":"https:\/\/www.datanumen.com\/blogs\/wp-content\/uploads\/2019\/02\/logowithouttext.jpg","width":296,"height":397,"caption":"Data Recovery Blog"},"image":{"@id":"https:\/\/www.datanumen.com\/blogs\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/DataNumen","https:\/\/x.com\/DataNumen","http:\/\/www.linkedin.com\/company\/datanumen","https:\/\/myspace.com\/datanumen\/","http:\/\/www.pinterest.com\/datanumen\/","https:\/\/www.youtube.com\/channel\/UCVIIuuKfUBHSJauUaVP13tw"]},{"@type":"Person","@id":"https:\/\/www.datanumen.com\/blogs\/#\/schema\/person\/33ded6c47c77587f9158d99f05e23957","name":"AuthorCCW","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/3516ed90fc242780d3947a989f895bc46e70c3e83485d30f2b457a007906aa58?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/3516ed90fc242780d3947a989f895bc46e70c3e83485d30f2b457a007906aa58?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/3516ed90fc242780d3947a989f895bc46e70c3e83485d30f2b457a007906aa58?s=96&d=mm&r=g","caption":"AuthorCCW"},"sameAs":["https:\/\/www.datanumen.com\/"]}]} ```