Lập trình kết nối và soạn thảo tệp Excel qua mạng cùng A-Tools (1 người xem)

[Nguyễn Duy Tuân]

LẬP TRÌNH VBA KẾT NỐI VÀ SOẠN THẢO TỆP 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:

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.
[GPECODE=vb]
#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
[/GPECODE]
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:

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ủ.
[GPECODE=vb]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
[/GPECODE]

+ 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ủ.
[GPECODE=vb]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
[/GPECODE]
+ Ví dụ: Máy khách lấy dữ liệu lưu trong CSDL “Shops.xls”, sheet “Shop 1” ở máy chủ.
[GPECODE=vb]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
[/GPECODE]
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ủ.
[GPECODE=vb]Sub GetRecordSetFromServer()
On Error GoTo lbEndProc
Dim XNet As New BSNetwork
Dim Rst As Object
If Not ConnectToServer Then Exit Sub
Cells.ClearContents
Set Rst = XNet.GetRecordSet("select * from KHO", "DBKEY=MDB")
Range("A1").CopyFromRecordset Rst
lbEndProc:
Set Rst = Nothing
Set XNet = Nothing
If Err <> 0 Then
MsgBoxW2 Err.Description, vbCritical, Application.Caption
End If
End Sub
[/GPECODE]


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).

 

[Nguyễn Duy Tuân]

Tài liệu và các file ví dụ lập trình VBA kết nối mạng trong Excel cùng A-Tools

Gửi các bạn tài liệu và các file ví dụ lập trình VBA kết nối mạng trong Excel cùng A-Tools.

DOWNLOAD tài liệu

 

[Nguyễn Duy Tuân]

Ví dụ máy khách mở vùng Excel từ máy chủ để soạn thảo

Gửi tiếp các bạn ví dụ máy kháchmở vùng Excel từ máy chủ.
Bình thường khi máy khách đã kết nối tới máy chủ, tại màn hình "Quản trị máy khách" người dùng có thể mở vùng dữ liệu hay file Excel từ máy chủ để soạn thảo. A-Tools cung cấp khả năng lập trình (viết macro) để làm việc này.

Thủ tục dưới đây chỉ chạy khi máy khách đã kết nối tới máy chủ.
[GPECODE=vb]Sub OpenRange()
Dim XNet As New BSNetwork
Dim URs As BSUserRanges
Dim ur As BSUserRange
'Nhận các vùng dữ liệu trong file "Shops.xls" trong máy chủ vào biến URs
Set URs = XNet.Databases("Shops.xls").UserRanges
'Kiểm tra vùng "Shop 2_A9-F30" đã mở chưa, nếu chưa thì mở, nếu có rồi thì khởi hoạt nó
If Not URs.IsOpened("Shop 2_A9-F30") Then
Set ur = URs.Open("Shop 2_A9-F30")
Else
'Khởi hoạt nó
Set ur = URs("Shop 2_A9-F30")
ur.Activate
End If

If MsgBox("Do you want to close this range?", vbQuestion + vbYesNo) = vbYes Then
ur.Close
End If

Set ur = Nothing
Set URs = Nothing
Set XNet = Nothing
End Sub[/GPECODE]

 

[Nguyễn Duy Tuân]

Lập trình VBA trong Excel kết nối CSDL trong mạng LAN, Internet - A-Tools

Lập trình kết nối CSDL trong máy tính phần lớn chúng ta quen kết nối tới CSDL nằm tại máy có file chương trình chạy, hoặc trong mạng LAN, ít người biết tới kết nối qua Internet, có thể từ trước chúng ta không có giải pháp nào cho việc này...

Video này tôi hướng dẫn mọi người dùng ADO kết nối tới CSDL Access trong mạng LAN, với các CSDL khác như Excel, Foxpro, SQL Server.... cũng tương tự, chỉ khác phần khai báo ConnectionString. Đây là đoạn video hướng dẫn căn bản và đơn giản tối đa để những người mới học lập trình CSDL có thể thực hành và làm ngay được.

Các CSDL Access, Excel, Foxpro, Paradox,... chỉ có thể chia sẻ qua mạng LAN và có những hạn chế về bảo mật, không thể chia sẻ qua Internet. Trong video này tôi đưa ra một giải pháp mới với Add-in A-Tools cho phép chúng ta kết nối CSDL qua Internet với những loại CSDL mà bình thường không thể làm được.

Hy vọng video giúp ích được nhiều người bước đầu tiếp cận vào lập trinhg CSDL trong Excel và những người đã làm và tìm ra được giải pháp mới cho việc kết nối CSDL qua internet vào Excel.

[video=youtube;lSyoR1XFte8]https://www.youtube.com/watch?v=lSyoR1XFte8&feature=youtu.be&list=PLZi2tdQERHM09nzkoiavJO4pHcED1NmOQ[/video]​

[GPECODE=vb]
'Cách làm kết nối CSDL qua LAN bằng ADO
Sub GetDataFromADO_Recordset()
Dim cnn As New ADODB.Connection
Dim RST As ADODB.Recordset
Dim cFileName As String

cFileName = "\\192.168.1.115\Access Databases\Example.mdb"
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & cFileName

Set RST = cnn.Execute("select * from dmkh")

Range("A5").CopyFromRecordset RST

RST.Close
Set RST = Nothing
cnn.Close
Set cnn = Nothing
End Sub

Sub ExxecuteCommandWithADO()
Dim cnn As New Connection
Dim RST As Recordset
Dim cFileName As String, X As Long

'Folder "\\192.168.1.115\Access Databases" is share full
cFileName = "\\192.168.1.115\Access Databases\Example.mdb"
'Connect to Access database
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & cFileName

cnn.Execute "DELETE FROM dmkh WHERE MA_KH LIKE 'NEW%'", X
cnn.Execute "INSERT INTO dmkh (MA_KH,TINH_TP) VALUES ('NEW 102','HN')", X
Set RST = cnn.Execute("SELECT * FROM dmkh", X)

'Close connection and free memory
RST.Close
Set RST = Nothing
cnn.Close
Set cnn = Nothing
End Sub

'Cách làm kết nối CSDL qua internet bằng A-Tools
Sub GetRecordSetFromServer()
Dim Xnet As New BSNetwork
Dim cnn As BSConnection
Dim RST As ADODB.Recordset

If Not Xnet.Connected Then
If Not Xnet.Connect("bluesofts.dyndns.org", "user") Then Exit Sub
End If

Set cnn = Xnet.OpenConnection("MDB")
Set RST = cnn.ExecSql("SELECT * FROM DMKH")

Range("G6:M1000").ClearContents
Range("G6").CopyFromRecordset RST

RST.Close
Set RST = Nothing
cnn.Close
Set cnn = Nothing
Set Xnet = Nothing
End Sub

Sub DoExecuteCommandINSERT()
Dim Xnet As New BSNetwork
Dim cnn As BSConnection
Dim X As Long

If Not ConnectToServer Then Exit Sub
Set cnn = Xnet.OpenConnection("MDB")

X = cnn.ExecSqlCmd("INSERT INTO dmkh (MA_KH,TINH_TP) VALUES ('NEW 102','HN')")
MsgBoxW2 "Records affected: " & X, , "INSERT"

cnn.Close
Set cnn = Nothing
Set Xnet = Nothing
End Sub

[/GPECODE]

Download tài liệu hướng dẫn và mã nguồn ví dụ trong video