Gọi thủ tục SQL server từ Excel sử dụng VBA

Chia sẻ bởi:hands

★★★★★

‹ Chỉ giúp mình cách update liên tục... Hỏi về cách đổ dữ liệu từ... ›

Quảng cáo

Chào các bạn.
Bài viết này mình kết hợp giữa VBA Excel và MS SQL server trong việc truy vấn thông tin.
Tác dụng của SQL server cho chúng ta khả năng ổn định về tốc độ cũng như độ an toàn khi dữ liệu lớn hơn mã excel có thể gặp rủi ro.

Trong bài này chúng ta có 1 sheet riêng là CauHinh để chúng ta lưu các cấu hình về máy chủ kết nối. Bao gồm Server, database, user ID, Password.

Để thực hiện gọi 1 thủ tục trong SQL, chúng ta tạo thủ tục đơn giản như sau.
-- =============================================
-- Author:        Dinh Anh
-- Create date: August 25,2011
-- Description:   Filter data
-- =============================================
CREATE PROCEDURE sp_DataFilter
    @_Para CHAR(20)=''
AS
BEGIN

SET NOCOUNT ON;

-- Insert statements for procedure here
    SELECT Ma_Dvi, Ten_Dvi FROM [dbo].[Branch] WHERE Ma_Dvi = LTRIM(RTRIM(@_Para))
END
GO
Chúng ta tạo 1 button trong Excel, Sau đó viết code cho button này như sau.
Private Sub cmdDataFilter_Click()
m = MsgBox("Xin vui long doi den khi thong bao hoan thanh de tranh treo may", vbOKOnly, "Warnings!.")

' Some code before         
        ' Goi sub mDataFilter
        Call mDataFilter
        m = MsgBox("Da thuc hien xong", vbOKOnly, "Warnings")
End Sub
Mã lệnh của mDataFilter như sau.
Sub mDataFilter()
        Dim cnt As adodb.Connection ' references Microsoft Active Data Object Library XX.XX
        Dim Rst As adodb.Recordset
        Dim cmd As adodb.Command
        Dim stCon As String         'SQL Connection string
        Dim stProcName As String    'Stored Procedure name

Dim Para As Variant

Set cnt = New adodb.Connection
        Set Rst = New adodb.Recordset
        Set cmd = New adodb.Command

' Lấy thông tin ở sheet cấu hình(CauHinh ) để tạo kết nối tới máy chủ SQL.

cnt.ConnectionString = "Provider=SQLOLEDB;Data Source=" + Worksheets("CauHinh").Range("E2") + ",1433;Initial Catalog=" + Worksheets("CauHinh").Range("E3") + ";User Id=" + Worksheets("CauHinh").Range("E4") + ";Password=" + Worksheets("CauHinh").Range("E5")

cnt.Open

cmd.CommandType = adCmdStoredProc      
        cmd.ActiveConnection = cnt             
        cmd.CommandText = "sp_DataFilter"         
        cmd.CommandTimeout = 0

' Chúng ta thêm parametter cho thủ tục SQL
       With cmd
               .Parameters.Append .CreateParameter("@_Para", adVarChar, adParamInput, 16,"DEP_NO09")               
       End With
        Rst.Open cmd.Execute
        If Rst.RecordCount = 0 Then Exit Sub ' Nếu không có kết quả thì exit.

Rst.MoveFirst

Range("I5").Select
        Worksheets("FIndexes").Range("A8").CopyFromRecordset Rst ' Copy kết quả điền vào sheet FIndexes bắt đầu từ dòng A8.

If CBool(Rst.State And adStateOpen) = True Then Rst.Close
        Set Rst = Nothing

If CBool(cnt.State And adStateOpen) = True Then cnt.Close
        Set cnt = Nothing
End Sub
Với cách làm này chúng ta có thể khai báo thông tin máy chủ và cơ sở dữ liệu SQL, User name, password mà chúng ta cần kết nối đến một cách thuận tiện, tuy nhiên lưu ý về tính bảo mật thông tin ở chỗ này. Hơn nữa chúng ta có thể viết thêm phần nhận giá trị cho Parametter từ 1 cell trên sheet cho thuận tiện khi cần thay đổi giá trị parametter. Hoặc viết một thủ tục với nhiều parametter với các yêu cầu phức tạp hơn.