Hướng dẫn lập trình với Google Sheets và Excel Online bằng Excel VBA và Add-in A-Tools v10

[Nguyễn Duy Tuân]

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.

(Xem 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"

Còn tiếp....

 

[Nguyễn Duy Tuân]

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=1...8&ithint=file,xlsx&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

Còn tiếp...

 

[Nguyễn Duy Tuân]

Ghi dữ liệu và định dạng lên sheet

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.

Ví dụ ghi dữ liệu và định dạng lên 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

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"
'Hoặc
'Private Const FileID = "11870394B81CE870!34948"
Private Const MyCloudType = ctOneDrive

Copy y nguyên code ghi lên Google Sheets để chạy cho Excel Online

Sub ExcelOnline_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

(Xem tiếp Phần 02: Hướng dẫn lập trình cho Google Sheets và Excel Online giống như Excel VBA)​

Còn tiếp...

 

[Nguyễn Duy Tuân]

Các phương pháp về ghi dữ liệu và định dạng lên bảng tính Online

Trong phần bài viết này tôi sẽ trình bày các bạn chi tiết về việc ghi dữ liệu và định dạng lên sheet với các thành phần quan trong là BSCloudRange. Đây là class để làm việc với dữ liệu, định dạng trên sheet.

Nội dung bài viết này được trình bày trong video Phần 03

(Hướng dẫn lập trình cho Google Sheets và Excel Online giống như Excel VBA - Phần 03)
​

Từ Sheet (BSCloudWorksheet hay Excel.Worksheet) chúng ta đều có các thành phần thuộc tính có kiểu Range (trong Excel), BSCloudRange (tương đương với Range) để làm việc với dữ liệu và định dạng, các thành phần có tên gọi giống nhau gồm:

+ Range("địa chỉ tham chiếu") : đọc và ghi dữ liệu theo địa chỉ tham chiếu. địa chỉ đưa vào dạng văn bản, ví dụ : "A1", "C2:C6", "A2:A6,C2:C6".
+ Cells(RowIndex, ColumnIndex) : đọc và ghi dữ liệu theo tọa độ dòng, cột. Tọa độ này tính theo Sheet.
+ Rows([Index]) : quản lý và truy cập các dòng trong Sheet, vị trí của Index tính theo Sheet.
+ Columns([Index]) : quản lý và truy cập các cột trong Sheet, vị trí của Index tính theo Sheet.

Từ Range, BSCloudRange có các thành phần thuộc tính cùng kiểu để truy cập vào dữ liệu và định dạng.
+ Cells() : truy cập vào vùng theo tọa độ RowIndex, ColumnIndex. Tọa độ này tính theo Range. Lưu ý khác với Cells trong sheet.
+ Resize() : điều chỉnh kích thước vùng
+ Offset() : di chuyển địa chỉ vùng
+ EntireRow : lấy cả dòng
+ EntireColumn : lấy cả cột
+ Rows([Index]) : quản lý và truy cập các dòng trong Range, vị trí của Index tính theo Range.
+ Columns([Index]) : quản lý và truy cập các cột trong Range, vị trí của Index tính theo Range.

Dùng vòng lặp để truy xuất Range
Ví dụ dưới đây bạn làm việc với Excel hay với các bảng tính online (Google Sheets, Excel Online) là như nhau:

Sub GoogleSheet_Cells()
    Dim MyCloud As New BSCloud
    Dim Wb As BSCloudWorkbook, Sh As BSCloudWorksheet
    Dim I&, J&, n&
 
    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("report")
    Sh.BeginUpdate 'Remember run sh.EndUpdate()
    Dim C As BSCloudRange
    'For each in Cells
    'For Each C In Sh.Range("A3:B6").Cells
    'Way 1:
    I = 0
    For Each C In Sh.Range("A3:B6")
        I = I + 1
        C.Value = I
        If I Mod 2 = 0 Then
            C.Interior.Color = vbYellow
        End If
    Next
    'Way 2:
    n = 0
    For I = 1 To Sh.Range("A3:B6").Rows.Count
        For J = 1 To Sh.Range("A3:B6").Columns.Count
            n = n + 1
            Sh.Range("A3:B6").Cells(I, J).Value = n
        Next J
    Next I
 
    'For each in Rows
    I = 0
    For Each C In Sh.Range("E3:G5").Rows
        I = I + 1
        C.Value = I
        If I Mod 2 = 0 Then
            C.Interior.Color = vbYellow
        End If
    Next
    'For each in Columns
    I = 0
    For Each C In Sh.Range("B8:G10").Columns
        I = I + 1
        C.Value = I
        If I Mod 2 = 0 Then
            C.Interior.Color = vbYellow
        End If
    Next
 
    Sh.EndUpdate 'Sync all
lbEnd:
    If Err <> 0 Then
        Debug.Print "Error: " & Err.Description
    End If
    Set Sh = Nothing
    Set Wb = Nothing
    Set MyCloud = Nothing
End Sub

Kết quả chạy thủ tục trên ta có:

​

Các thành phần định dạng
Các thành phần thuộc tính dưới đây các bạn làm giống như với Excel, chỉ vài thuộc tính không được dùng.

Định dạng font chữ
+ Range.Font : là đối tượng định dạng với font chữ, các thuộc tính được dùng như Color, ColorIndex, Name, Size, Underline, Italic, Bold (các thuộc tính khác như Strikethrough, Subscript, Superscript,... không được dùng).

Định dạng nền
+ Range.Interior (Range.Fill) : là đối tượng để định dạng nền của Range. Bạn được phép dùng các thuộc tính: Color, ColorIndex để thiết lập màu sắc, thủ tục Clear() để xóa màu nền.

Định dạng viền - Borders
+ Range.Borders : định dạng đường viền, các thuộc tính được dùng là: Color, ColorIndex, LineStyle (kiểu đường vẽ), Weight (độ dày của nét vẽ). Nếu bạn muốn định dạng với loại đường viền thì chỉ định tham số: Range.Borders(XlBordersIndex). XlBordersIndex là Enum chỉ ra các loại đường viền như: xlEdgeLeft, xlEdgeRight,...

Ví dụ dưới đây tôi vừa ghi dữ liệu vừa định dạng. Ví dụ này chạy với cả Google Sheets, Excel Online. Với Excel chỉ khai báo kiểu Workbook, Worksheet mà thôi.

Sub GoogleSheet_Write()
    'Declare and connect to Google Drive
    Const FileID = "https://docs.google.com/spreadsheets/d/1-o__30yfKSTRAB3vrqEo6fCnMQSXHEN8ImtE4hca4CA/edit?" 'Google Sheets
    Dim MyCloud As New BSCloud
    Dim Wb As BSCloudWorkbook, Sh As BSCloudWorksheet
 
    On Error GoTo lbEnd
    'Check connection
    If Not MyCloud.Connected(ctGoogleDrive) Then
        If Not MyCloud.OpenAuthor(Application, ctGoogleDrive, Application.Hwnd) Then
            Exit Sub
        End If
    End If
    '-------------------------------
    Set Wb = MyCloud.Workbooks.Open(FileID) 'Open Goolge Sheets
    Set Sh = Wb.Sheets("report")
 
    'VBA code writes and formats to Google Sheets like Excel
    Sh.Cells.Clear
    Sh.Range("A1").Value = "VBA to Google Sheets"
    Sh.Range("A1").Font.Size = 20
    Sh.Range("A1").Font.Bold = True
    Sh.Range("A2:A6").Value = WorksheetFunction.Transpose( _
                                Array(10000, 20000, 15000, 40000, 30000) _
                                )
    Sh.Range("C2:C6").Formula = "=A2*0.1"
    Sh.Range("A2:A6,C2:C6").Font.Color = RGB(255, 0, 0)
    Sh.Range("A2:A6,C2:C6").Interior.Color = vbYellow
    Sh.Range("A2:A6,C2:C6").Borders.Color = vbBlack
    Sh.Range("A2:A6,C2:C6").HorizontalAlignment = XlHAlign.xlHAlignCenter
    Sh.Range("A2:A6,C2:C6").VerticalAlignment = xlCenter
    Sh.Range("A2:A6,C2:C6").NumberFormat = "#,##0"
    Sh.Range("A2:A6").Font.Size = 15
    Sh.Range("A2:A6").Font.Color = vbBlue
    Sh.Range("C2:C6").Interior.Color = vbGreen
 
lbEnd:
    If Err <> 0 Then
        Debug.Print "Error: " & Err.Description
    End If
    Set Sh = Nothing
    Set Wb = Nothing
    Set MyCloud = Nothing
End Sub

Kết quả ta nhận được là (với các nền tảng bảng tính khác cũng tương tự)

​

Tăng tốc độ xử lý bảng tính online

Một vấn đề rất quan trọng khi bạn làm việc với bảng tính online đó là tốc độ. Vì mọi lệnh đều chạy trên internet nên tốc độ sẽ chậm hơn so với làm trực tiếp tại máy - local. Add-in A-Tools cung cấp thủ tục để giải quyết việc này rất dễ dàng, tốc độ cập nhật lên bảng tính online rất nhanh, bạn nên ứng dụng cách thức dưới đây cho ứng dụng của mình.

Để đạt tốc độ xử lý nhanh nhất bạn hãy đảm bảo viết code theo cấu trúc dưới đây

ParentObject.BeginUpdate 'Đặt trạng thái đồng bộ hàng loạt

'Các lệnh cập nhật dữ liệu và định dạng

ParentObject.EndUpdate(Async) 'Sync all - Đồng bộ tất cả.

ParentObject: có thể là BSCloudWorksheet, BSCloudWorkbook, BSFont, BSBorder.
EndUpdate(Async) : Async nếu là True (ngầm định) thì ứng dụng không phải chờ thực hiện xong. Nếu là False thì ứng dụng phải chờ đến khi các lệnh thực hiện xong. Bạn cần dùng tham số Async là False trong trường hợp bắt buộc lệnh trước phải hoàn thành mới thực hiện lệnh kế tiếp.

Bây giờ tôi sẽ sửa ví dụ GoogleSheet_Write để tốc độ chạy nhanh nhất như sau. Copy toàn bộ code thủ tục GoogleSheet_Write, sau đó chỉ cần thêm BeginUpdate ở đầu và EndUpdate ở cuối như sau:

Sub GoogleSheet_Write_VeryFast()
    'Declare and connect to Google Drive
    Const FileID = "https://docs.google.com/spreadsheets/d/1-o__30yfKSTRAB3vrqEo6fCnMQSXHEN8ImtE4hca4CA/edit?" 'Google Sheets
    Dim MyCloud As New BSCloud
    Dim Wb As BSCloudWorkbook, Sh As BSCloudWorksheet
 
    On Error GoTo lbEnd
    'Check connection
    If Not MyCloud.Connected(ctGoogleDrive) Then
        If Not MyCloud.OpenAuthor(Application, ctGoogleDrive, Application.Hwnd) Then
            Exit Sub
        End If
    End If
    '-------------------------------
    Set Wb = MyCloud.Workbooks.Open(FileID) 'Open Goolge Sheets
    Set Sh = Wb.Sheets("report")
 
    'VBA code writes and formats to Google Sheets like Excel
    Sh.Cells.Clear 'Should run before BeginUpdate
    Sh.BeginUpdate 'Remember run EndUpdate()
 
    Sh.Range("A1").Value = "VBA to Google Sheets"
    Sh.Range("A1").Font.Size = 20
    Sh.Range("A1").Font.Bold = True
    Sh.Range("A2:A6").Value = WorksheetFunction.Transpose( _
                                Array(10000, 20000, 15000, 40000, 30000) _
                                )
    Sh.Range("C2:C6").Formula = "=A2*0.1"
    Sh.Range("A2:A6,C2:C6").Font.Color = RGB(255, 0, 0)
    Sh.Range("A2:A6,C2:C6").Interior.Color = vbYellow
    Sh.Range("A2:A6,C2:C6").Borders.Color = vbBlack
    Sh.Range("A2:A6,C2:C6").HorizontalAlignment = XlHAlign.xlHAlignCenter
    Sh.Range("A2:A6,C2:C6").VerticalAlignment = xlCenter
    Sh.Range("A2:A6,C2:C6").NumberFormat = "#,##0"
    Sh.Range("A2:A6").Font.Size = 15
    Sh.Range("A2:A6").Font.Color = vbBlue
    Sh.Range("C2:C6").Interior.Color = vbGreen
 
    Sh.EndUpdate  'Sync all
lbEnd:
    If Err <> 0 Then
        Debug.Print "Error: " & Err.Description
    End If
    Set Sh = Nothing
    Set Wb = Nothing
    Set MyCloud = Nothing
End Sub

Tôi nhấn mạnh lần nữa, Google Sheets và Excel Online dùng chung code. Trong bài viết tôi thường ví dụ với Google Sheets.

Ghi dữ liệu vào dòng cuối của sheet

Đây là nội dung rất quan trọng nếu các bạn muốn lập trình để nhiều người cùng nhập liệu và đẩy dữ liệu vào một sheet trên cloud mà không ghi đè vào nhau. Cách thức làm việc này các nền tảng sẽ khác nhau:
+ Excel (chạy local): bạn dùng Range.End(xlUp).Row để xác định dòng cuối.
+ Excel Online (trên OneDrive) chúng ta sẽ dùng đối tượng BSCloudTable
+ Google Sheets (trên Google Drive) chúng ta dùng hàm Append().

Tôi sẽ trình bày lần lượt cách làm với từng nền tảng một (không trình bày với Excel vì đã quá quen thuộc).

Ghi vào dòng cuối trong Google Sheets

Chúng ta có hai hàm Append ở hai đối tượng BSCloudWorksheet và BSCloudRange. Hàm Append dùng để ghi giá trị hay mảng giá trị vào dòng cuối của sheet hay vùng dữ liệu (nếu gọi từ BSCloudRange).

Function BSCloudWorksheet.Append(Value, Response As BSUpdateResponse) As Boolean
Function BSCloudRange.Append(Value,
[ValueInputOption As BSValueInputOption = vioUSER_ENTERED],
[InsertDataOption As BSInsertDataOption = idoINSERT_ROWS],
[IncludeValuesInResponse As Boolean = False],
Response As BSUpdateResponse) As Boolean

Tham số Response kiểu BSUpdateResponse sẽ nhận thông tin về dữ liệu và vùng dữ liệu được đẩy lên. Tham số này chỉ nhận đủ giá trị nếu dùng hàm Append trong BSCloudRange. Tham số này sẽ rất cần thiết nếu bạn cần biết địa chỉ vùng giá trí trị mà bạn đã đẩy lên là địa chỉ nào, dữ liệu trên đó được cập nhật như thế nào.

Ví dụ ghi 1000 dòng dữ liệu lên sheet

Sub GoogleSheet_Append_1000_Rows()
    'Khai bao va ket noi Google Drive
    Const FileID = "https://docs.google.com/spreadsheets/d/1-o__30yfKSTRAB3vrqEo6fCnMQSXHEN8ImtE4hca4CA/edit?"
    Dim MyCloud As New BSCloud, res As BSUpdateResponse
    Dim Wb As BSCloudWorkbook, Sh As BSCloudWorksheet, I&, T
 
    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
    '-------------------------------
    'Code VBA ghi va dinh dang len Google Sheets giong het Excel
    Set Wb = MyCloud.Workbooks.Open(FileID) 'Open Goolge Sheets
    Set Sh = Wb.Sheets("report")
    'Add values to 3 columns per row
 
    Sh.Cells.Clear 'Should run before BeginUpdate
    T = Timer
    Sh.BeginUpdate
 
    For I = 1 To 1000
        Sh.Append Array("HH00" & I, 15000, Now), res
    Next I
 
    Sh.EndUpdate
 
    MsgBox "Time spent: " & (Timer - T) / 1000 & " seconds.", vbInformation
 
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ụ trên dùng BeginUpdate, EndUpdate nên tốc độ ghi lên Google Sheets rất nhanh. Bạn cần thực hành ví dụ này để hiểu bản chất vấn đề hơn.

Ví dụ tiếp theo tôi ghi các mảng giá trị nhiều dòng , nhiều cột ghi cùng một lúc vào dòng cuối của Google Sheets.

'Sh.Append() for Google Sheets only
Sub GoogleSheet_AppendRows()
    'Khai bao va ket noi Google Drive
    Const FileID = "https://docs.google.com/spreadsheets/d/1-o__30yfKSTRAB3vrqEo6fCnMQSXHEN8ImtE4hca4CA/edit?"
    Dim MyCloud As New BSCloud, res As BSUpdateResponse
    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
    '-------------------------------
    'Code VBA ghi va dinh dang len Google Sheets giong het Excel
    Set Wb = MyCloud.Workbooks.Open(FileID) 'Open Goolge Sheets
    Set Sh = Wb.Sheets("report")
    'Add values to 3 columns per row
 
    Sh.Cells.Clear
    'Write row by row
    Sh.Append Array("HH001", 15000, Now), res
    Sh.Append Array("HH002", 20000, Now), res
 
    'If use Append from Range then res get information
    'Sh.Range("A1:C1").Append Array("HH002", 20000, Now), res
 
    'Write multiple rows at once
    Dim Values() 'Dynamic array
    ReDim Values(1 To 3)
    Values(1) = Array("HH003", 14000, Now)
    Values(2) = Array("HH004", 18000, Now)
    Values(3) = Array("HH005", 20000, Now)
    'Append multi rows
    Sh.Append Values, res
 
    'Write multiple rows at once with array 2D
    ReDim Values(1 To 2, 1 To 3)
    Values(1, 1) = "HH006": Values(1, 2) = 8000: Values(1, 3) = Now
    Values(2, 1) = "HH007": Values(2, 2) = 5000: Values(2, 3) = Now
    'Append array 2D (rows, columns)
    Sh.Append Values, res
 
lbEnd:
    If Err <> 0 Then
        Debug.Print "Error: " & Err.Description
    End If
    Set Sh = Nothing
    Set Wb = Nothing
    Set MyCloud = Nothing
End Sub

Kết quả bạn nhận được như dưới đây:

​

Ví dụ dưới đây tôi ghi mảng giá trị gồm 3 cột vào dòng cuối của vùng cột A:C. Dựa vào vùng cơ sở A1:C1 để tìm dòng cuối. Sau khi xong thì lấy thông tin trong biến res để biết địa chỉ đã cập nhật dữ liệu, tìm tọa độ dòng cuối với hàm GetLastRow().

Sub GoogleSheet_Append_From_Range()
    'Khai bao va ket noi Google Drive
    Const FileID = "https://docs.google.com/spreadsheets/d/1-o__30yfKSTRAB3vrqEo6fCnMQSXHEN8ImtE4hca4CA/edit?"
    Dim MyCloud As New BSCloud, res As BSUpdateResponse
    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 Goolge Sheets
    Set Sh = Wb.Sheets("report")
    'Add values to 3 columns per row
 
    Sh.Range("A1:C1").Append Array("HH001", 15000, Now), res
 
    MsgBox "The last row is appended: " & GetLastRow(res.Updates.UpdatedRange), vbInformation
    'To known the last row, not run append. Sh.LastRow("BaseAddress")
lbEnd:
    If Err <> 0 Then
        Debug.Print "Error: " & Err.Description
    End If
    Set Sh = Nothing
    Set Wb = Nothing
    Set MyCloud = Nothing
End Sub

Các bài viết bổ sung và cập nhật mới nhất tôi tập hợp tại đây

Còn tiếp...

 

[PhanTuHuong]

Chạy theo khai thác triệt để A-Tools của chú chắc cũng mệt!

 

[Excelvp]

Mình cứ thắc mắc, học được lập trình ở mức độ phức tạp thế này, người dùng sao không chủ động ngôn ngữ lập trình của mình, mà phải phụ thuộc vào nền tảng trả phí, mà người thường thì đọc chắc sẽ không hiểu được, vậy đối tượng người học là ai

 

[PhanTuHuong]

Mình cứ thắc mắc, học được lập trình ở mức độ phức tạp thế này, người dùng sao không chủ động ngôn ngữ lập trình của mình, mà phải phụ thuộc vào nền tảng trả phí, mà người thường thì đọc chắc sẽ không hiểu được, vậy đối tượng người học là ai

Nếu vậy tôi cũng thắc mắc sao nhiều người giỏi lập trình mà không tự xây dựng môi trường làm việc như Windows hoặc Office? Người mua họ bỏ tiền ra sẽ biết tiêu như thế nào!

 

[ongke0711]

Mình cứ thắc mắc, học được lập trình ở mức độ phức tạp thế này, người dùng sao không chủ động ngôn ngữ lập trình của mình, mà phải phụ thuộc vào nền tảng trả phí, mà người thường thì đọc chắc sẽ không hiểu được, vậy đối tượng người học là ai

Viết code để sử dụng tool có sẳn mà bạn thấy nó phức tạp rồi thì biết khi nào mới lập trình ra được cái tool đó để khỏi trả phí...Chờ lúc viết được chắc đổi công việc rồi hoặc mất 1, 2 năm thì lúc đó công nghệ thay đổi, người ta ra tới cái gì rồi mà mình thì mới biết viết được cái tool cũ .
Code này chỉ để cho người lập trình tích hợp vào ứng dụng của họ chứ đâu phải cho người dùng cuối đâu bạn. Trong lập trình thì lạ gì chuyện dùng thư viện của bên. thứ 3.

 

[Nguyễn Duy Tuân]

Chạy theo khai thác triệt để A-Tools của chú chắc cũng mệt!

Chạy theo luyện bóng bàn để lấy beer của anh em thấy đang mệt đây