Cách sử dụng workbook trong VBA là như thế nào? Lợi ích của việc sử dụng workbook? Ngay sau đây, Học Excel Online sẽ cung cấp hướng dẫn đầy đủ để sử dụng đối tượng workbook trong VBA.
Bảng hướng dẫn nhanh về workbook trong VBA
| Tác vụ | Cách thực hiện |
| Truy cập workbook đang mở bằng tên | Workbooks(“Example.xlsx”) |
| Truy cập workbook đang mở (sổ được mở đầu tiên) | Workbooks(1) |
| Truy cập workbook đang mở (sổ được mở sau cùng) | Workbooks(Workbooks.Count) |
| Truy cập workbook đang hoạt động | ActiveWorkbook |
| Truy cập workbook có chứa mã VBA | ThisWorkbook |
| Khai báo một biến workbook | Dim wk As Workbook |
| Gán một biến workbook | Set wk = Workbooks ( “Example.xlsx” ) Set wk = ThisWorkbook Set wk = Workbooks (1) |
| Kích hoạt workbook | wk.Activate |
| Đóng workbook mà không lưu | wk.C Close SaveChanges: = False |
| Đóng workbook và lưu | wk.C Close SaveChanges: = True |
| Tạo workbook mới | Set wk = Workbooks.Add |
| Mở workbook | Set wk = Workbooks.Open
( “C:Docs Example.xlsx” ) |
| Mở workbook ở dạng chỉ đọc | Setwk=Workbooks.Open (“C:DocsExample.xlsx”, ReadOnly:=True) |
| Kiểm tra workbook tồn tại | If Dir(“C:Docsbook1.xlsx”) = “” Then
MsgBox “File does not exist.” EndIf |
| Kiểm tra workbook đang mở | Xem phần bên dưới trong bài |
| Liệt kê tất cả các workbook đang mở | For Each wk In Application.Workbooks
Debug.Print wk.FullName Next wk |
| Mở workbook bằng Hộp thoại Tệp | Xem phần này ở nội dung bên dưới |
| Lưu workbook | wk.Save |
| Lưu bản sao workbook | wk.SaveCopyAs “C:Copy.xlsm” |
| Sao chép workbook nếu đã đóng | FileCopy “C:file1.xlsx” , “C:Copy.xlsx” |
| SaveAs Workbook | wk.SaveAs “Backup.xlsx” |
Bắt đầu với đối tượng Workbook trong VBA
Chúng ta có thể truy cập bất kỳ workbook bằng cách sử dụng mã Workbooks (“Example.xlsm”), chỉ cần thay thế Example.xlsm bằng tên của workbook mà bạn muốn sử dụng.
Ví dụ sau đây hướng dẫn cách ghi dữ liệu worksheet trong VBA, chúng ta phải chỉ định workbook, worksheet và vùng dữ liệu.
' https://excelmacromastery.com/
Public Sub WriteToA1()
' Writes 100 to cell A1 of worksheet "Sheet1" in MyVBA.xlsm
Workbooks("MyVBA.xlsm").Worksheets("Sheet1").Range("A1") = 100
End Sub
Phần đầu tiên là workbook, phần thứ hai là worksheet và phần thứ 3 là range (vùng dữ liệu). Dưới đây là ví dụ khác cách ghi vào ô:
Chúng ta có thể ghi vào bất kỳ ô nào trong bất kỳ worksheet từ workbook. Bạn chỉ cần thay đổi tên workbook, tên worksheet và vùng dữ liệu phù hợp với yêu cầu của mình,
' https://excelmacromastery.com/
Public Sub WriteToMulti()
' Writes 100 to cell A1 of worksheet "Sheet1" in MyVBA.xlsm
Workbooks("MyVBA.xlsm").Worksheets("Sheet1").Range("A1") = 100
' Writes "John" to cell B1 of worksheet "Sheet1" in MyVBA.xlsm
Workbooks("MyVBA.xlsm").Worksheets("Sheet1").Range("B1") = "John"
' Writes 100 to cell A1 of worksheet "Accounts" in MyVBA.xlsm
Workbooks("MyVBA.xlsm").Worksheets("Accounts").Range("A1") = 100
' Writes the date to cell D3 of worksheet "Sheet2" in Book.xlsc
Workbooks("Book.xlsx").Worksheets("Sheet2").Range("D3") = "116"
End Sub
Xem xét workbook sau đây:
Workbooks("Example.xlsx")
Workbooks là bộ sưu tập tất cả các workbook. Để truy cập vào workbook bạn phải cung cấp tên workbook cho bộ sưu tập workbooks. Khi xác định đối tượng chúng ta sử dụng nó để thực hiện các tác vụ với workbook.
Khắc phục sự cố bộ sưu tập workbooks
Khi bạn sử dụng bộ sưu tập workbooks để truy cập workbook có thể sẽ xuất hiện lỗi.
Run-time Error 9: Subscript out of Range.
Điều này có nghĩa VBA không tìm thấy workbook bạn đã truyền dưới dạng tham số. Có thể vì các lý do sau:
- Workbook có vấn đề.
- Tên không đúng.
- Bạn tạo workbook làm việc mới (ví dụ: Book1) nhưng quên lưu tên và sau đó cố gắng truy cập nó bằng workbooks.(“Book1.xlsx”) .
- (Dành cho Excel 2007/2010). Nếu bạn chạy hai phiên bản Excel thì Workbooks() chỉ tham chiếu đến các workbook đang mở hiện tại.
- Bạn truyền một số để làm Index(chỉ mục) cho các workbook và nó lớn hơn số lượng workbook đang mở, ví dụ: bạn sử dụng workbook(3) nhưng thực tế chỉ có 2 workbook đang mở.
Nếu không thể xử lý lỗi hãy sử dụng các chức năng trong phần “Finding all open workbooks” ( tìm tất workbook trong bộ sưu tập workbooks) và chúng sẽ xuất tên của tất cả các workbook đang mở vào Immediate window (Ctrl+G).
Các ví dụ sử dụng workbook trong VBA
Lưu ý: để thử ví dụ này, chúng ta sẽ tạo hai workbook có tên là Test.xlsx và Test2.xlsx.
' https://excelmacromastery.com/
Public Sub WorkbookProperties()
' Prints the number of open workbooks
Debug.Print Workbooks.Count
' Prints the full workbook name
Debug.Print Workbooks("Test1.xlsx").FullName
' Displays the full workbook name in a message dialog
MsgBox Workbooks("Test1.xlsx").FullName
' Prints the number of worksheets in Test2.xlsx
Debug.Print Workbooks("Test2.xlsx").Worksheets.Count
' Prints the name of currently active sheet of Test2.xlsx
Debug.Print Workbooks("Test2.xlsx").ActiveSheet.Name
' Closes workbook called Test1.xlsx
Workbooks("Test1.xlsx").Close
' Closes workbook Test2.xlsx and saves changes
Workbooks("Test2.xlsx").Close SaveChanges:=True
End Sub
Chú ý: trong ví dụ trên Debug.Print sử dụng tương đối nhiều. Hàm này sẽ xuất các giá trị vào Immediate window. Để xem Immediate window chọn View–>Immediate window từ menu (phím tắt là Ctrl+G).
Truy cập Workbook bằng index (chỉ mục)
Chúng có thể sử dụng số Index với worbooks(). Index đề cập đến số thứ tự workbook.
Workbook (1) đề cập đến workbook làm việc dược mở đầu tiên, workbooks (2) đề cập đến workbook được mở thứ 2 và tiếp tục như vậy.
' First workbook that was opened Debug.Print Workbooks(1).Name ' Third workbook that was opened Debug.Print Workbooks(3).Name ' The last workbook that was opened Debug.Print Workbooks(Workbooks.Count).Name
Trong ví dụ này chúng ta sử dụng workbooks.count, cái này có nghĩa là số lượng workbook hiện có trong bộ sưu tập workbooks. Sử dụng Index không thực sự hữu ích trừ khi bạn cần đến thứ tự. Thay vào đó, sử dụng workbook với workbooks() sẽ hiệu quả hơn.
Truy cập tất cả các workbook đang mở
Nếu bạn muốn truy cập tất cả các workbook đang mở hay nói cách khác các mục trong bộ sưu tập workbooks()thì có thể thực hiện bằng cách sử dụng For each Loop.
Chúng ta cũng có thể sử dụng For Loop:
' https://excelmacromastery.com/
Public Sub PrintWrkFileName()
' Prints out the full filename of all open workbooks
Dim wrk As Workbook
For Each wrk In Workbooks
Debug.Print wrk.FullName
Next wrk
End Sub
Để truy cập tất cả Workbook thì có thể chọn một trong hai vòng lặp trên. For Loop sẽ hữu ích nếu bạn muốn sử dụng thứ tự hoặc bộ đếm.
' https://excelmacromastery.com/
Public Sub PrintWrkFileNameIdx()
' Prints out the full filename of all open workbooks
Dim i As Long
For i = 1 To Workbooks.Count
Debug.Print Workbooks(i).FullName
Next i
End Sub
Lưu ý: cả hai ví dụ đều đọc theo thứ từ từ lần truy cập đầu tiên đến lần truy cập cuối cùng. Nếu muốn đọc theo thứ tự ngược lại thì làm như sau:
' https://excelmacromastery.com/
Public Sub PrintWrkFileNameIdxRev()
' Prints out the full filename of all open workbooks
' in reverse order.
Dim i As Long
For i = Workbooks.Count To 1 Step -1
Debug.Print Workbooks(i).FullName
Next i
End Sub
Mở workbook
Chúng ta có thể mở workbook bằng các cách thủ công như trên đây trước khi chạy Macro nhưng sẽ hữu ích hơn nêu mở workbook một cách tự động thao tác bằng VBA.
Mã VBA sẽ mở Workbook “Book1.xlsm” trong thư mục “C: Docs”
' https://excelmacromastery.com/
Public Sub OpenWrk()
' Open the workbook and print the number of sheets it contains
Workbooks.Open ("C:DocsBook1.xlsm")
Debug.Print Workbooks("Book1.xlsm").Worksheets.Count
' Close the workbook without saving
Workbooks("Book1.xlsm").Close saveChanges:=False
End Sub
Bạn sẽ kiểm tra workbook có tồn tại hay không, vì vậy giúp bạn tránh được lỗi. Các hàm Dir hỗ trợ bạn thực hiện điều này:
' https://excelmacromastery.com/
Public Sub OpenWrkDir()
If Dir("C:DocsBook1.xlsm") = "" Then
' File does not exist - inform user
MsgBox "Could not open the workbook. Please check it exists"
Else
' open workbook and do something with it
Workbooks.Open("C:DocsBook1.xlsm")
End If
End Sub
Kiểm tra Open workbook
Nếu bạn mở workbook dưới dạng chỉ đọc, thì không thành vấn đề nếu workbook đã mở. Tuy nhiên nếu muốn cập nhật dữ liệu trong workbook thì bạn phải kiểm tra nó được mở hay chưa.
Hàm dưới đây có thể kiểm tra xem workbook mở hay chưa, nếu chưa nó sẽ mở workbook.
' https://excelmacromastery.com/
Function GetWorkbook(ByVal sFullFilename As String) As Workbook
Dim sFilename As String
sFilename = Dir(sFullFilename)
On Error Resume Next
Dim wk As Workbook
Set wk = Workbooks(sFilename)
If wk Is Nothing Then
Set wk = Workbooks.Open(sFullFilename)
End If
On Error Goto 0
Set GetWorkbook = wk
End Function
Bạn có thể sử dụng như thế này:
' https://excelmacromastery.com/
Sub ExampleOpenWorkbook()
Dim sFilename As String
sFilename = "C:DocsBook2.xlsx"
Dim wk As Workbook
Set wk = GetWorkbook(sFilename)
End Sub
Mã nãy có thể giải quyết tốt tình huống trên. Tuy nhiên nếu workbook hiện đang mở ở chế độ chỉ đọc hoặc được mở bởi người dùng khác thì bạn có thể xử lý tình huống này như cách dưới đây.
Ta có thể sử dụng hàm dưới đây để kiểm tra tệp đã được mở hay chưa và nếu có thì thông báo cho người dùng đóng lại để macro chạy thành công.
' https://excelmacromastery.com/
' Function to check if workbook is already open
Function IsWorkBookOpen(strBookName As String) As Boolean
Dim oBk As Workbook
On Error Resume Next
Set oBk = Workbooks(strBookName)
On Error GoTo 0
If Not oBk Is Nothing Then
IsWorkBookOpen = True
End If
End Function
Dưới đây là ví dụ về việc sử dụng chức năng này. Trong trường hợp nếu workbook đã được mở thì bạn phải thông báo người dùng phải đóng để macro tiếp tục chạy.
' https://excelmacromastery.com/
Sub ExampleUse()
Dim sFilename As String
sFilename = "C:tempwritedata.xlsx"
If IsWorkBookOpen(Dir(sFilename)) = True Then
MsgBox "File is already open. Please close file and run macro again."
Exit Sub
End If
' Write to workbook here
End Sub
Nếu bạn cần kiểm tra workbook có đang mở trong phiên bản excel khác thì có thể sử dụng thuộc tính Readonly của workbook. Nó sẽ đặt thành True nếu mở trong các hoàn cảnh khác.
Close workbook (đóng workbook)
Để đóng workbook trong VBA khá đơn giản, chỉ cần dùng lệnh Close của workbook.
wk.Close
Thông thường khi đóng một workbook trong VBA và không muốn thấy thông báo có muốn lưu tệp từ Excel. Bạn có thể chỉ định cho workbook có lưu hay không, sau đó thông báo của excel không xuất hiện nữa:
' Don't save changes wk.Close SaveChanges:= False ' Do save changes wk.Close SaveChanges:= True
Ta không thể lưu các thay đổi đối với workbook đang mở dưới dạng chỉ đọc.
Lưu workbook
Ta có thể lưu workbook khi muốn đóng nó, còn nếu muốn lưu ở bất kỳ giai đoạn nào chỉ cần sử dụng phương pháp như sau:
wk.Save
Ta cũng có thể sử dụng phương pháp SaveAs:
wk.SaveAs "C:Backupsaccounts.xlsx"
Phương thức workbook SaveAs cho phép bạn thêm mật khẩu với 12 tham số, đặt tệp ở chế độ chỉ đọc…
Ngoài ra cũng có thể sử dụng VBA để lưu workbook dưới dạng bản sao bằng SaveCopyAs.
wk.SaveCopyAs "C:DocsCopy.xlsm"
Sao chép workbook
Nếu workbook đang mở, chúng ta có thể sử dụng hai phương pháp trên để tạo bản sao tức là SaveAs và SaveCopyAs.
Nếu bạn muốn sao chép một workbook mà không cần mở thì có thể sử dụng FileCopy như ví dụ sau:
Public Sub CopyWorkbook()
FileCopy "C:DocsDocs.xlsm", "C:DocsExample_Copy.xlsm"
End Sub
Sử dụng hộp thoại File để mở Workbook
Ngoài những cách mở Workbook như đề cập ở tên thì có thể sử dụng hộp thoại File để mở Workbook. Sử dụng cách này nếu bạn muốn người dùng chọn workbook. Có thể dễ dàng sử dụng Windows File Dialog như dưới đây:
FileDialog có thể định cấu hình và sử dụng nó để:
- Chọn một tập tin.
- Chọn một thư mục.
- Mở tệp.
- “Save as” lưu dưới dạng tệp.
Nếu bạn chỉ muốn người dùng chọn tệp có thể sử dụng chức năng GetOpenFileName.
Hàm sau sẽ mở một workbook làm việc bằng File Dialog, hàm sẽ trả về tên tệp đầy đủ nếu tệp được chọn. Nếu ngwoif dùng hủy sẽ hiển thị một thông báo và trả về chuỗi trống.
' https://excelmacromastery.com/
Public Function UserSelectWorkbook() As String
On Error Goto ErrorHandler
Dim sWorkbookName As String
Dim FD As FileDialog
Set FD = Application.FileDialog(msoFileDialogFilePicker)
' Open the file dialog
With FD
' Set Dialog Title
.Title = "Please Select File"
' Add filter
.Filters.Add "Excel Files", "*.xls;*.xlsx;*.xlsm"
' Allow selection of one file only
.AllowMultiSelect = False
' Display dialog
.Show
If .SelectedItems.Count > 0 Then
UserSelectWorkbook = .SelectedItems(1)
Else
MsgBox "Selecting a file has been cancelled. "
UserSelectWorkbook = ""
End If
End With
' Clean up
Set FD = Nothing
Done:
Exit Function
ErrorHandler:
MsgBox "Error: " + Err.Description
End Function
Khi bạn gọi hàm này, bạn phải kiểm tra xem người dùng có hủy hộp thoại hay không. Ví dụ sau đây cho bạn thấy cách dễ dàng gọi hàm UserSelectWorkbook và xử lý trường hợp người dùng hủy.
' https://excelmacromastery.com/
Public Sub TestUserSelect()
Dim userBook As Workbook, sFilename As String
' Call the UserSelectworkbook function
sFilename = UserSelectWorkbook()
' If the filename returns is blank the user cancelled
If sFilename <> "" Then
' Open workbook and do something with it
Set userBook = Workbooks.Open(sFilename)
End If
End Sub
Ta có thể tùy chỉnh hộp thoại bằng cách thay đổi tiêu đề, bộ lọc và AllowMultiSelect trong hàm UserSelectWorkbook .
' https://excelmacromastery.com/
Public Sub WriteToCellUsingThis()
' Both lines do the same thing.
Debug.Print ThisWorkbook.FullName
Debug.Print Workbooks("MyVBA.xlsm").FullName
End Sub
Sử dụng Thisworkbook
Có một cách dễ dàng hơn để truy cập workbook hiện tại thay vì sử dụng Workbooks (). Đó là sử dụng từ khóa ThisWorkbook . Nó đề cập đến workbook hiện tại tức là workbook có chứa mã VBA.
Nếu mã của chúng tôi trong workbook gọi MyVBA.xlsm thì ThisWorkbook và Workbooks (“MyVBA.xlsm”) tham chiếu đến cùng một workbook.
Sử dụng ThisWorkbook hữu ích hơn sử dụng Workbooks () . Với ThisWorkbook không cần phải lo lắng về tên của tệp.
- Thay đổi tên tệp sẽ không ảnh hưởng đến mã
- Sao chép mã sang một workbook khác không yêu cầu thay đổi mã.
Thực tế là tên tệp của bạn sẽ luôn thay đổi. Sử dụng ThisWorkbook có nghĩa là mã của bạn sẽ vẫn hoạt động tốt.
Ví dụ sau đây cho thấy hai dòng mã. Một sử dụng ThisWorkbook và một sử dụng Workbooks (). Workbooks sẽ không còn hoạt động nếu tên của MyVBA.xlsm thay đổi.
Sử dụng ActiveWorkbook
ActiveWorkbook đề cập đến workbook hiện đang hoạt động. Phương pháp này chỉ hữu ích lúc đâu. Vấn đề là bất kỳ workbook nào cũng có thể hoạt động chỉ bằng một cú nhấp chuột đơn giản. Vì thế mà có thể dẫn đến viết dữ liệu vào sai workbook.
Ngoài ra, sử dụng ActiveWorkbook làm cho mã khó đọc. Mã có thể không phân biệt workbook nào phải là workbook active.
Vì thể tránh sử dụng ActiveWorkbook trừ bắt buộc phải làm vậy.
Các ví dụ về truy cập workbook
Chúng ta đã xem xét tất cả các cách truy cập workbook. Đoạn mã sau đây cho thấy các ví dụ về những cách này:
' https://excelmacromastery.com/
Public Sub WorkbooksUse()
' This is a workbook that is already open and called MyVBA.xlsm
Debug.Print Workbooks("MyVBA.xlsm").FullName
' The workbook that contains this code
Debug.Print ThisWorkbook.FullName
' The open workbook that was opened first
Debug.Print Workbooks(1).FullName
' The open workbook that was opened last
Debug.Print Workbooks(Workbooks.Count).FullName
' The workbook that is the currently active one
Debug.Print ActiveWorkbook.FullName
' No workbook mentioned - the active one will be used
Debug.Print Worksheets("Sheet1").Name
' A closed workbook called Book1.xlsm in folder C:Docs
Workbooks.Open ("C:DocsBook1.xlsm")
Debug.Print Workbooks("Book1.xlsm").FullName
Workbooks("Book1.xlsm").Close
End Sub
Khai báo biến workbook
Mục đích khai báo biến workbook để làm cho mã dễ đọc và dễ hiểu hơn.
' https://excelmacromastery.com/
Public Sub OpenWrkObjects()
Dim wrk As Workbook
Set wrk = Workbooks.Open("C:DocsBook1.xlsm")
' Print number of sheets in each book
Debug.Print wrk.Worksheets.Count
Debug.Print wrk.Name
wrk.Close
End Sub
Chúng ta có thể đặt biến workbook bất kỳ bằng các phương pháp truy cập mà chúng ta đã tìm hiểu.
Dưới đây cho thấy cùng một mã nhưng không có biến workbook:
' https://excelmacromastery.com/
Public Sub OpenWrkNoObjects()
Workbooks.Open ("C:DocsBook1.xlsm")
Debug.Print Workbooks("Book2.xlsm").Worksheets.Count
Debug.Print Workbooks("Book2.xlsm").Name
Workbooks("Book2.xlsm").Close
End Sub
Trong những ví dụ này, không có quá nhiều khác biệt. Tuy nhiên, khi bạn có nhiều mã, việc sử dụng biến sẽ hữu ích đặc biệt cho worksheet và vùng dữ liệu khi tên dài, ví dụ: thisWorkbook.Worksheets (“Sheet1”). Range (“A1”) .
Bạn có thể đặt tên biến workbook giống như wrkRead hoặc wrkWrite để nhanh chóng thấy được chức năng của nó.
Tạo một workbook mới
Để tạo một workbook mới ta sử dụng hàm Add. Hàm này tạo ra một workbook trống mới, điều này cũng tương tự như chọn workbook mới từ menu tệp của Excel.
Khi tạo một workbook làm việc mới ta thường sẽ lưu nó. Đoạn mã dưới đây sẽ minh họa cách thực hiện.
' https://excelmacromastery.com/
Public Sub AddWordbook()
Dim wrk As Workbook
Set wrk = Workbooks.Add
' Save as xlsx. This is the default.
wrk.SaveAs "C:TempExample.xlsx"
' Save as a Macro enabled workbook
wrk.SaveAs "C:TempExample.xlsm", xlOpenXMLWorkbookMacroEnabled
End Sub
Khi bạn tạo một workbook mới, nó thường chứa ba worksheet. Điều này được xác định bởi thuộc tính Application.SheetsInNewWorkbook.
Nếu bạn muốn có một số worksheet khác trong workbook mới thì bạn thay đổi thuộc tính này trước khi tạo workbook mới. Ví dụ sau đây cho bạn thấy cách tạo một workbook mới với bảy worksheet.
' https://excelmacromastery.com/
Public Sub AddWordbookMultiSheets()
' Store SheetsInNewWorkbook value so we can reset it later
Dim sheetCnt As Long
sheetCnt = Application.SheetsInNewWorkbook
' Set sheets in a new workbook to be 7
Application.SheetsInNewWorkbook = 7
' Workbook will be created with 7 sheets
Dim wrk As Workbook
Set wrk = Workbooks.Add
' Display sheet count
Debug.Print "number of sheets: " & CStr(wrk.Worksheets.Count)
' Reset to original value
Application.SheetsInNewWorkbook = sheetCnt
End Sub
Sử dụng WITH với workbook
Lệnh WITH làm cho việc đọc và viết mã VBA dễ dàng hơn. Sử dụng With có ý nghĩa bạn chỉ cần đề cập đối tượng một lần.
Ví dụ sau có hai sub. Đoạn đầu tiên như các mã bình thường.
Đoạn thứ thứ hai sử dụng từ khóa With. điều này giúp mã rõ ràng hơn. Các từ khóa End With đánh dấu sự kết thúc của một mã phần bằng cách sử dụng With.
' https://excelmacromastery.com/
' Not using the With keyword
Public Sub NoUsingWith()
Debug.Print Workbooks("Book2.xlsm").Worksheets.Count
Debug.Print Workbooks("Book2.xlsm").Name
Debug.Print Workbooks("Book2.xlsm").Worksheets(1).Range("A1")
Workbooks("Book2.xlsm").Close
End Sub
' Using With makes the code easier to read
Public Sub UsingWith()
With Workbooks("Book2.xlsm")
Debug.Print .Worksheets.Count
Debug.Print .Name
Debug.Print .Worksheets(1).Range("A1")
.Close
End With
End Sub.
(Bài viết được đăng bởi chức năng tự động lấy tin của website)
[ad_2]