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