Сервердегі деректерді Excel VBA-дағы «клиенттік» жазбаларды зерттеп, оларды қажетіне қарай өзгерту және оларды серверге сақтау арқылы өзгертуге болады.
Мұны жасаудың неғұрлым тиімді тәсілі, егер мәліметтер базасы қашықта болса және трафик көп болса, «сервер жағында» жұмыс жасау керек. Бұл жаттығу Excel-де сақталған процедураны шақырады, қызметкерлерді олардың туған күндеріне қарай жас шамаларына бөлу (яғни 18-25 жас, 26-35 жас және т.б.), сервер мен Excel арасында мол деректер алмасусыз.
Бұл мақала оқырманға Әзірлеуші лентасы көрсетілген және VBA редакторымен таныс деп болжайды. Олай болмаса, Google-дің «Excel Developer Tab» немесе «Excel Code Window» сұраймыз.
Жаттығудың үш элементі бар:
- Мәліметтер кестесі tblStaff деректер базасында TestDB;
- Сақталған процедура spAgeRange;
- Біз шақыратын Excel xlsm xlsm. Excel файлының үлгісін табуға болады Мұнда
Мәліметтер кестесі
Деректер базасын жасаңыз SQL Server деп аталатын DBTest.
Кесте үшін келесі бағандарды орнатыңыз tblStaff.

Келесілерді кестеге көшіріңіз:
| 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 | Гувер | 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» параметрі. Мен жай ғана мәліметтер тергенімді ескересіз Жалақы күні әртүрлі күн форматтарымен күресуден гөрі жіп ретінде. Көмегімен жолды күнге айналдыру өте қарапайым Түрлендіру функция, егер Жалақы күні арифметикалық мақсаттарда қолданылуы керек.
Жаңа жұмыс кітабын жасаңыз. 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
