Как позвонить SQL Server Хранимая процедура из Excel VBA

Данные на сервере можно изменить, изучив записи «на стороне клиента» в Excel VBA, изменив их по мере необходимости и сохранив их обратно на сервер.
Более эффективный способ сделать это, особенно если база данных находится в удаленном месте и задействован большой объем трафика, — выполнять работу «на стороне сервера». В этом упражнении вызывается хранимая процедура из Excel для категоризации сотрудников по возрастным группам в соответствии с датами их рождения (например, 18–25 лет, 26–35 лет и т. д.) без обильного обмена данными между сервером и Excel.

В этой статье предполагается, что у читателя отображается лента «Разработчик» и он знаком с редактором VBA. Если нет, погуглите «Excel Developer Tab» или «Excel Code Window».

Упражнение состоит из трех элементов:

• Таблица данных столперсонал в базе данных ТестБД;
• Хранимая процедура SpAgeRange;
• Excel xlsm, который мы будем называть XLSM. Образец файла Excel можно найти здесь

Таблица технических данных

Создайте базу данных в SQL Server которые называются ДБТест.

Настройте следующие столбцы для таблицы столперсонал.

Скопируйте в таблицу следующее:

Хранимая процедура.

Запустите этот скрипт для 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 library для облегчения использования объектов данных.

Вставьте следующий код в окно кода. После активации он будет подключаться к 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

Добавьте кнопку на Лист1 и назначьте ее подпроцедуре «WriteStoredProcedure

Результаты

Нажмите кнопку, затем просмотрите tblStaff, который должен быть обновлен с указанием возраста и возрастных диапазонов. Обработка произошла на стороне сервера.

Восстановление поврежденных книг

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 ремонт Эксель xlsx или xlsm файлы.

Об авторе:

Феликс Хукер — эксперт по восстановлению данных в DataNumen, Inc., которая является мировым лидером в области технологий восстановления данных, включая ремонт rar и программные продукты для восстановления sql. Для получения дополнительной информации посетите www.datanumen.com