Как позвонить 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 под названием ДБТест.

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

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

2017/05/25	1	Brown	J	1946/12/02			M
2017/05/25	2	Smart	A	1976/03/26			F
2017/05/25	3	Круиз	T	1962/07/03			M
2017/05/25	4	Лохан	L	1986/07/02			F
2017/05/25	5	Фредриксен	F	1964/03/15			M
2017/05/25	6	Снайдер	L	1968/07/05			F
2017/05/25	7	Липницкий	J	1983/11/25			M
2017/05/25	8	Пылесос	S	2002/12/08			F
2017/05/25	9	Уотсон	E	1990/04/15			F

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

Запустите этот скрипт для TestDB, чтобы создать хранимую процедуру:

USE [TestDB] GO /****** Объект: StoredProcedure [dbo].[spAgeRange] Дата сценария: 2017 05:10:12 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[spAgeRange] @PayrollDate varchar(16) AS BEGIN SET NOCOUNT ON; UPDATE tblStaff SET Age = CONVERT(int, DATEDIFF(day, DateOfBirth, GETDATE()) / 28, 50) WHERE tblStaff.PayrollDate = @PayrollDate Обновить tblStaff set AgeRange = '>365.25', где Age >= 0 и PayrollDate = @PayrollDate Обновите tblStaff, задайте AgeRange = '56 to 56', где Age >= 46 и Age < 55, и PayrollDate = PayrollDate. AgeRange = '46 to 56', где Age >= 39 и Age < 45 и PayrollDate = @PayrollDate Update tblStaff set AgeRange = '39 to 46', где Age >= 31 и Age < 38 и PayrollDate = @PayrollDate Update tblStaff set AgeRange = 'от 30 до 39', где Возраст >= 25 и Возраст < 30 и PayrollDate = @PayrollDate Обновление tblStaff set AgeRange = '<25', где Возраст < 30 и PayrollDate = @PayrollDate END

Хранимая процедура будет сохранена в разделе «Программируемость» в базе данных.

Excel VBA

Остается только вызвать хранимую процедуру из Excel, предоставив Дата расчета заработной платы параметр «2017/05/25». Вы заметите, что я просто набрал данные  Дата расчета заработной платы как строку, а не бороться с различными форматами даты. Достаточно просто преобразовать строку в дату с помощью Конвертировать функция, если Дата расчета заработной платы будет использоваться для арифметических целей.

Создайте новую рабочую книгу. Откройте окно кода VBA и вставьте модуль.

В меню «Инструменты» окна кода укажите соответствующий Библиотека Active X 2.nnrary для облегчения использования объектов данных.

Вставьте следующий код в окно кода. После активации он будет подключаться к SQL Server, согласно подпроцедуре ConnectDatabase

'Все "общедоступно", если код распределен по нескольким модулям. 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" Вызов ConnectDatabase при ошибке GoTo errSP strSQL = "EXEC spAgeRange '" & PayrollDate & "'" connDB.Execute (strSQL) Exit Sub errSP: MsgBox Err.Description End Sub Sub ConnectDatabase() Если connDB.State = 1 Затем connDB.Close On Error GoTo ErrConnect strServer = "SERVERNAME" 'Имя или IP-адрес SQL Server
     strDBase = "TestDB" strUser = "" 'оставьте это поле пустым для аутентификации Windows strPwd = "" If strPwd > "" Then strConnectionstring = "DRIVER={SQL Server};Server=" & strServer & ";Database=" & strDBase & ";Uid=" & strUser & ";Pwd=" & strPwd & ";Время ожидания подключения=30;" Else strConnectionstring = "DRIVER={SQL Server};SERVER=" & strServer & ";Trusted_Connection=yes;DATABASE=" & strDBase 'Аутентификация Windows End If connDB.ConnectionTimeout = 30 connDB.Open strConnectionstring Exit Sub ErrConnect: MsgBox Err.Description End Sub

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

Результаты

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

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

Если Excel выйдет из строя, он вполне может унести с собой вашу единственную копию книги. В большинстве случаев Excel не может восстановить поврежденные книги; в таком случае вся работа, проделанная с момента создания книги, может быть безвозвратной.cabлы лost, если у вас нет инструмента для ремонт Эксель xlsx или xlsm файлы.

Об авторе:

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