Tiếp theo phần 01.
Nếu các bạn chưa đọc phần 01 thì bấm vào đây để đọc từ đầu.
Trong 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, định dạng, ghi dữ liệu vào dòng cuối của sheet với 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 bảng tính online (Google Sheets, Excel Online).
(Video 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ả nhận được như sau
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
Mời các bạn đọc tiếp:
Download Add-in A-Tools để thực hành
Tác giả Nguyễn Duy Tuân