Tiếp theo trong chủ đề nối nhiều tập tin excel thành một tâp tin duy nhất, bài này tôi xin hướng dẫn cách gom nhiều tập tin excel có nhiều sheets khác nhau thành một tâp tin excel duy nhất dữ liệu của các sheet sẽ được copy tương ứng vào các sheets của tập tin duy nhất này. (How to combine multiple excel have multi sheets in one file by marco in Excel 2010?)
Như trong các bài viết trước tôi sẽ chuẩn bị một ví dụ để mô tả trong xuyên xuốt bài viết này, đầu tiên như bạn thấy trong hình dưới tôi có chuẩn bị 6 tập tin excel dùng để gom nó thành một tập tin duy nhất tại một sheet duy nhất trong tập tin excel "Combine_All_Excel.xlsx"
Trong tất các tập tin excel từ 1 → 6 tôi đều tạo sẵn dữ liệu cho cả 3 Sheet (sheet 1, sheet 2, sheet 3) trừ Sheet 2 tôi chỉ có tạo dữ liệu 2 sheet để test trong trường hợp tập tin không có số lượng sheet có không đồng bộ. Dữ liệu các bạn thấy trong một tin tin sẽ như sau:
Bây giờ ta sẽ tạo một tập tin excel trắng hoàn toàn mới dùng để gom 6 tập tin excel trên thành một, như trong ví dụ ở hình trên tôi đã tạo tập tin excel tên "Combine_All_Excel.xlsx".
Khi tạo tập tin này bạn cần chú ý số lượng Sheets lớn nhất trong các tập tin gom của bạn là bao nhiêu từ đó tạo ra số Sheets tương ứng trong tập Combine_All_Excel.xlsx, như trong ví dụ này các tập tin gom từ 001 → 006 của tôi có số Sheet lớn nhất là 3 thì trong tập tin gom tôi phải có sẵn 3 Sheets (Trường hợp có 4 sheets thì bạn phải tạo 4 sheets)
Sau khi tạo xong mở tập tin này lên và chọn trên thanh menu "View → Macro".
Trong cửa sổ Macro được hiển thị, bạn điền các thông số sau:
Tiếp theo bạn copy đoạn mã code bên dưới vào bản soạn thảo Module1 (Code). Đoạn macro này sẽ thực hiện việc copy tất cả dữ liệu ở tất cả các tập tin excel vào Sheet 1 của tập tin excel "Combine_All_Excel.xlsx", bạn lưu ý thay đổi đường dẫn path = "D:\Z-Test\EXCEL" cho tương ứng với thư mục chứa các tập tin excel cần gom.
Trong đoạn macro bạn cần chú ý 2 thông số sau:
Sau đó bạn nhấn nút Run màu xanh bên trên bảng menu icon, hoặc trên thanh menu chọn "Run" → "Run Sub/UserForm F5" để thực thi các lệnh macro vừa tạo.
Sau khi tiến trình chạy xong một bảng thông báo kết thúc sẽ hiện thị, bạn chọn "OK" để kết thúc quá trình gom các tập tin excel.
Dữ liệu của các tập tin excel sẽ được gom lại trong tập tin "Combine_All_Excel" và dưới đây là kết quả.
Như bạn thấy trong hình tại Sheet 3 tôi bị mất đi dữ liệu của tập excel 002, nhưng nó ko phải là mất mà là tại excel 002 tôi không có Sheet 3 nên macro sẽ bỏ quả và gom tới tập tin excel 003.
Nếu đoạn macro này không đáp ứng được nhu cầu, bạn có thể xem thêm các chủ đề khác về việc nối nhiều tập tin excel thành 1 như sau:
Chúc các bạn thành công, nếu bài viết hữu ích hãy like và comments gì đó nha ^^! nó tạo cho mình có thêm động lực viết bài ^^!
Write: +Bui Ngoc Son
Như trong các bài viết trước tôi sẽ chuẩn bị một ví dụ để mô tả trong xuyên xuốt bài viết này, đầu tiên như bạn thấy trong hình dưới tôi có chuẩn bị 6 tập tin excel dùng để gom nó thành một tập tin duy nhất tại một sheet duy nhất trong tập tin excel "Combine_All_Excel.xlsx"
Trong tất các tập tin excel từ 1 → 6 tôi đều tạo sẵn dữ liệu cho cả 3 Sheet (sheet 1, sheet 2, sheet 3) trừ Sheet 2 tôi chỉ có tạo dữ liệu 2 sheet để test trong trường hợp tập tin không có số lượng sheet có không đồng bộ. Dữ liệu các bạn thấy trong một tin tin sẽ như sau:
Bây giờ ta sẽ tạo một tập tin excel trắng hoàn toàn mới dùng để gom 6 tập tin excel trên thành một, như trong ví dụ ở hình trên tôi đã tạo tập tin excel tên "Combine_All_Excel.xlsx".
Khi tạo tập tin này bạn cần chú ý số lượng Sheets lớn nhất trong các tập tin gom của bạn là bao nhiêu từ đó tạo ra số Sheets tương ứng trong tập Combine_All_Excel.xlsx, như trong ví dụ này các tập tin gom từ 001 → 006 của tôi có số Sheet lớn nhất là 3 thì trong tập tin gom tôi phải có sẵn 3 Sheets (Trường hợp có 4 sheets thì bạn phải tạo 4 sheets)
Sau khi tạo xong mở tập tin này lên và chọn trên thanh menu "View → Macro".
Trong cửa sổ Macro được hiển thị, bạn điền các thông số sau:
- Macro Name: MergeFilesExcel
- Macros in: This Workbook
Tiếp theo bạn copy đoạn mã code bên dưới vào bản soạn thảo Module1 (Code). Đoạn macro này sẽ thực hiện việc copy tất cả dữ liệu ở tất cả các tập tin excel vào Sheet 1 của tập tin excel "Combine_All_Excel.xlsx", bạn lưu ý thay đổi đường dẫn path = "D:\Z-Test\EXCEL" cho tương ứng với thư mục chứa các tập tin excel cần gom.
Sub MergeFilesExcel()
Dim ThisWB As String
Dim path As String
Dim lngFilecounter As Long
Dim wbDest As Workbook
Dim shtDest As Worksheet
Dim WS As Worksheet
Dim Filename As String, Wkb, WkbDest As Workbook
Dim CopyRng As Range
Dim Dest As Range
Dim RowofCopySheet As Integer
RowofCopySheet = 1
ThisWB = ActiveWorkbook.Name
'Dien duong dan folder chua cac tap tin excel can gom lai.
'Nhu ban thay toi tien duong dan thu muc chua cai file excel cua toi.
path = "D:\Z-Test\EXCEL"
Application.EnableEvents = False
Application.ScreenUpdating = False
Dim x As Integer
Dim max As Integer
max = 3
For x = 1 To max
'MsgBox x
Set shtDest = ActiveWorkbook.Sheets(x)
Filename = Dir(path & "\*.xls", vbNormal)
If Len(Filename) = 0 Then Exit Sub
Do Until Filename = vbNullString
If Not Filename = ThisWB Then
Set Wkb = Workbooks.Open(Filename:=path & "\" & Filename)
'MsgBox Wkb.Sheets.Count & "Sheets Count"
If x <= Wkb.Sheets.Count Then
If Wkb.Sheets(x).Range("A1").Value = 0 Then
'MsgBox Wkb.Sheets(n).Name & " is empty"
Else
'MsgBox Wkb.Sheets(n).Range("A1").CurrentRegion.Rows.Count & " Row"
'MsgBox ColumnLetter(Wkb.Sheets(n).Range("A1").CurrentRegion.Columns.Count) & " Cols"
Set CopyRng = Wkb.Sheets(x).Range("A2:" & ColumnLetter(Wkb.Sheets(x).Range("A1").CurrentRegion.Columns.Count) & Wkb.Sheets(x).Range("A1").CurrentRegion.Rows.Count)
Set Dest = shtDest.Range("A" & shtDest.UsedRange.SpecialCells(xlCellTypeLastCell).Row + 1)
CopyRng.Copy Dest
End If
Else
'Ko xu ly
End If
Wkb.Close False
End If
Filename = Dir()
Loop
Next x
Range("A1").Select
Application.EnableEvents = True
Application.ScreenUpdating = True
MsgBox "Ket Thuc!"
End Sub
Function ColumnLetter(ColumnNumber As Long) As String
Dim n As Long
Dim c As Byte
Dim s As String
n = ColumnNumber
Do
c = ((n - 1) Mod 26)
s = Chr(c + 65) & s
n = (n - c) \ 26
Loop While n > 0
ColumnLetter = s
End Function
Dim ThisWB As String
Dim path As String
Dim lngFilecounter As Long
Dim wbDest As Workbook
Dim shtDest As Worksheet
Dim WS As Worksheet
Dim Filename As String, Wkb, WkbDest As Workbook
Dim CopyRng As Range
Dim Dest As Range
Dim RowofCopySheet As Integer
RowofCopySheet = 1
ThisWB = ActiveWorkbook.Name
'Dien duong dan folder chua cac tap tin excel can gom lai.
'Nhu ban thay toi tien duong dan thu muc chua cai file excel cua toi.
path = "D:\Z-Test\EXCEL"
Application.EnableEvents = False
Application.ScreenUpdating = False
Dim x As Integer
Dim max As Integer
max = 3
For x = 1 To max
'MsgBox x
Set shtDest = ActiveWorkbook.Sheets(x)
Filename = Dir(path & "\*.xls", vbNormal)
If Len(Filename) = 0 Then Exit Sub
Do Until Filename = vbNullString
If Not Filename = ThisWB Then
Set Wkb = Workbooks.Open(Filename:=path & "\" & Filename)
'MsgBox Wkb.Sheets.Count & "Sheets Count"
If x <= Wkb.Sheets.Count Then
If Wkb.Sheets(x).Range("A1").Value = 0 Then
'MsgBox Wkb.Sheets(n).Name & " is empty"
Else
'MsgBox Wkb.Sheets(n).Range("A1").CurrentRegion.Rows.Count & " Row"
'MsgBox ColumnLetter(Wkb.Sheets(n).Range("A1").CurrentRegion.Columns.Count) & " Cols"
Set CopyRng = Wkb.Sheets(x).Range("A2:" & ColumnLetter(Wkb.Sheets(x).Range("A1").CurrentRegion.Columns.Count) & Wkb.Sheets(x).Range("A1").CurrentRegion.Rows.Count)
Set Dest = shtDest.Range("A" & shtDest.UsedRange.SpecialCells(xlCellTypeLastCell).Row + 1)
CopyRng.Copy Dest
End If
Else
'Ko xu ly
End If
Wkb.Close False
End If
Filename = Dir()
Loop
Next x
Range("A1").Select
Application.EnableEvents = True
Application.ScreenUpdating = True
MsgBox "Ket Thuc!"
End Sub
Function ColumnLetter(ColumnNumber As Long) As String
Dim n As Long
Dim c As Byte
Dim s As String
n = ColumnNumber
Do
c = ((n - 1) Mod 26)
s = Chr(c + 65) & s
n = (n - c) \ 26
Loop While n > 0
ColumnLetter = s
End Function
Trong đoạn macro bạn cần chú ý 2 thông số sau:
- path = "D:\Z-Test\EXCEL": Đường dẫn thư mục chứa tất cả các tập tin excel cần gom, bạn thay đổi cho phù hợp.
- Dim max As Integer - max = 3: Thông số này là quang trọng bạn cần chú ý, để có được thông số này trước tiên bạn cần xác định trong các tập tin excel cần gom số lương sheets lớn nhất là bao nhiêu, từ đó điền vào số max.
Sau đó bạn nhấn nút Run màu xanh bên trên bảng menu icon, hoặc trên thanh menu chọn "Run" → "Run Sub/UserForm F5" để thực thi các lệnh macro vừa tạo.
Sau khi tiến trình chạy xong một bảng thông báo kết thúc sẽ hiện thị, bạn chọn "OK" để kết thúc quá trình gom các tập tin excel.
Dữ liệu của các tập tin excel sẽ được gom lại trong tập tin "Combine_All_Excel" và dưới đây là kết quả.
Như bạn thấy trong hình tại Sheet 3 tôi bị mất đi dữ liệu của tập excel 002, nhưng nó ko phải là mất mà là tại excel 002 tôi không có Sheet 3 nên macro sẽ bỏ quả và gom tới tập tin excel 003.
Nếu đoạn macro này không đáp ứng được nhu cầu, bạn có thể xem thêm các chủ đề khác về việc nối nhiều tập tin excel thành 1 như sau:
- Nối Sheet 1 hoặc Sheet 2,3,4,... của nhiều tập tin excel thành một tập tin duy nhât.
- Nối nhiều tập tin excel có nhiều sheet thành một sheet duy nhất trong 1 tập tin excel duy nhất.
- Nối các Sheet của nhiều tập tin excel thành 1 tập tin excel duy nhất tương ứng với các Sheet của các tập tin excel được gom.
Chúc các bạn thành công, nếu bài viết hữu ích hãy like và comments gì đó nha ^^! nó tạo cho mình có thêm động lực viết bài ^^!
Write: +Bui Ngoc Son
No comments:
Post a Comment