Lập trình vba kết nối và soạn thảo excel qua mạng
A-Tools cung cấp giao diện người dùng để kết nối và soạn thảo tệp Excel qua mạng. A-Tools cung cấp đối tượng lập trình để bạn có thể tự thiết kế một giao diện nhập liệu khác và truy xuất dữ liệu Excel qua mạng bằng dòng lệnh, cài đoạn code vào ứng dụng Excel của bạn để nó có thể chạy được qua mạng. Bài viết dưới đây tôi trình bày với các bạn phương pháp lập trình VBA với A-Tools để kết nối và soạn thảo tệp Excel qua mạng.
Nhúng file thư viện “addinatools.dll” vào file Excel của bạn
Mở file Excel. Nhấn ALT+F11 để vào môi trường lập trình VBE (Visual Basic Editor). Vào menu A-Tools->References…, khi hiện ra hộp thoại bạn hãy chọn mục Add-In A-Tools, nếu không thấy hãy chọn nút “Browse…” và chọn file ở đường dẫn “C:\Windows\System32\addinatools.dll”. Kết quả bạn thực hiện được như hình dưới đây:
1. Thành phần của đối tượng BSNetwork
2. Lập trình truy xuất dữ liệu Excel qua mạng
Để tiện cho thực hành, các bạn mở tệp “BSNetworkInfo.xls” để xem mã nguồn và chạy thử.
+ StartServer()
Loại: Hàm, giá trị trả về là kiểu Boolean.
Ứng dụng: dùng để tạo máy chủ để chia sẻ và quản trị dữ liệu cho các máy khách từ xa. Nếu tạo máy chủ thành công hàm trả về TRUE, ngược lại là FALSE.
Cấu trúc:
Code:
Function StartServer([UserName As String], [Password As String]) As Boolean
UserName, Password: Tên và mật khẩu truy cập để tạo/chạy máy chủ. Khi cài đặt A-Tools cung cấp sẵn hai user là “admin” và “user”, mật khẩu để trống. Thiết lập UserName & Password, vào màn hình “Quản trị máy chủ”.
Nếu bỏ qua tất cả các tham số thì khi chạy hàm sẽ hiện một hộp thoại để nhập UserName và Password.
+ StopServer()
Loại: Thủ tục.
Ứng dụng: dùng để tắt máy chủ. Khi máy chủ tắt thì các máy khách từ xa không thể kết nối tới nó.
Cấu trúc: Thủ tục này không có tham số.
+ IsRunning
Loại: Thuộc tính. Kiểu giá trị Boolean, chỉ đọc.
Ứng dụng: dùng để kiểm tra trạng thái máy chủ hay máy khách có đang chạy không. Nếu đang chạy giá trị trả về TRUE.
Cấu trúc: Thuộc tính này không có tham số.
+ IsServer
Loại: Thuộc tính. Kiểu giá trị Boolean, chỉ đọc.
Ứng dụng: dùng để kiểm tra trạng A-Tools đang chạy có phải là máy chủ không. Nếu là máy chủ giá trị trả về TRUE.
Cấu trúc: Thuộc tính này không có tham số.
+ LocalHost
Loại: Thuộc tính. Kiểu giá trị String, chỉ đọc.
Ứng dụng: cho biết tên của máy tính đang chạy.
Cấu trúc: Thuộc tính này không có tham số.
+ LocalIP
Loại: Thuộc tính. Kiểu giá trị String, chỉ đọc.
Ứng dụng: cho biết địa chỉ IP của máy tính đang chạy trong mạng LAN.
Cấu trúc: Thuộc tính này không có tham số.
+ RemoteHost
Loại: Thuộc tính. Kiểu giá trị String, chỉ đọc. Chỉ dùng cho máy khách.
Ứng dụng: cho biết tên của máy chủ đang được kết nối.
Cấu trúc: Thuộc tính này không có tham số.
+ RemoteIP
Loại: Thuộc tính. Kiểu giá trị String, chỉ đọc. Chỉ dùng cho máy khách.
Ứng dụng: cho biết địa chỉ IP của máy chủ đang được kết nối trong mạng.
Cấu trúc: Thuộc tính này không có tham số.
+ Port
Loại: Thuộc tính. Kiểu giá trị Long, chỉ đọc.
Ứng dụng: cho biết cổng được dùng để kết nối giữa máy chủ và máy khách. Ngầm định khi cài đặt A-Tools, cổng là 1711. Máy chủ và các máy khách muốn kết nối với nhau phải chung một cổng. Người dùng có thể thay đổi cổng bằng cách mở màn hình “Tùy chọn” để thay đổi.
Cấu trúc: Thuộc tính này không có tham số.
Ví dụ: khởi tạo máy chủ
Tạo một Module trong VBE. Dán các đoạn code sau và chạy.
#If VBA7 Then
Declare PtrSafe Function MsgBoxW2 Lib "AddinATools.dll" (ByVal MSG As Variant, Optional ByVal uType As VbMsgBoxStyle = vbOKOnly, Optional ByVal Caption As Variant = vbNullString, Optional ByVal hwnd As Long = 0) As VbMsgBoxResult
#Else
Declare Function MsgBoxW2 Lib "AddinATools.dll" (ByVal MSG As Variant, Optional ByVal uType As VbMsgBoxStyle = vbOKOnly, Optional ByVal Caption As Variant = vbNullString, Optional ByVal hwnd As Long = 0) As VbMsgBoxResult
#End If
Function RunServer() As Boolean
On Error GoTo lbEndProc
Dim XNet As New BSNetwork
If Not XNet.IsRunning Then
RunServer = XNet.StartServer("admin", "")
If Not RunServer Then Exit Function
MsgBox "Server is started.", vbInformation
End If
lbEndProc:
Set XNet = Nothing
If Err <> 0 Then
MsgBoxW2 Err.Description, vbCritical, Application.Caption
End If
End Function
Sub StopServer()
On Error GoTo lbEndProc
Dim XNet As New BSNetwork
If XNet.IsRunning And XNet.IsServer Then
XNet.StopServer
MsgBox "Server is stopped.", vbInformation
End If
lbEndProc:
Set XNet = Nothing
If Err <> 0 Then
MsgBoxW2 Err.Description, vbCritical, Application.Caption
End If
End Sub
Hàm MsgBoxW2() của A-Tools cho phép hiện chuỗi Unicode và tùy chỉnh ngôn ngữ theo thiết lập của A-Tools. Cách dùng tương tự như hàm MsgBox() của VBA.
+ Connect()
Loại: Hàm. Kiểu giá trị Boolean.
Ứng dụng: dùng để kết nối tới máy chủ. Nếu kết nối thành công giá trị trả về TRUE.
Cấu trúc: Code:Function Connect([Host As String], [UserName As String], [Password As String]) As Boolean
Host: là tên hoặc địa chỉ IP của máy chủ, cũng có thể là tên miền/domain (ví dụ: www.bluesofts.net)
UserName, Password: Tên và mật khẩu để tạo kết nối máy chủ. Tên và mật khẩu được tạo trong máy chủ, người dùng phải biết mới kết nối được.
+ Disconnect()
Loại: Thủ tục.
Ứng dụng: dùng để ngắt kết nối tới máy chủ.
Cấu trúc: Thủ tục này không có tham số.
+ Connected
Loại: Thuộc tính. Kiểu giá trị Boolean, chỉ đọc.
Ứng dụng: dùng để kiểm tra trạng máy khách đã được kết nối thành công chưa. Nếu kết nối thành công giá trị trả về TRUE.
Cấu trúc: Thuộc tính này không có tham số.
+ Databases
Loại: Thuộc tính. Kiểu class BSDatabases, chỉ đọc.
Ứng dụng: chứa danh sách CSDL/Database mà máy chủ đang chia sẻ. Mỗi phần tử của danh sách (Item) có kiểu BSDatabase.
Cấu trúc: Đối tượng này có các thành phần như sau
Count: cho biết số lượng CSDL/Database
Update: cập nhật danh sách CSDL. Có tham số LoadType
Function Update(LoadType As BSLoadType) As Boolean
Nếu LoadType là ltActive (ngầm định), cập nhật danh sách đang hoạt động tại máy chủ
Nếu LoadType là ltInactive, cập nhật danh sách KHÔNG hoạt động tại máy chủ
Nếu LoadType là ltAll, cập nhật tất cả danh sách CSDL máy chủ đang quản lý.
+ Database
Loại: Thuộc tính. Kiểu class BSDatabase, chỉ đọc.
Ứng dụng: chứa thông tin về CSDL/Database.
Cấu trúc: Đối tượng này có các thành phần như sau:
UserRanges: kiểu class BSUserRanges, chứa danh sách các vùng chia sẻ của CSDL
CA, FullName, Type….các bạn tự tìm hiểu.
+ UserRanges
Loại: Thuộc tính. Kiểu class BSUserRanges, chỉ đọc.
Ứng dụng: chứa danh sách các vùng chia sẻ của CSDL. Mỗi phần tử của danh sách là một BSUserRange.
Cấu trúc: Đối tượng này có các thành phần như Count, Updated, Open,…các bạn tự tìm hiểu.
+ UserRange
Loại: Thuộc tính. Kiểu class BSUserRange, chỉ đọc.
Ứng dụng: chứa và thiết lập các thông tin của vùng chia sẻ.
Cấu trúc:
Các thành phần của class BSUserRange can thiệp vào vùng dữ liệu như ở giao diện người dùng.
Đối tượng Range, Cells có kiểu BSRange gần giống với class Range của Excel, cho phép nhận và ghi dữ liệu lên vùng dữ liệu trong máy chủ.
+ Ví dụ: máy khách kết nối và đọc các thông tin CSDL trong máy chủ.
Function ConnectToServer() As Boolean
On Error GoTo lbEndProc
Dim XNet As New BSNetwork
ConnectToServer = XNet.IsRunning And XNet.Connected
If Not ConnectToServer Then
ConnectToServer = XNet.Connect("localhost", "user", "")
'ConnectToServer = XNet.Connect("127.0.0.1", "user", "")
'ConnectToServer = XNet.Connect("www.bluesofts.net", "user", "")
If Not ConnectToServer Then Exit Function
MsgBox "Client is connecting to server.", vbInformation
End If
lbEndProc:
Set XNet = Nothing
If Err <> 0 Then
MsgBoxW2 Err.Description, vbCritical, Application.Caption
End If
End Function
Sub Disconnect()
On Error GoTo lbEndProc
Dim XNet As New BSNetwork
If XNet.IsRunning And Not XNet.IsServer Then
XNet.Disconnect
MsgBox "Client is disconnected.", vbInformation
End If
lbEndProc:
Set XNet = Nothing
If Err <> 0 Then
MsgBoxW2 Err.Description, vbCritical, Application.Caption
End If
End Sub
Sub GetDatabasesInfo()
On Error GoTo lbEndProc
Dim XNet As New BSNetwork
Dim db As BSDatabase
Dim rng As BSUserRange
Dim I&
If Not ConnectToServer Then Exit Sub
Cells.ClearContents
Range("A1").Value = "DATABASE INFORMATIONS"
I = 2
For Each db In XNet.Databases
I = I + 1
Cells(I, 1).Value = db.Name
For Each rng In db.UserRanges
I = I + 1
Cells(I, 2).Value = rng.ID
Cells(I, 3).Value = rng.Name
Next
Next
Set XNet = Nothing
lbEndProc:
Set UR = Nothing
Set XNet = Nothing
If Err <> 0 Then
MsgBoxW2 Err.Description, vbCritical, Application.Caption
End If
End Sub
+ BSRange
Loại: Thuộc tính. Kiểu class BSRange, chỉ đọc.
Ứng dụng: đọc và ghi dữ liệu và công thức lên vùng dữ liệu. Cách đọc và ghi dữ liệu tương tự như class Range của Excel.
Ví dụ:
BSRange(“A1”).Value = “Nhập dữ liệu lên máy chủ”
BSRange(“A2”).Formula = “=SUM(B1:B10)”
BSRange(“A3”).FormulaArray = “=SUM(IF(B1:B10 > 10, B1:B10, 0))”
Địa chỉ vùng có thể dùng theo chỉ mục dòng và cột.
BSRange(2, 2).Formula = “=SUM(B1:B10)”
Cấu trúc:
+ Ví dụ: Máy khách ghi dữ liệu lên CSDL “Shops.xls”, sheet “Shop 1” trong máy chủ.
Sub WriteDataToServer()
On Error GoTo lbEndProc
Dim XNet As New BSNetwork
Dim UR As BSUserRange
Dim I&
If Not ConnectToServer Then Exit Sub
Set UR = XNet.Databases("Shops.xls").UserRanges("Shop 1")
UR.Range("A7").Value = "Writing data by VBA!"
For I = 9 To 30
UR.Cells(I, 4).Value = I * 1000
'UR.Range("D" & I).Value = I * 1000
'Use Cells() is faster Range() !!!
Next I
lbEndProc:
Set UR = Nothing
Set XNet = Nothing
If Err <> 0 Then
MsgBoxW2 Err.Description, vbCritical, Application.Caption
End If
End Sub
+ Ví dụ: Máy khách lấy dữ liệu lưu trong CSDL “Shops.xls”, sheet “Shop 1” ở máy chủ.
Sub ReadDataFromServer()
On Error GoTo lbEndProc
Dim XNet As New BSNetwork
Dim UR As BSUserRange
Dim I&
If Not ConnectToServer Then Exit Sub
Set UR = XNet.Databases("Shops.xls").UserRanges("Shop 1")
Cells.ClearContents
UR.Range("A7").Value = "Reading data in ""Shop 1"" by VBA!"
For I = 9 To 30
Cells(I, 1).Value = UR.Cells(I, 4).Value
UR.Range("D" & I).Value = I * 1000
Next I
lbEndProc:
Set UR = Nothing
Set XNet = Nothing
If Err <> 0 Then
MsgBoxW2 Err.Description, vbCritical, Application.Caption
End If
End Sub
3. Kết hợp giữa BSNetwork và BSFormulaArray
Khi máy khách đang kết nối tới máy chủ, ta có thể dùng hàm BS_SQL để truy vấn dữ liệu trong máy chủ.
+ Ví dụ: lấy toàn bộ dữ liệu trong sổ KHO của file Access “Examble.mdb” đã kết nối với DBKEY là “MDB” trên máy chủ.
=BS_SQL(“select * from KHO”, “SERVERSOURCE=YES; DBKEY=MDB”)
Trong VBA ta dùng hàm GetRecordSet().
+ GetRecordSet()
Loại: Hàm. Kiểu giá trị đối tượng Recordset.
Ứng dụng: dùng để truy vấn câu lệnh SQL, lấy dữ liệu từ máy chủ. Nếu kết nối thành công, hàm trả về bảng dữ liệu trong đối tượng Recordset, ngược lại trả về Nothing.
Cấu trúc:
Function GetRecordSet(SQLText As String, Options As String) As Unknown
Tham số SQLText, Options dùng tương tự như hàm BS_SQL().
+ Ví dụ: lấy toàn bộ dữ liệu trong sổ KHO của file Access “Examble.mdb” đã kết nối với DBKEY là “MDB” trên máy chủ.
Sub ReadDataFromServer()
On Error GoTo lbEndProc
Dim XNet As New BSNetwork
Dim UR As BSUserRange
Dim I&
If Not ConnectToServer Then Exit Sub
Set UR = XNet.Databases("Shops.xls").UserRanges("Shop 1")
Cells.ClearContents
UR.Range("A7").Value = "Reading data in ""Shop 1"" by VBA!"
For I = 9 To 30
Cells(I, 1).Value = UR.Cells(I, 4).Value
UR.Range("D" & I).Value = I * 1000
Next I
lbEndProc:
Set UR = Nothing
Set XNet = Nothing
If Err <> 0 Then
MsgBoxW2 Err.Description, vbCritical, Application.Caption
End If
End Sub
Bài viết sau tôi sẽ giới thiệu kỹ thuật lập trình ứng dụng Chat trong Excel cùng A-Tools.
Để tìm hiểu về lập trình mạng nâng cao trong A-Tools các bạn nên đọc và chạy thử các tệp:
“BSNetworkInfo.xls” , “BSNetwork.xls” (đường dẫn C:\A-Tools\HELP & DEMOS\A-Tools VBA Programming).
Tác giả: Nguyễn Duy Tuân - Công ty Cổ phần Bluesofts