làm thế nào để gọi một SQL Server Thủ tục lưu trữ từ Excel VBA

Dữ liệu trên máy chủ có thể được sửa đổi bằng cách kiểm tra các bản ghi 'phía máy khách' trong Excel VBA, thay đổi chúng theo yêu cầu và lưu chúng trở lại máy chủ.
Một cách hiệu quả hơn để thực hiện việc này, đặc biệt nếu cơ sở dữ liệu ở một vị trí xa và có nhiều lưu lượng tham gia, là thực hiện công việc 'phía máy chủ'. Bài tập này gọi một thủ tục được lưu trữ từ Excel để phân loại nhân viên thành các độ tuổi theo ngày sinh của họ (tức là 18-25 tuổi, 26-35 tuổi, v.v.) mà không cần trao đổi nhiều dữ liệu giữa máy chủ và Excel.

Bài viết này giả định rằng người đọc đã hiển thị dải băng Nhà phát triển và quen thuộc với Trình soạn thảo VBA. Nếu không, hãy Google “Excel Developer Tab” hoặc “Excel Code Window”.

Có ba yếu tố cho bài tập:

• Một bảng dữ liệu tblNhân viên trong một cơ sở dữ liệu Kiểm traDB;
• Một thủ tục được lưu trữ phạm vi spAge;
• Một xlsm Excel, mà chúng ta sẽ gọi xlsm. Một tệp Excel mẫu có thể được tìm thấy đây

Bảng dữ liệu

Tạo cơ sở dữ liệu trong SQL Server gọi là DBTest.

Thiết lập các cột sau cho một bảng tblNhân viên.

Sao chép nội dung sau vào bảng:

Thủ tục lưu trữ.

Chạy tập lệnh này với TestDB để tạo thủ tục được lưu trữ:

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

Quy trình được lưu trữ sẽ được lưu trong phần “Khả năng lập trình” trong cơ sở dữ liệu.

VBA Excel

Tất cả những gì còn lại là gọi thủ tục được lưu trữ từ Excel, cung cấp Ngày trả lương thông số của “2017/05/25”. Bạn sẽ lưu ý rằng tôi chỉ cần nhập dữ liệu  Ngày trả lương dưới dạng một chuỗi thay vì vật lộn với các định dạng ngày khác nhau. Nó đủ đơn giản để chuyển đổi một chuỗi thành một ngày bằng cách sử dụng Chuyển đổi chức năng nếu Ngày trả lương được sử dụng cho các mục đích số học.

Tạo một sổ làm việc mới. Mở cửa sổ mã VBA và chèn một mô-đun.

Từ menu Công cụ của cửa sổ mã, hãy tham khảo phần thích hợp Thư viện ActiveX 2.nn để tạo thuận lợi cho việc sử dụng các đối tượng dữ liệu.

Dán đoạn mã sau vào cửa sổ Code. Điều này, sau khi được kích hoạt, sẽ kết nối với SQL Server, theo quy trình phụ 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

Thêm một nút vào Sheet1 và gán nó cho thủ tục phụ “Thủ tục ghi được lưu trữ"

Kết quả

Nhấn nút, sau đó kiểm tra tblStaff, tblStaff này sẽ được cập nhật theo độ tuổi và độ tuổi. Quá trình xử lý đã diễn ra phía máy chủ.

Khôi phục sổ làm việc bị hỏng

Nếu Excel bị lỗi, bản sao duy nhất của bảng tính mà bạn đang sử dụng có thể cũng bị mất theo. Trong nhiều trường hợp, Excel thường không thể khôi phục các bảng tính bị hỏng; trong trường hợp đó, tất cả công việc đã thực hiện kể từ khi tạo bảng tính có thể bị mất vĩnh viễn, trừ khi bạn có công cụ để khôi phục chúng. sửa excel xlsx hoặc xlsm.

Giới thiệu tác giả:

Felix Hooker là một chuyên gia phục hồi dữ liệu trong DataNumen, Inc., công ty hàng đầu thế giới về công nghệ khôi phục dữ liệu, bao gồm sửa chữa rar và các sản phẩm phần mềm phục hồi sql. Để biết thêm thông tin, hãy truy cập www.datanumennăm