Как да се обадя a SQL Server Съхранена процедура от Excel VBA

Данните на сървър могат да бъдат модифицирани, като се изследват записите „от страна на клиента“ в Excel VBA, като се променят според нуждите и се запазват обратно на сървъра.
По-ефективен начин за това, особено ако базата данни е на отдалечено място и има много трафик, е да се свърши работата „от страна на сървъра“. Това упражнение извиква съхранена процедура от Excel за категоризиране на служителите във възрастови диапазони според датите им на раждане (т.е. 18-25 години, 26-35 години и т.н.), без обилен обмен на данни между сървъра и Excel.

Тази статия предполага, че на читателя е показана лентата за програмисти и е запознат с редактора на VBA. Ако не, моля Google „Раздел за програмисти на Excel“ или „Прозорец на кода на Excel“.

Има три елемента на упражнението:

• Таблица с данни tbl Персонал в база данни TestDB;
• Съхранена процедура spAgeRange;
• Excel xlsm, който ще наречем xlsm. Може да се намери примерен файл на Excel тук

Таблица с данни

Създайте база данни в SQL Server нарича DBTest.

Настройте следните колони за таблица tbl Персонал.

Копирайте следното в таблицата:

Съхранена процедура.

Изпълнете този скрипт срещу TestDB, за да създадете съхранената процедура:

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

Съхранената процедура ще бъде запазена в „Програмируемост“ в базата данни.

Excel vba

Остава само да извикате съхранената процедура от Excel, като предоставите Дата на заплата параметър на „2017/05/25“. Ще забележите, че просто съм въвел данни  Дата на заплата като низ, а не като борба с различни формати на дати. Достатъчно просто е да преобразувате низ в дата с помощта на Превръщам функция ако Дата на заплата трябва да се използва за аритметични цели.

Създайте нова работна книга. Отворете прозореца на кода на VBA и поставете модул.

От менюто Инструменти на прозореца на кода се обърнете към съответния Библиотека Active X 2.nn за улесняване на използването на обекти от данни.

Поставете следния код в прозореца Код. Това, след като бъде активирано, ще се свърже с SQL Server, съгласно подпроцедурата ConnectDatabase

'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

Добавете бутон към Sheet1 и го присвойте на подпроцедура “WriteStoredProcedure"

Резултатите

Натиснете бутона, след това проверете tblStaff, който трябва да се актуализира с възрасти и възрастови диапазони. Обработката е извършена от страна на сървъра.

Възстановяване на повредени работни книги

Ако Excel се срине, е много вероятно да повлече със себе си и единственото ви копие на работната книга. В голям процент от случаите Excel често не е в състояние да възстанови повредени работни книги; в такъв случай цялата работа, извършена след създаването на работната книга, може да бъде безвъзвратно загубена, освен ако нямате инструмент за... ремонт на Excel xlsx или xlsm файлове.

Въведение на автора:

Феликс Хукър е експерт по възстановяване на данни в DataNumen, Inc., която е световен лидер в технологиите за възстановяване на данни, включително ремонт на RAR файлове и sql софтуерни продукти за възстановяване. За повече информация посетете WWW.datanumen.com