Дані на сервері можна змінити, перевіривши записи на стороні клієнта в Excel VBA, змінивши їх за необхідності та збереживши назад на сервері.
Більш ефективний спосіб зробити це, особливо якщо база даних знаходиться у віддаленому місці і залучено багато трафіку, - це виконувати роботу на стороні сервера. Ця вправа вимагає збереженої процедури з Excel для класифікації працівників за віковими діапазонами відповідно до їх дат народження (тобто 18-25 років, 26-35 років тощо), без рясного обміну даними між сервером та Excel.
У цій статті передбачається, що на пристрої для читання відображається стрічка розробника та він знайомий з редактором VBA. Якщо ні, будь ласка, перегляньте Google “Вкладка розробника Excel” або “Вікно коду Excel”.
У вправі є три елементи:
- Таблиця даних tblСтаф в базі даних TestDB;
- Збережена процедура spAgeRange;
- Excel xlsm, який ми будемо називати xlsm. Зразок файлу Excel можна знайти тут
Таблиця даних
Створити базу даних у SQL Server званий DBTest.
Налаштуйте наступні стовпці для таблиці tblСтаф.

Скопіюйте в таблицю:
| 2017/05/25 | 1 | коричневий | 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 | Hoover | S | 2002/12/08 | F | ||
| 2017/05/25 | 9 | Уотсон | E | 1990/04/15 | F |
Зберігається процедура.
Запустіть цей скрипт проти 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”. Ви зауважите, я просто набрав дані Дата виплати заробітної плати як струна, а не боротьба з різними форматами дат. Досить просто перетворити рядок на дату за допомогою Конвертувати функція if Дата виплати заробітної плати має використовуватися для арифметичних цілей.
Створіть нову книгу. Відкрийте вікно коду 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
Додайте кнопку до Аркуша1 і призначте її підпроцедурі “WriteStoredProcedure"
Результати
Натисніть кнопку, а потім вивчіть tblStaff, який слід оновити за віком та віковим діапазоном. Обробка відбулася на стороні сервера.
Відновлення пошкоджених робочих книжок
Якщо Excel виходить з ладу, він цілком може пошкодити разом із собою вашу єдину копію книги. У значному відсотку випадків Excel не може відновити пошкоджені книги; у такому випадку вся робота, виконана з моменту створення книги, може бути безповоротно втрачена, якщо у вас немає інструменту для цього. ремонт Excel файли xlsx або xlsm.
Вступ автора:
Фелікс Хукер - фахівець з відновлення даних у DataNumen, Inc., яка є світовим лідером у галузі технологій відновлення даних, в тому числі ремонт rar-файлів та програмні продукти для відновлення sql. Для отримання додаткової інформації відвідайте WWW.datanumen.com
