Tổng hợp các code VBA Excel thao tác với Workbook chỉ trong nháy mắt

Nếu bạn đang tìm kiếm cách rút ngắn thời gian thao tác với workbook Excel của mình thì VBA Excel chính là công cụ bạn cần. Trong bài viết này, Gitiho sẽ cung cấp cho các bạn một thư viện code VBA trong Excel giúp bạn mở file Excel, lưu file Excel, tạo file Excel mới cũng như trích xuất dữ liệu chỉ trong nháy mắt.

Xem thêm: Khóa học Excel Tuyệt đỉnh Excel giúp bạn trở thành bậc thầy Excel trong 16h

EXG01: Tuyệt đỉnh Excel | Khóa học Excel online từ cơ bản đến nâng cao

G-LEARNING

499,000^đ 799,000^đ

XEM NHANH BÀI VIẾT

1 Tìm hiểu về đối tượng Workbook trong VBA Excel
2 Tìm hiểu về VBA Excel
3 Tổng hợp mã VBA Excel thao tác với workbook
4 Tổng kết

Tìm hiểu về đối tượng Workbook trong VBA Excel

Trước khi tìm hiểu các mã VBA Excel, bạn cần hiểu về đối tượng workbook Excel. Vậy workbook là gì? Có điểm gì bạn cần lưu ý khi thao tác với đối tượng này? Chúng ta sẽ cùng khám phá ngay nhé.

Workbook là gì?

Có thể bạn đã từng nghe đến thuật ngữ workbook, hoặc một tên gọi được được sử dụng phổ biến hơn: File Excel. Một workbook chính là một file Excel, bao gồm các trang tính, bảng biểu và đồ thị mà chúng ta vẫn hay sử dụng để trình bày dữ liệu.

Phân biệt Workbook và Worksheet trong Excel

Sau khi tìm hiểu workbook là gì, điều quan trọng hơn là bạn phải phân biệt được 2 thuật ngữ workbook và worksheet, vì ý nghĩa của 2 thuật ngữ này hoàn toàn khác nhau và trong hầu hết các trường hợp, chúng ta không thể dùng chúng thay cho nhau.

Workbook là một file Excel, trong khi đó, worksheet chỉ là một trang tính nằm trong file Excel. Nói cách khác, một workbook có thể bao gồm nhiều worksheet. Bạn có thể hiểu workbook như một quyển sách mà các trang của nó được gọi là worksheet.

Mỗi khi mở Excel hoặc mở một workbook nhất định, bạn sẽ thấy màn hình như hình dưới. Tại đây, các worksheet trong workbook sẽ được hiển thị tại thanh dưới của giao diện Excel.

Tổng hợp các code VBA Excel thao tác với Workbook chỉ trong nháy mắt

Xem thêm: Hướng dẫn giải đáp 40 câu hỏi thường gặp trong Microsoft Excel

Tìm hiểu về VBA Excel

Như vậy, chúng ta đã nắm rõ được khái niệm workbook là gì, cũng như phân biệt được workbook và worksheet khi làm việc với dữ liệu trên Excel. Tiếp theo, chúng ta sẽ khám phá thư viện code VBA trong Excel. Tuy nhiên, VBA là gì? Bạn đã biết chưa?

VBA là gì?

Nếu bạn còn chưa biết VBA là gì thì mình sẽ giải thích qua một chút nhé. VBA Excel (Visual Basic for Applications) là một ngôn ngữ lập trình chung của các phần mềm trong hệ sinh thái Microsoft Office, bao gồm Excel, Word và PowerPoint.

Đối với Excel, VBA Excel cho phép người sử dụng tự động hóa các thao tác Excel, tự tạo các chức năng cho biểu mẫu hay báo cáo trong trang tính. Ngoài ra, VBA Excel giúp bạn thực hiện tính toán với dữ liệu dày đặc trên bảng tính một cách nhanh chóng và loại bỏ tối đa các sai sót của quá trình tính toán thủ công. Thậm chí bạn có thể tự tạo một hàm Excel mới để thực hiện các yêu cầu cụ thể dành cho trang tính của mình.

Như vậy, bằng cách sử dụng mã VBA, bạn có thể thực hiện các thao tác phức tạp mà khó có thể hoàn thành với các tính năng Excel thông thường. Bên cạnh đó, bạn có thể thiết lập một quy trình các thao tác áp dụng lên các đối tượng trong worksheet chỉ trong một cú nhấn chuột thay vì lặp đi lặp lại các thao tác riêng biệt với từng đối tượng.

Nói một cách ngắn gọn, VBA Excel là công cụ tối ưu trải nghiệm làm việc trên Excel của bạn. Nếu bạn còn chưa tin thì hãy đọc bài viết dưới đây về sức mạnh tạo hàm Excel của VBA nhé.

Xem thêm: Hướng dẫn cách tự tạo hàm UDF trong Excel bằng lệnh VBA

Cách sử dụng VBA Excel trong workbook

Làm thế nào để sử dụng các mã VBA Excel? Khi bạn đã copy lệnh mình cần, điều quan trọng lúc này là biết được vị trí cần paste lệnh.

Việc sử dụng VBA trong Excel được thể hiện qua các macro. Các macro này đóng vai trò là "phiên dịch viên" mã hóa lệnh VBA chúng ta đã tạo để Excel hiểu và thực hiện hành động cần thiết. Như vậy, chúng ta sẽ dễ dàng đẩy nhanh tiến độ làm việc trong Excel bằng cách sử dụng các Macro.

Dưới đây là cụ thể các bước sử dụng lệnh VBA trong Excel.

Bước 1: Đi đến thẻ Developer > Visual Basic. Thao tác này sẽ mở ra cửa sổ Microsoft Visual Basic for Applications.

Bước 2: Tại cửa sổ VBA Excel, bạn nhấn chuột phải tại worksheet cần áp dụng lệnh VBA > Insert > Module.

Bước 3: Tại module vừa tạo, bạn paste đoạn mã VBA Excel cần sử dụng.

Bước 4: Nhấn biểu tượng hình tam giác màu xanh trên thanh công cụ hoặc nhấn phím tắt F5/fn+F5 để chạy VBA. Thao tác này sẽ mở ra hộp thoại Macro.

Bước 5: Tại hộp thoại Macro, chọn mã VBA Excel cần chạy và nhấn Run.

Để tìm hiểu kỹ hơn về VBA Excel, bạn hãy tham khảo bài viết giới thiệu về công cụ này ngay dưới đây nhé. Nếu đã sẵn sàng thì tiếp tục khám phá thư viện code VBA trong Excel thao tác với đối tượng workbook luôn thôi!

Xem thêm: Giới thiệu đặc điểm file VBA Excel và các tính năng VBA cơ bản

Tổng hợp mã VBA Excel thao tác với workbook

Trong phần tổng hợp mã VBA Excel dưới đây, mình sẽ hướng dẫn bạn một số cách thực hiện các thao tác với đối tượng workbook bằng VBA trong Excel.

Chúng ta sẽ tìm hiểu các code mở file Excel, tạo mới file Excel, trích xuất và nạp dữ liệu từ một file Excel sang một file khác. Ngoài ra, chúng ta cũng có thể dùng VBA Excel như một cách lưu file Excel hiệu quả.

Code VBA mở file Excel

Thao tác đầu tiên chúng ta sẽ tìm hiểu chính là mở workbook. Thông thường, để mở file Excel, chúng ta sẽ cần truy cập trực tiếp vào folder chứa file hoặc thao tác trong phần mềm Excel bằng các phương pháp:

Cách 1: Đi đến thẻ File > Open > truy cập file Excel cần mở

Cách 2: Nhấn tổ hợp phím tắt Ctrl+O > truy cập file Excel cần mở

Vậy còn với VBA Excel, chúng ta cần xác định các yêu cầu như thế nào để có thể mở file Excel? Dưới đây là những yêu cầu mình đặt ra để xây dựng lệnh VBA Excel thực hiện thao tác mở workbook.

Khi mở folder để tìm file, có một bộ lọc file để hiển thị chỉ những file Excel và file CSV (mặc định là các file Excel) trong folder.
Khi bấm nút Open thì file được chọn sẽ mở ra.
Khi bấm nút Cancel, có thông báo dưới dạng MsgBox để xác nhận thao tác.

Với các yêu cầu như trên, dưới đây là mã VBA Excel chúng ta sẽ sử dụng để mở file Excel:

Sub Open_Single_File()
     'Khai báo các biến sử dụng
     Dim dk_Ten_tieu_de As String     'Tên tiêu đề cửa sổ Workbook Open
     Dim dk_Loc_LoaiFile As String    'Lọc các loại file có trong cửa sổ Workbook Open
     Dim dk_Loc_Index As Integer      'Thứ tự lọc mặc định
     Dim kq_File_duoc_chon As String  'File được chọn
          dk_Loc_LoaiFile = "Excel Files (*.xls*),*.xls," & "CSV Files (*.csv),*.csv,"
          dk_Loc_Index = 1
          dk_Ten_tieu_de = "Select Your Input File of Choice"
     'Mở cửa sổ Workbook Open
     kq_File_duoc_chon = Application.GetOpenFilename _
          (FileFilter:=dk_Loc_LoaiFile, _
           FilterIndex:=dk_Loc_Index, _
           Title:=dk_Ten_tieu_de)
     'Các trường hợp không thành công
     'Trường hợp 1: Không có file đựoc chọn
     If kq_File_duoc_chon = "" Then
          MsgBox ("Khong co file duoc chon")
          Exit Sub
     'Truờng hợp 2. Bấm vào nút Cancel
     ElseIf kq_File_duoc_chon = "False" Then
          MsgBox ("Ban da bam lenh Huy thao tac")
          Exit Sub
     End If
     'Truờng hợp thành công: Mở file được chọn
     Workbooks.Open kq_File_duoc_chon
End Sub

Như bạn thấy trong đoạn code VBA phía trên, bên cạnh các dòng lệnh là diễn giải ý nghĩa tương ứng. Những gì sẽ diễn ra sau khi bạn chạy macro với lệnh này là: Hộp thoại chọn file mở lên, chỉ hiển thị các file ở định dạng Excel mở được. Sau khi bạn chọn một file và nhấn Open, Excel sẽ mở workbook đó lên. Còn nếu bạn nhấn Cancel, một hộp thoại thông báo như trong hình dưới đây sẽ hiện lên.

Giả sử bạn mở file Excel chứa VBA Excel, bạn sẽ nhận được một dòng cảnh báo Security Warning của hệ thống. Vậy làm thế nào để loại bỏ thông báo này? Tham khảo cách thiết lập phân quyền cho phép bật macro để sử dụng VBA Excel trong bài viết dưới đây nhé.

Code VBA lưu file Excel

Chúng ta đã tìm hiểu mã VBA Excel được dùng để mở file Excel, vậy còn mã với cách lưu file Excel thì sao? Tương tự như trên, đầu tiên chúng ta sẽ xác định các yêu cầu cho mã như dưới đây:

Khi bấm nút Save, file Excel sẽ được lưu vào folder đang mở.
Khi bấm nút Cancel, có thông báo dưới dạng MsgBox để xác nhận thao tác.

Để đáp ứng các yêu cầu như trên, dưới đây là mã VBA Excel chúng ta sẽ sử dụng với cách file Excel:

Sub Luu_Workbook_FileDialogSaveAs()
     'Mo cua so FileDialog SaveAs
     With Application.FileDialog(msoFileDialogSaveAs)
           .FilterIndex = 2
          .Show
          If .SelectedItems.Count > 0 Then     'Co file duoc chon
                ActiveWorkbook.SaveAs Filename:=.SelectedItems(1), _
                    FileFormat:=xlOpenXMLWorkbookMacroEnabled
          Else     'Các truong hop khác không có file duoc chon
                 MsgBox ("Khong co file duoc chon")
          End If
      End With
End Sub

Nếu bạn muốn lưu file Excel dưới dạng Read-only thì hãy thêm dòng mã như sau vào ngay trên dòng Else:

VBA.SetAttr .SelectedItems(1), vbReadOnlyNhư vậy, khi bạn chạy macro với lệnh VBA Excel trên, hộp thoại File Save sẽ hiện lên. Bạn chọn vị trí folder cần lưu file và điền tên file muốn lưu như bình thường, sau đó nhấn Save để hoàn thành cách lưu file Excel.

Nếu bạn nhấn Cancel, bạn sẽ thấy hiện lên trên giao diện trang tính Excel một hộp thoại trong hình dưới đây. Hộp thoại này sẽ thông báo cho bạn biết rằng thao tác lưu file đã được hủy bỏ.

Code VBA tạo mới workbook

Trong trường hợp cần tạo một workbook mới thì bạn cần sử dụng mã VBA Excel như thế nào? Dưới đây là các mã bạn có thể dùng trong các trường hợp cần tạo một file Excel hoàn toàn mới.

Trường hợp 1: Tạo thêm một workbook mới, không đóng workbook hiện tại.

Sub Tao_Workbook_Moi()
Workbooks.Add
End Sub

Trường hợp 2: Tạo một workbook mới, đóng và lưu workbook hiện tại.

Sub Tao_Workbook_Moi()
     Dim new_wb As Workbook
     Set new_wb = Workbooks.Add
     Dim TenWorkbook_Moi As String
     TenWorkbook_Moi = ThisWorkbook.Path & "\" & _
                                       ThisWorkbook.Sheets(1).Range("A1") & _
                                        ".xlsx"
     new_wb.SaveAs TenWorkbook_Moi
End Sub

Khi sử dụng mã VBA Excel phía trên để tạo một workbook mới, bạn cần chú ý một vài đặc điểm của workbook trong các dòng mã để đảm bảo mã chạy ổn định:

Đường dẫn (Path): Nếu lấy theo đường dẫn của file gốc thì dùng ThisWorkbook.Path
Tên file: có thể đặt tại 1 ô trong file gốc và chỉ định tới ô này (để viết tên tùy theo ý muốn)
Loại file: có thể đặt tại 1 ô trong file gốc và chỉ định tới ô này (hoặc dùng loại file mặc định là .xlsx hoặc .xlsm)

Đối với các trường hợp khác, bạn có thể sử dụng lệnh FileDialogSaveAs để chỉ định rõ địa chỉ và tên file Excel hiện tại.

Xem thêm: Hướng dẫn cách tách sheet trong Excel thành các file riêng bằng VBA

Code VBA trích xuất dữ liệu sang workbook mới

Giả sử bạn muốn tạo một workbook mới bằng lệnh VBA Excel, sau đó trích xuất dữ liệu từ workbook hiện tại ra workbook vừa tạo. Thay vì copy paste và thực hiện các thao tác định dạng thủ công, dưới đây là cách làm tối ưu hơn với lệnh VBA trong Excel:

Sub Export_New_Workbook()
  'Khai bao các bien su dung
     Dim NewWB As Workbook, ThisWB As Workbook
          Set ThisWB = ThisWorkbook        'Workbook ban dau
          Set NewWB = Workbooks.Add     'Tao workbook moi
     Dim Sheet_Import As Worksheet, Sheet_Export As Worksheet
          Set Sheet_Import = NewWB.Worksheets(1)   'Sheet chua du lieu duoc trich xuat
          Set Sheet_Export = ThisWB.Worksheets("Data")   'Sheet chua du lieu ban dau
  'Dua phan giá tri sang file moi
     Sheet_Import .Range("A1:D16").Value = Sheet_Export.Range("A54:D69").Value
     'Dinh dang trong file moi
      With Sheet_Import
          .Range("A8:C8").Style = "Accent1"                            'Ðinh dang Cell Styles
          .Range("A1:C1").EntireColumn.ColumnWidth = 12    'Ðo rong cot
          .Range("A1:A16").EntireRow.RowHeight = 18            'Chieu cao cua dong
          With .Range("A8:C13").Borders                                  'Ðinh dang duong vien
               .LineStyle = xlContinuous           'Loai duong ke
               .ThemeColor = 5                         'Mau duong ke
          End With
          With .Range("A15").Font          'Ðinh dang font chu
               .FontStyle = "Bold"                   'Kieu chu (in dam/nghieng/gach chân…)
               .Size = 13                                 'Co chu
               .Color = 255                             'Màu chu
          End With
     End With
     NewWB.Activate      'Kích hoat Workbook moi tao
End Sub

Mã VBA Excel này sẽ thực hiện tạo workbook mới, sau đó copy cả dữ liệu và định dạng từ file Excel hiện tại sang file vừa tạo. Bạn có thể kết hợp thêm cách lưu file Excel vào thao tác xây dựng code VBA để lưu ngay file mới sau khi tạo. Đây là mẹo cực kỳ hữu ích khi bạn đang cần tạo một bản sao backup cho file Excel gốc, đề phòng trường hợp sự cố không mong muốn

Code VBA nạp dữ liệu từ workbook khác

Phần cuối cùng của thư viện code VBA trong Excel mình sẽ chia sẻ tại bài viết ngày hôm nay là code VBA Excel được dùng để nạp dữ liệu từ một hoặc nhiều workbook khác Dưới đây là code VBA Excel bạn có thể sử dụng để copy dữ liệu từ một workbook khác vào workbook hiện tại. Thao tác này vô cùng quan trọng nếu bạn đang cần tổng hợp dữ liệu từ nhiều file Excel khác nhau vào một file chung.

Để xây dựng lệnh VBA Excel, mình sẽ đặt ra các yêu cầu và quy trình hoạt động của code như sau:

Đầu tiên, lệnh mở thư mục chọn file và duyệt tới các file Excel.
Lệnh cần cho phép chọn nhiều file cùng lúc với điều kiện các file chi tiết có cấu trúc giống nhau.
Chỉ cần lấy phần nội dung dữ liệu, không bao gồm định dạng trong các trang tính.
Cuối cùng, sau khi đã lấy dữ liệu thì đóng file Excel đã chọn.

Và chúng ta có code mẫu VBA trong Excel dưới đây:

Sub  Import_data_from_multi_Workbooks()
  'Khai báo các biến sử dụng
     Dim ThisWB As Workbook, OpenWB As Workbook     'Biến Workbook
     Dim i As Integer      'Biến vòng lặp
     Dim lr_Data As Long       'Dòng cuối bảng nhận dữ  liệu (lr = Last Row)
     Dim fr_OpenWB As Long       'Dòng đầu bảng cho dữ liệu (fr = First Row)
     Dim lr_OpenWB As Long       'Dòng cuối bảng cho dữ liệu
     Dim KhoangCach As Long     'Khoảng cách tính từ dòng đầu tới dòng cuối nơi cho
     With Application.FileDialog(msoFileDialogFilePicker)
          .AllowMultiSelect = True   'Cho phép chọn nhiều file cùng lúc (chọn 1 file là False)
          .Show
        For i = 1 To .SelectedItems.Count   'Vòng lặp theo số file được chọn
          Set ThisWB = ThisWorkbook        'Workbook ban đầu
          Set OpenWB = Workbooks.Open(.SelectedItems(i))     'Workbook được chọn
          'Xác định giá trị các biến phạm vi Nơi cho, Nơi nhận
          lr_Data = ThisWB.Sheets("Data").Range("A" & Rows.Count).End(xlUp).Row
          fr_OpenWB = 8
          lr_OpenWB = OpenWB.Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
          KhoangCach = lr_OpenWB - fr_OpenWB + 1
          'Đưa dữ liệu từ Nơi cho sang Nơi nhận
          ThisWB.Sheets("Data").Range("A" & lr_Data + 1 & ":BM" & lr_Data + KhoangCach).Value = _
                OpenWB.Sheets(1).Range("A" & fr_OpenWB & ":BM" & lr_OpenWB).Value
          'Đóng bảng tính đã mở lên trước đó
          OpenWB.Close SaveChanges:=False   
        Next i
     End With
End Sub

Thay vì gộp các workbook, nếu bạn chỉ cần gộp sheet trong Excel thì bài viết dưới đây sẽ cung cấp cho bạn code VBA Excel bạn cần.

Xem thêm: Hướng dẫn cách gộp các sheet trong Excel kèm theo code VBA

Tổng kết

Vậy là chúng ta đã tìm hiểu các mã VBA Excel có thể giúp chúng ta tự động hóa các thao tác với đối tượng workbook, hay còn gọi là file Excel. Với thư viện code VBA trong Excel trên, chúng ta chỉ cần một vài cú nhấn chuột để mở file Excel, tạo file Excel hay trích xuất dữ liệu giữa các file khác nhau. Tất nhiên, bạn cũng có code VBA để thực hiện cách lưu file Excel nhanh chóng.

Hy vọng bài viết ngày hôm nay đã giúp bạn sưu tầm được các lệnh VBA trong Excel hữu ích cho công việc của mình. Để thu thập thêm các lệnh VBA Excel khác, hãy tham khảo các bài viết về chủ đề VBA Excel trên blog tin học văn phòng và các khóa học sau của Gitiho bạn nhé: