Cách hợp nhất nhiều sổ làm việc Excel bằng VBA
Khi làm việc với nhiều nguồn dữ liệu khác nhau, bạn thường có thể gặp khó khăn khi biên dịch nhiều sổ làm việc và trang tính trước khi đến một phần dữ liệu cuối cùng. Hãy tưởng tượng một tình huống mà bạn có vài trăm sách bài tập để kết hợp trước khi bạn có thể bắt đầu một ngày của mình.
Không ai muốn dành hàng giờ liên tục để làm việc trên các nguồn khác nhau, mở từng sổ làm việc, sao chép và dán dữ liệu từ các trang tính khác nhau, trước khi tạo một sổ làm việc hợp nhất. Điều gì sẽ xảy ra nếu một macro VBA có thể làm điều này cho bạn?
Với hướng dẫn này, bạn có thể tạo mã macro VBA trong Excel của riêng mình để hợp nhất nhiều sổ làm việc, tất cả chỉ trong vài phút (nếu tệp dữ liệu là nhiều).
Mục Lục
Điều kiện tiên quyết để tạo mã Macro VBA của riêng bạn
Bạn cần một sổ làm việc để chứa mã VBA, trong khi phần còn lại của các sổ làm việc dữ liệu nguồn là riêng biệt. Ngoài ra, hãy tạo một sổ làm việc Hợp nhất, củng cố để lưu trữ dữ liệu tổng hợp từ tất cả các sổ làm việc của bạn.
Tạo một thư mục Hợp nhất tại vị trí ưa thích của bạn để lưu trữ tất cả các sổ làm việc nguồn của bạn. Khi macro chạy, nó sẽ chuyển qua từng sổ làm việc được lưu trữ trong thư mục này, sao chép nội dung từ các trang tính khác nhau và đặt nó vào sổ làm việc Hợp nhất.
Tạo mã VBA Excel của riêng bạn
Khi các điều kiện tiên quyết không còn nữa, đã đến lúc nghiên cứu kỹ mã và bắt đầu hack những điều cơ bản để điều chỉnh nó cho phù hợp với yêu cầu của bạn.
Nhấn vào Alt + F11 trên Excel để mở trình soạn thảo mã macro VBA. Dán mã được viết bên dưới và lưu tệp dưới dạng sổ làm việc hỗ trợ Macro (.xlsm sự mở rộng).
Sub openfiles()'declare the variables used within the VBA code
Dim MyFolder As String, MyFile As String, wbmain As Workbook, lastrow As Long
'disable these functions to enhance code processing
With Application
.DisplayAlerts = False
.ScreenUpdating = False
End With
'change the path of the folder where your files are going to be saved
MyFolder = InputBox("Enter path of the Consolidation folder") & ""
'define the reference of the folder in a macro variable
MyFile = Dir(MyFolder)
'open a loop to cycle through each individual workbook stored in the folder
Do While Len(MyFile) > 0
'activate the Consolidation workbook
Windows("Consolidation").Activate
'calculate the last populated row
Range("a1048576").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select
'open the first workbook within the Consolidation folder
Workbooks.Open Filename:=MyFolder & MyFile
Windows(MyFile).Activate
'toggle through each sheet within the workbooks to copy the data
Dim ws As Worksheet
For Each ws In Sheets
ws.Activate
ws.AutoFilterMode = False
'ignore the header and copy the data from row 2
If Cells(2, 1) = "" Then GoTo 1
GoTo 10
1: Next
10: Range("a2:az20000").Copy
Windows("Consolidation").Activate
'paste the copied contents
ActiveSheet.Paste
Windows(MyFile).Activate
'close the open workbook once the data is pasted
ActiveWorkbook.Close
'empty the cache to store the value of the next workbook
MyFile = Dir()
'open the next file in the folder
Loop
'enable the disabled functions for future use
With Application
.DisplayAlerts = True
.ScreenUpdating = True
End With
End Sub
Giải thích mã VBA
Phần đầu tiên của mã là xác định một chương trình con, chương trình này chứa tất cả mã VBA của bạn. Xác định chương trình con với phụ, theo sau là tên của mã. Tên phụ có thể là bất cứ thứ gì; lý tưởng nhất là bạn nên giữ một cái tên có liên quan đến mã bạn sắp viết.
Excel VBA hiểu các biến do người dùng tạo và các kiểu dữ liệu tương ứng của chúng được khai báo với lờ mờ (kích thước).
Để nâng cao tốc độ xử lý mã của bạn, bạn có thể tắt cập nhật màn hình và tắt tất cả các cảnh báo, vì điều đó làm chậm quá trình thực thi mã.
Người dùng sẽ được nhắc về đường dẫn của thư mục chứa các tệp dữ liệu. Một vòng lặp được tạo để mở từng sổ làm việc được lưu trữ trong thư mục, sao chép dữ liệu từ mỗi trang tính và nối nó vào Hợp nhất sách bài tập.
Sổ làm việc Hợp nhất được kích hoạt để Excel VBA có thể tính toán hàng được điền cuối cùng. Ô cuối cùng trong trang tính được chọn và hàng cuối cùng được tính trong sổ làm việc bằng cách sử dụng hàm offset. Điều này rất hữu ích, khi macro bắt đầu thêm dữ liệu từ các tệp nguồn.
Khi vòng lặp mở tệp nguồn đầu tiên, các bộ lọc sẽ bị xóa khỏi mọi trang tính (nếu chúng tồn tại) và dữ liệu từ A2 đến AZ20000 sẽ được sao chép và dán vào sổ làm việc Hợp nhất.
Quá trình này được lặp lại cho đến khi tất cả các trang tính của sổ làm việc được nối vào trong sổ làm việc chính.
Cuối cùng, tệp nguồn được đóng khi tất cả dữ liệu được dán. Sổ làm việc tiếp theo được mở để macro VBA có thể lặp lại các bước tương tự cho tập hợp tệp tiếp theo.
Vòng lặp được mã hóa để chạy cho đến khi tất cả các tệp được cập nhật tự động trong sổ làm việc chính.
Tùy chỉnh dựa trên người dùng
Đôi khi, bạn không muốn lo lắng về các lời nhắc có sẵn, đặc biệt, nếu bạn là người dùng cuối. Nếu bạn muốn mã hóa đường dẫn của thư mục Hợp nhất trong mã, bạn có thể thay đổi phần này của mã:
MyFolder = InputBox("Enter path of the Consolidation folder") & ""Đến:
MyFolder = “Folder path” & ""Ngoài ra, bạn cũng có thể thay đổi các tham chiếu cột, vì bước này không được bao gồm trong mã này. Chỉ cần thay thế tham chiếu cột cuối bằng giá trị cột được điền cuối cùng của bạn (AZ, trong trường hợp này). Bạn cần nhớ rằng hàng được điền cuối cùng được tính thông qua mã macro, vì vậy bạn chỉ cần thay đổi tham chiếu cột.
Để tận dụng tối đa macro này, bạn chỉ có thể sử dụng nó để hợp nhất các sổ làm việc ở cùng một định dạng. Nếu cấu trúc khác nhau, bạn không thể sử dụng macro VBA này.
Hợp nhất nhiều sổ làm việc bằng Excel VBA Macro
Việc tạo và sửa đổi mã VBA trong Excel tương đối dễ dàng, đặc biệt nếu bạn hiểu một số sắc thái trong mã. VBA chạy một cách có hệ thống qua từng dòng mã và thực thi từng dòng một.
Nếu bạn thực hiện bất kỳ thay đổi nào đối với mã, bạn phải đảm bảo rằng bạn không thay đổi thứ tự của các mã, vì điều đó sẽ làm gián đoạn quá trình thực thi của mã.
Đọc tiếp
Giới thiệu về tác giả
