dinhanhency
Thành viên mới
- Tham gia
- 10/1/09
- Bài viết
- 25
- Được thích
- 46
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.
Chúng ta tạo 1 button trong Excel, Sau đó viết code cho button này như sau.
Mã lệnh của mDataFilter như sau.
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.
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.
Mã:
-- =============================================
-- 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
GOChúng ta tạo 1 button trong Excel, Sau đó viết code cho button này như sau.
Mã:
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ã:
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 SubVớ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.
