Хэрхэн дуудах вэ SQL Server Excel VBA-аас хадгалагдсан журам

Сервер дээрх өгөгдлийг Excel VBA дээрх 'клиент талын' бүртгэлийг шалгаж, шаардлагатай бол өөрчилж, сервер рүү буцааж хадгалах замаар өөрчлөх боломжтой.
Үүнийг хийх илүү үр дүнтэй арга бол, ялангуяа мэдээллийн бааз нь алслагдсан байршилд байгаа, ачаалал ихтэй байгаа тохиолдолд 'сервер талын' ажлыг хийх явдал юм. Энэхүү дасгал нь ажилчдыг төрсөн он сараар нь (өөрөөр хэлбэл 18-25 нас, 26-35 нас гэх мэт) насны ангиллаар ангилах, сервер болон Excel-ийн хооронд өгөгдөл солилцохгүйгээр Excel-ээс хадгалсан процедурыг дууддаг.

Энэ нийтлэл нь уншигчдад Developer туузыг харуулсан бөгөөд VBA редакторыг сайн мэддэг гэж үзнэ. Хэрэв үгүй ​​бол Google-ийн “Excel Developer Tab” эсвэл “Excel Code Window” -ийг ашиглана уу.

Дасгалын гурван элемент байдаг.

• Мэдээллийн хүснэгт tblStaff мэдээллийн бааз дотор Туршилтын DB;
• Хадгалагдсан процедур spAgeRange;
• Бидний дуудах Excel xlsm xlsm. Excel файлын жишээг олж болно энд

Мэдээллийн хүснэгт

Мэдээллийн сан үүсгэх SQL Server гэж нэрлэдэг DBTest.

Хүснэгтэнд зориулж дараах багануудыг тохируулна уу tblStaff.

Дараахь зүйлийг хүснэгтэд хуулж ав.

Хадгалагдсан журам.

Хадгалагдсан процедурыг үүсгэхийн тулд энэ скриптийг 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

Хадгалагдсан процедурыг мэдээллийн санд "Programmability" хэсэгт хадгалах болно.

Excel VBA

Excel-ээс хадгалагдсан процедурыг дуудах л үлдэх болно Цалингийн огноо “2017/05/25” -ын параметр. Надад зүгээр л өгөгдөл шивсэн байгааг тэмдэглэх болно  Цалингийн огноо янз бүрийн огнооны форматтай барилдахаас илүү мөр. Ашиглан мөрийг огноо болгон хөрвүүлэхэд хангалттай энгийн Хөрвүүлэх хэрэв функц Цалингийн огноо нь арифметикийн зорилгоор ашиглагдах болно.

Шинэ ажлын дэвтэр үүсгэх. VBA кодын цонхыг нээгээд модуль оруулна уу.

Кодын цонхны Tools цэсээс тохирохыг нь лавлах хэрэгтэй Идэвхтэй 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

Sheet1 дээр товчлуур нэмж, дэд процедурт оноож өг.WriteStoredProcedure"

Үр дүн

Товчлуурыг дараад tblStaff-ийг шалгаж, нас, насны хязгаараар шинэчлэх хэрэгтэй. Боловсруулалт нь серверийн тал дээр явагдсан.

Гэмтсэн ажлын номыг сэргээх

Хэрэв Excel уналтанд орсон бол ажлын дэвтэр дээрх цорын ганц хуулбарыг авч хаях хэрэгтэй. Excel-ийн ихэнх хугацааны эвдэрсэн ажлын номыг сэргээх боломжгүй байдаг. ийм тохиолдолд ажлын номыг бүтээснээс хойш хийсэн бүх ажил нь irrevo байж болох юмcably лost, хэрэв танд хэрэгсэл байхгүй бол Excel-ийг засах xlsx эсвэл xlsm файлууд.

Зохиогчийн танилцуулга:

Феликс Хүүкер бол мэдээлэл сэргээх мэргэжилтэн юм DataNumen, Үүнд мэдээлэл сэргээх технологиор дэлхийд тэргүүлэгч, Inc. rar засвар болон sql сэргээх програм хангамжийн бүтээгдэхүүнүүд. Дэлгэрэнгүй мэдээллийг авна уу WWW.datanumen.com