Microsoft Excel Desktop là phần mềm bảng tính rất mạnh mẽ chạy trên nền tảng Windows và Macbook, cho phép lập trình thông qua các đối tượng COM bằng các ngôn ngữ lập trình trên Windows như VBA, VB6, Delphi, C#, VB.NET, C++,.... Nhiều năm qua thế giới đã lập trình ra rất nhiều phần mềm to, nhỏ trên Excel. Về bảng tính online Microsoft cung cấp Excel Online (chạy trên trình duyệt web) lưu trên OneDrive và Excel365 (chạy trên Windows) đồng bộ lên OneDrive. Lợi ích của Excel 365 là vẫn cho phép lập trình VBA với điều kiện phải mở file ra. Google cung cấp Google Sheets mở trên trình duyệt web và lưu trên Google Drive. Về lập trình thì Excel Online dùng JavaScript hoặc TypeScript để lập trình, Google Sheets thì dùng JavaScript. Phần mềm lập trình trên nền tảng Windows sử dụng các hệ sinh thái Windows. Ví dụ kết nối dữ liệu như SQL Server, MySQL, MS Access, MS Excel, các giao diện nhập liệu với Windows Form,... Trên web thì hệ sinh thái để lập trình cùng ứng dụng nó khác. Bạn đã có một phần mềm Excel VBA bây giờ muốn đồng bộ dữ liệu lên cloud (cụ thể là lưu trên drive) nếu lập trình lại bằng JavaScript chạy trên nền tảng web sẽ rất khó khăn, trừ ứng dụng siêu nhỏ, nếu cố làm được bạn phải dành rất nhiều thời gian, có thể nhiều năm để làm lại một phần mềm trên Excel Online hoặc Google Sheets. Bản thân tôi đã có phần mềm kế toán A-Excel mất vài năm mới làm được trên Excel VBA nếu bây giờ tôi làm lại y như vậy trên Google Sheets hay Excel Online với tôi là không thể.
Với nhu cầu từ các phần mềm to, nhỏ trên Excel VBA hay một ngôn ngữ khác đã có muốn đồng bộ toàn bộ hay một phần dữ liệu lên Google Sheets, Excel Online (từ nay gọi là bảng tính online), vẫn bằng ngôn ngữ lập trình VBA, VB6, Delphi, C#, VB.NET, C++, tên các thủ tục gọi lệnh, tên thuộc tính các đối tượng gần như giống hệt Excel, code VBA cũ của bạn chỉ thay đổi vài khai báo ban đầu, kiểm tra kết nối, còn lại gần như giữ nguyên. Tôi tạo ra các class trong thư viện AddinATools.dll để thực hiện được việc này.
Trong bài viết này tôi sẽ trình bày chi tiết cách thực lập trình VBA để cập nhật dữ liệu và định dạng lên Google Sheets, Excel Online, cách lập trình với các tập tin trên Google Drive, OneDrive, DrobBox. Một code chạy trên đa nền tảng. "Base code for all platforms".
Bài đầu tiên mời các bạn xem video hướng dẫn cũng như giới thiệu giải pháp này.
Video hướng dẫn Phần 01
(Phần 01: Hướng dẫn lập trình cho Google Sheets và Excel Online giống như Excel VBA)
Để thực hành cấc code trong chủ đề này bạn cần thực hiện các yêu cầu về thư viện:
1. Cài đặt Add-in A-Tools v10 (2024) Pro, bản cập nhật tối thiểu ngày 27-11-2024. Download
2. Nhúng thư viện lập trình vào dự án VBA
Trong môi trường VBA, vào menu Tools - > References... check mục "AddinATools.dll"
Bây giờ bạn đã đủ điều kiện về môi trường và các thành phần thư viện tối thiểu để lập trình cùng tôi trong chủ đề này.
Để lập trình với bảng tính online cũng như các dịch vụ lưu trữ đám mây với thư viện Add-in A-Tools cần tuân thủ code cấu trúc code như sau:
(Toàn bộ code trong chủ đề này tôi dùng mẫu format code của diễn đàn là C# để định dạng, do không tìm được mẫu tương đồng. Mã nguồn lập trình là VBA các bạn nhé.)
Cấu trúc code
Sub GoogleSheet_CheckConnection()
Dim MyCloud As New BSCloud
On Error GoTo lbEnd
If Not MyCloud.Connected(ctGoogleDrive) Then
If Not MyCloud.OpenAuthor(Application, ctGoogleDrive, Application.Hwnd) Then
MsgBox "Can not connect to drive.", vbCritical
GoTo lbEnd
End If
End If
MsgBox "Connection successful!", vbInformation
'The following commands update data and format
lbEnd:
If Err <> 0 Then
Debug.Print "Error: " & Err.Description
End If
Set MyCloud = Nothing
End Sub
Ở code trên, chúng ta làm việc với dịch vụ cloud nào thì khai báo loại đó:
ctGoogleDrive - Làm việc với Google Drive/Google Sheets
ctOneDrive - Làm việc với OneDrive/Excel Online
ctDropBox - Làm việc với DropBox.
Hàm OpenAuthor() cho phép bạn đăng nhập tài khoản cloud của bạn và phê duyệt quyền truy cập cho thư viện làm việc. Bạn có thể mở trên form hoặc trên Website tùy vào tham số bạn nhập vào trong hàm.
Màn hình đăng nhập là của các nhà cung cấp dịch vụ đám mây cung cấp, không phải do A-Tools tạo ra.
Xem toàn bộ các tập tin và các sheet trên cloud bạn đang mở
Trên đầu Module tôi khai báo hai hằng số về FileID và loại Cloud. Các ví dụ bài viết này sẽ dùng hai hằng số này, nếu bạn muốn làm việc với dịch vụ cloud nào, với tập tin nào chỉ cần sửa hai hằng số này.
Private Const FileID = "https://docs.google.com/spreadsheets/d/1-o__30yfKSTRAB3vrqEo6fCnMQSXHEN8ImtE4hca4CA/edit?gid=401030668#gid=401030668"
Private Const MyCloudType = ctGoogleDrive
Sub GoogleSheet_ViewStruct()
Dim MyCloud As New BSCloud
Dim Wb As BSCloudWorkbook, Sh As BSCloudWorksheet
On Error GoTo lbEnd
If Not MyCloud.Connected(MyCloudType) Then
If Not MyCloud.OpenAuthor(Application, MyCloudType, Application.Hwnd) Then
Exit Sub
End If
End If
MyCloud.Workbooks.Open FileID, True 'Mở tập tin từ Drive
For Each Wb In MyCloud.Workbooks 'Duyệt từng tập tin đã mở
Debug.Print Wb.Name, "File_ID: " & Wb.ID
For Each Sh In Wb.Sheets 'Duyệt từng sheet trong tập tin - Workbook
Debug.Print vbTab & Sh.Name, Sh.ID
Next Sh
Next Wb
lbEnd:
If Err <> 0 Then
Debug.Print "Error: " & Err.Description
End If
Set Sh = Nothing
Set Wb = Nothing
Set MyCloud = Nothing
End Sub
Trong ví dụ trên tôi khai báo biến đối tượng để điều khiển bảng tính online:
Dim MyCloud As New BSCloud 'Điều khiển dịch vụ cloud
Dim Wb As BSCloudWorkbook 'Trong Excel là Excel.Workbook - Điều khiển tập tin
Dim Sh As BSCloudWorksheet 'Trong Excel là Excel.Worksheet - Điều khiển bảng tính
Các khai báo trên là đầu mối quan trọng để truy cập vào các thành phần của bảng tính online sau này.
Tập tin mã nguồn các vị dụ trong chủ đề này
Toàn bộ mã nguồn trình bày trong chủ đề này có trong tập tin theo địa chỉ dưới đây (các bạn cài Add-in A-Tools là có).
(*) Mã nguồn ví dụ trong bộ cài: "C:\A-Tools\HELP & DEMOS\A-Tools VBA Programming\Cloud\Write data to GoogleSheets_ExcelOnline.xlsm"
Làm việc với workbook và worksheet
Trong Excel làm việc với tập tin thì ta dùng class Workbook, làm việc với worksheet ta dùng class Worksheet.
Với A-Tools thì ta có hai class thay thế là BSCloudWookbook và BSCloudWorksheet. Tên các class để làm việc với cloud trong A-Tools có tên là "BSCloudxxx".
Dim MyCloud As New BSCLoud
Biến MyCloud dùng để quản trị kết nối, thông tin tài khoản kết nối, các thành phần quan trọng bao gồm:
MyCloud.FileManager : để làm việc với tập tin: Upload, Download, Copy, Move, Delete, Replace, ListFIles,.... Tôi sẽ trình bầy phần này riêng.
MyCloud.Workbooks : quản trị các tập tin mở trên cloud (drive). Kiểu Collection.
Để mở một tập tin trên cloud và mở sheet để làm việc ta làm như sau
Sub GoogleSheet_OpenSheet()
Dim MyCloud As New BSCloud
Dim Wb As BSCloudWorkbook, Sh As BSCloudWorksheet
On Error GoTo lbEnd
If Not MyCloud.Connected(MyCloudType) Then
If Not MyCloud.OpenAuthor(Application, MyCloudType, Application.Hwnd) Then
Exit Sub
End If
End If
Set Wb = MyCloud.Workbooks.Open(FileID) 'M? file trên drive
Set Sh = Wb.Sheets("report") 'M? sheet trong file
lbEnd:
If Err <> 0 Then
Debug.Print "Error: " & Err.Description
End If
'MyCloud.Workbooks.CloseAll
Set Sh = Nothing
Set Wb = Nothing
Set MyCloud = Nothing
End Sub
Nếu file chưa từng được mở chạy lênh mở
Set Wb = MyCloud.Workbooks.Open(FileID)
FileID là một Url đến tập tin trên drive hoặc ID của tập tin.
Với Google Sheets Url là: "https://docs.google.com/spreadsheets/d/1-o__30yfKSTRAB3vrqEo6fCnMQSXHEN8ImtE4hca4CA/edit?"
FIle ID là URL trên hoặc là "1-o__30yfKSTRAB3vrqEo6fCnMQSXHEN8ImtE4hca4CA" (là chuỗi nằm trong thành phần "*/d/FileID/*")
Với Excel Online trên OneDrive, Url là: "https://onedrive.live.com/edit?id=11870394B81CE870!34948&resid=11870394B81CE870!34948&ithint=file%2cxlsx&wdo=2&cid=11870394b81ce870"
File ID là "11870394B81CE870!34948"
Để tập kiểm tra tập tin đã mở hay chưa, ta kiểm tra MyCloud.Workbooks.IndexOf(FileID) > 0
Để trỏ đến tập tin đã mở từ cloud ta dùng Set wb = MyCloud.Workbooks(FileID)
Để đơn giản hóa toàn bộ khâu kiểm tra đã có hay chưa để mở các bạn chỉ cần làm một lệnh Set Wb = MyCloud.Workbooks.Open(FileID) hệ thống sẽ tự động kiểm tra nếu chưa mở sẽ mở, nếu đã mở thì nó trỏ đến. Biến Wb có kiểu BSCloudWorkbook sẽ làm việc với tập tin.
Thành phần của BSCloudWorkbook bao gồm:
+ Name: tên tập tin
+ ID (FileID): ID của tập tin trên cloud
+ Refress() : thủ tục cập nhật lại thông tin của file
+ Tables: Đối tượng chứa danh sách các table. Đối tượng này hiện tại chỉ được phép dùng với Excel Online. Kiểu Collection.
+ Sheets: Đối tượng chứa danh sách các Worksheet. Kiểu Collection.
(*) Lưu ý kiểu Collection là chứa danh sách, chúng đều có các thuộc tính chung là:+ Count (đếm số phần tử trong danh sách.
+ Item(NameOrID) trỏ đến một thành phần trong danh sách.
+ IndexOf(NameOrID) tìm kiếm và trả về vị trí trong danh sách.
Để mở một sheet để làm việc ta khai báo biến và trỏ vào sheet trong workbook, giống như với Excel.
Dim sh As BSCloudWorksheet
Set Sh = Wb.Sheets("report")
Class BSCloudWorksheet có các thành phần thuộc tính và thủ tục:
+ Name : tên sheet
+ ID : ID của sheet
+ Range() : trỏ đến địa chỉ vùng bảng tính.
+ Cells : trỏ đến ô trên bảng tính theo tọa độ RowIndex, ColumnIndex
+ BeginUpdate() : thủ tục thông báo chế độ đồng bộ
+ EndUpdate() : thủ tục kết thúc việc đồng bộ.
+ Parent : nhận điều khiển cha. ở đây chính là Sheets
+ Append() : thêm mảng giá trị vào vị trí dòng cuối của sheet. Hàm này chỉ dùng với Google Sheets
+ RowCount : đếm tổng số dòng đang có trong sheet
+ ColumnCount: đếm tổng số cột đang có trong sheet
+ AddRows() : hàm thêm hoặc bớt dòng trong sheet. Chỉ dùng với Google Sheets.
+ AddColumns(): hàm thêm hoặc bớt cột trong sheet. Chỉ dùng với Google Sheets.
+ Rows : điều khiển danh sách các dòng trong sheet, kiểu là BSCloudRange. Là một Collection.
+ Columns : điều khiển danh sách các cột trong sheet, kiểu là BSCloudRange. Là một Collection.
+ LastRow : tính ra tọa độ dòng cuối của sheet.
+ Clear() : xóa toàn bộ dữ liệu hoặc cả định dạng trên sheet.
+ ClearRequests : xóa toàn bộ các lệnh đồng bộ trong sheet nếu chưa gửi lên máy chủ.
+ Tables : điều khiển danh sách các table. Kiểu Collection. Chỉ dùng với Excel Online.
+ UploadRange() : hàm upload dữ liệu hoặc cả định dạng từ một vùng trên Excel lên bảng tính online.
+ Delete() : hàm xóa sheet.
Tạo thêm Worksheet
Sub ExcelOnline_AddSheet()
Dim MyCloud As New BSCloud
Dim Wb As BSCloudWorkbook, Sh As BSCloudWorksheet
On Error GoTo lbEnd
If Not MyCloud.Connected(MyCloudType) Then
If Not MyCloud.OpenAuthor(Application, MyCloudType, Application.Hwnd) Then
Exit Sub
End If
End If
Set Wb = MyCloud.Workbooks.Open(FileID)
Set Sh = Wb.Sheets.Add("report") 'Tạo sheet mới
lbEnd:
If Err <> 0 Then
Debug.Print "Error: " & Err.Description
End If
Set Sh = Nothing
Set Wb = Nothing
Set MyCloud = Nothing
End Sub
Video hướng dẫn Phần 02
(Phần 02: Hướng dẫn lập trình cho Google Sheets và Excel Online giống như Excel VBA)
Ghi dữ liệu và định dạng lên sheet
Ví dụ ghi dữ liệu và định dạng lên Google Sheets
Các bạn cần lưu ý các class/đối tượng để làm việc với bảng tính là BSCloudWorkbook (giống Excel.Workbook), BSCloudWorksheet (giống Excel.Worksheet), BSCloudRange (giống Excel.Range) tên các thủ tục, hàm, thuộc tính giống Excel vì thế các lệnh ghi dữ liệu, định dạng sẽ giống hệt Excel. Như vậy các mã nguồn Excel VBA cũ của các bạn có thể thay đổi chút ít là có thể cập nhật dữ liệu lên Google Sheets và Excel Online dễ dàng.
Sub GoogleSheet_WriteData()
Dim MyCloud As New BSCloud
Dim Wb As BSCloudWorkbook, Sh As BSCloudWorksheet
On Error GoTo lbEnd
If Not MyCloud.Connected(MyCloudType) Then
If Not MyCloud.OpenAuthor(Application, MyCloudType, Application.Hwnd) Then
Exit Sub
End If
End If
Set Wb = MyCloud.Workbooks.Open(FileID) 'Open File
Set Sh = Wb.Sheets("report") 'Open Sheet
'Write data
Sh.Range("A1").Value = "Nguyen Duy Tuan - https://bluesofts.net"
Sh.Range("A2").Value = Now
Sh.Range("A2").NumberFormat = "dd-mm-yyyy hh:mm:ss"
Sh.Range("A2").EntireColumn.ColumnWidth = 230 'Resize column A
lbEnd:
If Err <> 0 Then
Debug.Print "Error: " & Err.Description
End If
Set Sh = Nothing
Set Wb = Nothing
Set MyCloud = Nothing
End Sub
Ví dụ ghi dữ liệu và định dạng lên Excel Online
Ví dụ trên nếu bây giờ tôi thay đổi FileID gán vào Url của Excel Online trên OneDrive, MyCloudType là ctOneDrive thì code trên sẽ ghi dữ liệu lên Excel Online. Các bạn hãy xem và so sánh, code không phải thay đổi dòng nào.
Private Const FileID = "https://onedrive.live.com/edit?id=11870394B81CE870!34948&resid=11870394B81CE870!34948&ithint=file%2cxlsx&wdo=2&cid=11870394b81ce870"
'Or:
'Private Const FileID = "11870394B81CE870!34948"
Private Const MyCloudType = ctOneDrive
Mã nguồn để cập nhật dữ liệu và định dạng lên Excel Online giống hệt như với Google Sheets
Sub GoogleSheet_WriteData()
Dim MyCloud As New BSCloud
Dim Wb As BSCloudWorkbook, Sh As BSCloudWorksheet
On Error GoTo lbEnd
If Not MyCloud.Connected(MyCloudType) Then
If Not MyCloud.OpenAuthor(Application, MyCloudType, Application.Hwnd) Then
Exit Sub
End If
End If
Set Wb = MyCloud.Workbooks.Open(FileID) 'Open File
Set Sh = Wb.Sheets("report") 'Open Sheet
'Write data
Sh.Range("A1").Value = "Nguyen Duy Tuan - https://bluesofts.net"
Sh.Range("A2").Value = Now
Sh.Range("A2").NumberFormat = "dd-mm-yyyy hh:mm:ss"
Sh.Range("A2").EntireColumn.ColumnWidth = 230 'Resize column A
lbEnd:
If Err <> 0 Then
Debug.Print "Error: " & Err.Description
End If
Set Sh = Nothing
Set Wb = Nothing
Set MyCloud = Nothing
End Sub
Mời các bạn đọc tiếp:
Phần 02: Hướng dẫn lập trình với Google Sheets và Excel Online bằng Excel VBA và Add-in A-Tools