Trích xuất dữ liệu thành nhiều file trong VBA Excel

Làm việc với nhiều Sheet trên một file Excel giúp chúng ta tăng cường sự tương tác và liên kết giữa các bảng tính cần tính toán. Tuy nhiên, nó cũng gây trở ngại không nhỏ nếu file Excel của bạn quá lớn, và có quá nhiều Sheet.

Nội dung chính Show

Cách tách hàng loạt Sheet thành các file Excel bằng code VBA
Lời kết
Hướng dẫn tách sheet thành nhiều file Excel
Lấy dữ liệu từ file Excel khác thông qua VBA
Cách tổng hợp dữ liệu từ nhiều file Excel về một sheet Excel duy nhất
Cách tổng hợp dữ liệu từ nhiều file Excel thành một file Excel có nhiều sheet
Cách tổng hợp dữ liệu từ nhiều file Excel vào 1 file không cần mở file

Và nếu bạn đang có nhu cầu tách các Sheet này ra thành từng file Excel riêng biệt để tăng tốc độ mở file, giảm bớt dung lượng của file khi phải kéo theo các Add ins, hay VBA đã lưu thì đây chính là bài viết dành cho bạn.

Bởi vì trong bài viết này mình sẽ chia sẻ với các bạn một mẹo nhỏ trong việc tách hàng loạt Sheet trên một file Excel, và lưu lại dưới định dạng tên Sheet bằng code Macro VBA Excel.

Sau khi tách xong sẽ lấy tên của các Sheet thành tên file, và các công thức có trong Sheet sẽ vẫn được giữ nguyên mà không bị thay đổi.

Đọc thêm:

Dưới đây là ví dụ của file Excel, với file Bảng điểm gồm 6 Sheet khác nhau.

Cách tách hàng loạt Sheet thành các file Excel bằng code VBA

+ Bước 1: Để tách hàng loạt Sheet mà vẫn giữ nguyên các công thức, cùng các hàm Add ins, hay mã VBA (nếu có). Bạn click mở tab Developer (xem cách hiện tab Developer trên Excel) => và chọn Visual Basic như hình bên dưới.

Hoặc nhấn tổ hợp phím Alt + F11 để mở trình VBA trên Excel ra.

+ Bước 2: Tại hộp thoại Microsoft Visua Basic for Applications, bạn click mở menu Insert => rồi chọn Module.

+ Bước 3: Và bạn hãy copy đoạn mã Code sau:

Sub tachsheet() Application.ScreenUpdating = False Application.DisplayAlerts = False Dim sh As Worksheet For Each sh In Worksheets sh.Copy ActiveWorkbook.SaveAs ThisWorkbook.Path & “\” & sh.Name, 51 ActiveWorkbook.Close Next Application.ScreenUpdating = True Application.DisplayAlerts = True
End Sub

Hoặc code sau:

Sub Splitbook() ‘Updateby20140612 Dim xPath As String xPath = Application.ActiveWorkbook.Path Application.ScreenUpdating = False Application.DisplayAlerts = False For Each xWs In ThisWorkbook.Sheets xWs.Copy Application.ActiveWorkbook.SaveAs Filename:=xPath & “\” & xWs.Name & “.xls” Application.ActiveWorkbook.Close False Next Application.DisplayAlerts = True Application.ScreenUpdating = True
End Sub

+ Bước 4: Paste vào hộp thoại Module (Code) => sau đó nhấn Run, hoặc nhấn phím F5 trên bàn phím để thực hiện tách các Sheet thành các file Excel riêng biệt.

Và đây là kết quả của thư mục sau khi tách Sheet thành các file Excel định dạng *.xlsx.

Lời kết

Okay, như vậy là mình vừa hướng dẫn cho các bạn cách tách Sheet thành từng file Excel riêng biệt bằng code VBA rồi nhé.

Rất nhanh chóng và đơn giản phải không các bạn 😀 Chúc các bạn thành công nha !

CTV: Lương Trung – Blogchiasekienthuc.com

Note: Bài viết này hữu ích với bạn chứ? Đừng quên đánh giá bài viết, like và chia sẻ cho bạn bè và người thân của bạn nhé !

Việc tách sheet thành nhiều file Excel giúp bạn dễ dàng chia nhỏ file Excel, giảm dung lượng file gốc và sẽ lấy được dữ liệu ở sheet mà mình mong muốn. Hãy tham khảo bài viết dưới đây của Taimienphi.vn để biết cách tách sheet trên Excel.

Chắc hẳn bạn cũng biết là việc tạo nhiều sheet trong Excel giúp nội dung dữ liệu giữa các sheet được liền mạch, người xem sẽ dễ dàng chuyển đổi nội dung cũng như theo dõi các nội dung có liên quan với nhau ở từng sheet sẽ dễ dàng hơn. Tuy nhiên, việc nhiều sheet thì dung lượng file Excel cũng sẽ ngày một tăng lên, nhất là khi bạn bổ sung thêm tiện ích add ins hay thêm code macro VBA trong Excel.

Tách sheet trên Excel thành nhiều file riêng biệt

Do đó, giải pháp tách sheet thành nhiều file Excel là cách xử lý hợp lý trong trường hợp người dùng chỉ muốn lấy dữ liệu trong một sheet nào đó, và việc tách sheet trong Excel cũng rất đơn giản, khi tách thì các nội dung trong sheet vẫn được đảm bảo, không bị thay đổi kể cả sheet đó có sử dụng công thức tính toán.

Hướng dẫn tách sheet thành nhiều file Excel

Bước 1: Đầu tiên bạn cần phải kiểm tra xem Excel mình đang dùng đã có tab Developer chưa. Nếu chưa có, bạn thực hiện việc cho hiện Developertheo hướng dẫn tại đây.

Tiếp đến, bạn mở file Excel có nhiều sheet lên, giả sử hình dưới là bạn dang có file Excel với 5 sheet khác nhau.

Bước 2: Bạn nhấn tổ hợp phím ALT + F11 để mở trình VBA trong Excel, hoặc bạn nhấn vào tab Developer -> rồi nhấn chọn Visual Basic.

Bước 3: Sau đó, giao diện VBA xuất hiện -> bạn nhấn vào tab Insert -> rồi chọn vào phần Module để mở giao diện nhập code VBA.

Tiếp theo, bạn hãy copy đoạn mã code dưới đây vào giao diện phần Module ở trên.

Sub Splitbook()

'Updateby20140612

Dim xPath As String

xPath = Application.ActiveWorkbook.Path

Application.ScreenUpdating = False

Application.DisplayAlerts = False

For Each xWs In ThisWorkbook.Sheets

xWs.Copy

Application.ActiveWorkbook.SaveAs Filename:=xPath & "\" & xWs.Name & ".xls"

Application.ActiveWorkbook.Close False

Next

Application.DisplayAlerts = True

Application.ScreenUpdating = True

End Sub

Bước 4: Bạn nhấn vào Run trên thanh công cụ để thực hiện chạy hoặc nhấn phím F5 trên bàn phím để chạy mã code bạn vừa thêm ở trên.

Quá trình chạy code hoàn tất, bạn mở thư mục chứa file Excel gốc -> tại đây, bạn sẽ thấy từng file mới được tách từ các sheet sẽ nằm tại đây. Nếu file Excel của bạn có dùng công thức tính toán, thì toàn bộ các công thức tính toán cùng dữ liệu ở từng sheet sẽ vẫn được đảm bảo và giữ nguyên.

Bạn có thể mở file vừa được tách lên để kiểm tra. Nếu có thông báo như hình dưới -> bạn nhấn chọn Yes để đồng ý và sẽ mở được file lên bình thường.

Như vậy là bài hướng dẫn tách sheet thành nhiều file Excel của Taimienphi.vn đến đây là hết rồi. Các bước để bạn thực hiện thật đơn giản phải không nào! Cái khó nhất là để có mã code sử dụng thì bạn có thể copy đoạn code mà Taimienphi.vn đã tạo sẵn, và bạn chỉ đơn giản là copy chúng vào Module rồi chạy chúng là xong.

https://thuthuat.taimienphi.vn/cach-tach-sheet-thanh-nhieu-file-excel-47863n.aspx
Ngoài ra, bạn có thể tăng thêm Sheet trong Excel hoặc thậm chí bạn có thể gộp nhiều file Excel thành 1 Filetrong Excel là khá dễ dàng! Tất cả đã có hướng dẫn trên Taimienphi.vn, bạn hãy tham khảo cách thực hiện rồi làm theo nhé. Chúc bạn thành công!

Trong công việc hàng ngày, các bạn sẽ gặp trường hợp nhận được rất nhiều file Excel từ nhiều nguồn, hoặc phòng ban khác gửi để cập nhập trong báo cáo, hoặc phải tổng hợp báo cáo tháng theo từng ngày.

Vậy, làm sao để cập nhật dữ liệu từ nhiều file excel về một file excel duy nhất một cách tự động. Bài viết này sẽ chia sẻ giúp các bạn một trong những cách làm đơn giản nhất. Thay vì phải viết code VBA phức tạp, thì chỉ với vài thao tác thiết lập ban đầu với Power Query bạn có thể dự động toàn bộ file dữ liệu cho các lần tiếp theo.

Trước tiên, chúng ta sẽ cùng tìm hiểu về cách lấy dữ liệu từ file excel khác thông qua VBA xem như thế nào nhé. Cách này khá phức tạp đối với người mới, cần biết code VBA.

Chương trình YOUNG TALENT đã nhận được đánh giá tích cực từ nhiều học viên. Mọi người đều bất ngờ với lộ trình đào tạo bài bản 20 môn kết hợp tư duy và kỹ năng phục vụ mục đích phân tích. Nội dung chương trình mang tính thực tiễn cao, áp dụng ngay vào công việc, phù hợp với các bạn sinh viên hoặc vừa tốt nghiệp. TRẢI NGHIỆM LUÔN !

Lấy dữ liệu từ file Excel khác thông qua VBA

Thông thường, đối với nhiều người sử dụng Excel lâu năm sẽ nghĩ ngay đến VBA để giải quyết tình huống ghép nhiều hoặc ghép 2 file excel thành 1. VBA là một ngôn ngữ lập trình trên Excel, mục đích của VBA nhằm tự động hóa các thao tác mà người dùng Excel bằng cách viết một đoạn code.

Điểm mạnh của code VBA đó là người dùng chỉ viết 1 lần duy nhất để thiết lập ban đầu, sau đó chỉ cần click một nút được thiết lập để run – chạy đoạn code đã thiết lập sẵn đó.

Tuy nhiên, điểm yếu của VBA đó là người dùng rất khó tiếp cận. Bản chất của VBA là một ngôn ngữ lập trình, yêu cầu sự logic và người học vô cùng cần mẫn, chuyên sâu mới có thể viết được. Thông thường ít nhất mất 3 tháng để các bạn có thể viết được ngôn ngữ này, nhưng chỉ sau 1 tháng không sử dụng bạn có thể quên luôn cách viết.

Bên cạnh đó các file cần tổng hợp cần có cấu trúc giống nhau về tên cột, số lượng cột, dòng và vùng dữ liệu. Nếu file khác số lượng cột thì code VBA sẽ không hiểu và báo lỗi không thực hiện được.

Phía dưới là đoạn code VBA dành cho bạn nào mong muốn được biết:

Sub merge_all()
	Dim cnn As ADODB.Connection
	Dim rst As ADODB.Recordset
	Dim s As Worksheet
	Dim I As Long, d As Long, CountFiles As Long, J As Long

	SheetName = “Sheet1” & “$” — Sheet1 là tên sheet của file bạn cần tổng hợp
	RangeAddress = “A1:U1000” — đây là vùng dữ liệu của sheet mà bạn cần tổng hợp
	Dim files As Variant
	files = Application.GetOpenFilename(, , , , True)

	If VarType(files) = vbBoolean Then Exit Sub

	Set s = Sheets(“Master”) – tên sheet này tuy bạn chọn

	For d = LBound(files) To UBound(files)
	Set cnn = GetConnXLS(files(d))
	If cnn Is Nothing Then
	MsgBox “kiem tra lai du lieu file: ” & files(d)
	Exit Sub
	End If

	Set rst = cnn.Execute(“SELECT *,””” & files(d) & “”” as [From File] FROM [” & SheetName & RangeAddress & “]”)

	CountFiles = CountFiles + 1

	If CountFiles = 1 Then
	For J = 0 To rst.Fields.Count – 1
	s.Cells(3, J + 1).Value = rst.Fields(J).Name
	Next J
	End If

	I = I + s.Range(“A” & 4 + I).CopyFromRecordset(rst) – A4 hiện tại là ô dán dữ liệu vào, sửa nếu thay đổi

	rst.Close
	Set rst = Nothing
	cnn.Close
	Set cnn = Nothing

	Next d
	MsgBox “hoan thanh”
	End Sub

Không dễ để viết được đoạn code phía trên, ghi nhớ cho lần sau đúng không các bạn! Do vậy, trong bài viết này mình sẽ chia sẻ các bạn thêm cách đơn giản hơn. Bạn sẽ biết cách tổng hợp dữ liệu từ nhiều file Excel bằng Power Query. Bạn có thể ghép 2 file excel thành 1 hoặc nhiều hơn một cách dễ dàng.

Power Query là một công cụ mới được phát triển bởi Microsoft trong khoảng 5 năm gần đây. Tuy nhiên, ở Việt Nam việc được nhắc đến, hay sử dụng còn rất hạn chế. Các tài liệu về Power Query chủ yếu là bằng tiếng anh.

Do vậy, một số lượng lớn người sử dụng chưa tiếp cận được.

Giống với VBA, thì Power Query giúp người dùng có thể tự động hóa dữ liệu thông qua việc thiết lập duy nhất 1 lần ban đầu.

Điểm khác của Power Query đó là người dùng không cần biết viết ngôn ngữ lập trình khó hiểu như VBA, bạn chỉ cần thao tác chọn – thả vô cùng đơn giản, tiết kiệm thời gian, đơn giản dễ học. Đây chính là điểm ưu việt của Power Query so với VBA.

Với cách làm này, bạn có thể xử lý rất nhiều tình huống cụ thể trong công việc mà không cần biết đến coding.

Microsoft Power Query phát triển trong thời gian gần đây, nhằm giúp đại đa số người dùng có thể tăng hiệu quả công việc, thay vì lặp lại các thao các hàng ngày. Thì bạn có thể sử dụng Power Query như một công cụ cứu cánh, tiết kiệm thời gian. Bạn hãy tham khảo cách cách gộp nhiều file excel thành 1 bằng cách ứng dụng power query bên dưới nhé.

Cách tổng hợp dữ liệu từ nhiều file Excel về một sheet Excel duy nhất

Trường hợp đầu tiên mình muốn giới thiệu các bạn cách tổng hợp dữ liệu từ các nguồn khác nhau với template của mỗi file là giống nhau. Mục tiêu của các bạn đó là tất cả các file nhận được sẽ tự động trở thành một sheet tổng hợp cho mục đích cá nhân, ví dụ như dùng để tổng hợp danh sách nhân viên theo từng tháng, hoặc báo cáo doanh số theo từng tháng, năm.

Bước đầu tiên: tổng hợp, download toàn bộ các file mà các bạn nhận được bỏ chung vào 1 folder. Đối với cách này, sẽ không phân biệt tên của file, quan trọng các file có chung template, tức là có tiêu đề giữa các file giống nhau là được.

Bước 2:Mở 1 file excel mới dùng để tổng hợp toàn bộ các file mà các bạn nhận được và thao tác lần lượt như sau:

Data --> Get Data --> From File --> From Folder

H1. Cách tổng hợp dữ liệu từ nhiều file excel về một sheet Excel duy nhất (cách gộp nhiều file excel thành 1)

Xem thêm: Hướng dẫn sử dụng Power Query để chuẩn hóa dữ liệu

Bước 3: Chọn nguồn dữ liệu folder mà các bạn đã bỏ tất cả các file cần cập nhập theo các thao tác

Browse --> Tìm kiếm tên folder bỏ các file đã download hoặc tập hợp (ở đây mình ví dụ là folder Data Source) --> Ok --> Ok

H2. Cập nhật dữ liệu từ nhiều file excel về một file excel duy nhất – Tìm folder đã tập hợp lại trước đó

Bước 4: Sẽ xuất hiện 1 bảng thể hiện tất cả các file mà các bạn đã bỏ vào folder --> chọn Transform Data

H3. Lấy dữ liệu từ nhiều file excel – Khi xuất hiện bảng trên, nhấn Transform Data

Sau khi chọn Transform data, thì bạn sẽ vào bên trong môi trường của Power Query.

Tiếp sau đó mình sẽ lấy data của tất cả các file vào cùng 1 sheet bằng cách sau:

Add Column --> Custom Column --> Viết câu lệnh (Excel.Workbook --> Chọn Content bên cột Available Column , true ) --> OK

H4. Cập nhật dữ liệu từ nhiều file excel về một file excel duy nhất – Viết câu lệnh và cọn Cột Content như hình trên

Bước 5: Sau đó bạn tìm cột có tên là Custom thực hiện các bước như hình vẽ phía dưới để kéo data về.

H5. Hướng dẫn cập nhật dữ liệu giữa các sheet trong excel – Thực hiện các bước như hình để kéo data về

Bước 6: Bạn tìm cột Custom.Kind --> Chọn Sheet --> OK

Lý do vì sao chọn sheet, vì một số file bạn nhận được trong tương lai của các phòng ban không được định dạng table. Do vậy, tên sheet gần như không thay đổi theo thời gian. Nên đây là cách để tránh trường hợp dữ liệu không hiểu.

H6. Cách tổng hợp dữ liệu từ nhiều file excel, (ghép file excel )- Tiếp tục như hình

Bước 7: Bạn tìm đến cột Costom.Data --> Click chuột phải --> Remove Other Columns thì các cột không cần thiết sẽ được xóa bỏ, chỉ để lại cột cần thiết để lấy dữ liệu.

H7. Cách gộp nhiều file excel thành 1 (ghép file excel) – Tiếp tục như hình

Xem thêm: Tự động hóa excel bằng power query thay cho vba

Bước 8: Sau đó bạn xổ data từ con trỏ ngay tại B1 trong hình vẽ và ấn OK. Mục đích bước này để trích xuất toàn bộ dữ liệu từ tất cả các file theo các cột cần lấy. Bạn có thể bỏ chọn những cột mà bạn nghĩ không cần thiết, hoặc có thể remove column ở bước tiếp theo

H8. Trích xuất toàn bộ dữ liệu từ tất cả các file theo các cột cần lấy (ghép file excel)

Bước 9: Sau đó dữ liệu được lấy ra, bạn cần đưa dòng đầu tiên của dữ liệu thành cột tiêu đề. Bạn thực hiện các bước như trong hình.

Click con trỏ vào biểu tượng table góc trái màn hình --> lựa chọn Use First Row as Headers

Vậy là toàn bộ dữ liệu các bạn cần tổng hợp đã nằm trong 1 sheet. Bây giờ mình cần đưa ra giao diện Excel thuần túy.

H9. Cách tổng hợp dữ liệu từ nhiều file excel – Đưa dòng đầu tiên của dữ liệu thành cột tiêu đề

Bước 10: Chọn Home --> Close & Load --> Close & Load To --> Table --> New Worksheet --> OK

H10. Đưa ra giao diện Excel thuần túy

H11. Chọn như hình

Sau 10 bước trên, bạn chỉ thiết lập 1 lần duy nhất. Nếu bạn có 1 file có template giống vậy các tháng sau, hoặc tuần sau. Bạn chỉ cần bỏ file mới vào folder và ấn Data --> refresh all. Toàn bộ dữ liệu của các bạn sẽ được cập nhập một cách tự động và nhanh chóng.

Cách tổng hợp dữ liệu từ nhiều file Excel thành một file Excel có nhiều sheet

Trường hợp thứ 2: Tổng hợp dữ liệu từ các nguồn khác nhau với template mỗi file là khác nhau. Mục tiêu nhằm thiết lập công thức giữa các file với nhau để ra mục tiêu cụ thể.

Bước 1: Download file mà các bạn nhận được, bỏ chung vào 1 folder nhằm mục đích dễ quản lý. Các file download này phải thống nhất tên file để phần mềm có thể nhận diện cho lần tiếp theo

Bước 2. Mở file mà bạn cần tổng hợp toàn bộ các file mà bạn nhận được thành các sheet khác nhau để thiết lập công thức:

Data --> Get Data --> From File --> From Workbook

H12. Tổng hợp dữ liệu từ nhiều file excel thành một file excel có nhiều sheet – Mở file excel cần tổng hợp

Bước 3: Chọn tên file mà bạn muốn cập nhập hàng ngày. Lưu ý, tên file không thay đổi giữa các lần cập nhập. Nếu file mới được bỏ vào folder thì file cũ phải xóa đi. Đồng thời tên của file phải giữa nguyên so với lần đầu tiên thiết lập.

Bước 4. Thực hiện thao tác load file ra sheet. Lưu ý, các file bạn chọn có thể file được định dạng table hoặc không, do đó nên chọn tên sheet để load ra. Như hình phía dưới thì table sẽ có ký hiệu như Table1 và tên sheet sẽ có ký hiệu như DATA.

H13. Thực hiện thao tác load file ra sheet

Dữ liệu các bạn chọn được load ra sheet của file Excel.

H14. Dữ liệu được chọn load ra sheet của file excel

Lặp lại các thao tác trên tương ứng với số lượng file cần cập nhập vào 1 sheet. Các bước trên chỉ thực hiện 1 lần. Ngày hôm sau, nếu có các file mới. Các bạn chỉ việc download và bỏ vào folder với tên file không đổi so với ngày hôm trước, xóa file cũ đi. Để báo cáo được cập nhập.

Bước 5: Cập nhập các file mới nhận được hàng ngày bằng cách

Data --> Refresh All --> Refresh All

H15. Cập nhật các file mới nhận được

Vậy là toàn bộ các file mà hàng ngày bạn phải copy thủ công vào từng sheet đã được cập nhập tự động. Các thao tác thiết lập ban đầu chỉ làm 1 lần duy nhất. Việc này sẽ tiết kiệm được rất nhiều thời gian cho các bạn

Các cách làm việc trên ứng dụng Power Query trong Excel để tự động hóa dữ liệu. Vậy để học Power Query ở đâu? Tại Uniace chúng tôi có các khóa học từ cơ bản đến nâng cao cho các bạn, nhằm tự động hóa dữ liệu. Vui lòng tham khảo khóa học nền tảng của chúng tôi.

Làm sao để học power, học power bi ở đâu hiệu quả

Cách tổng hợp dữ liệu từ nhiều file Excel vào 1 file không cần mở file

Tương tự như cách làm phía trên, bạn cũng có thể làm các thao tác tương tự để có thể . Thay vì hàng ngày phải thực hiện các thao tác thủ công như mở file nhận được --> copy --> paste vào từng sheet của 1 file để chạy công thức. Thì việc thiết lập như các bước phía trên đã giúp các bạn tiết kiệm được rất nhiều thời gian.

Như vậy là Uniace vừa hướng dẫn đến bạn cập nhật dữ liệu từ nhiều file excel về một file excel duy nhất ( tổng hợp dữ liệu từ nhiều sheet trong excel). Chúc bạn thực hiện thành công. Vui lòng liên hệ với chúng tôi, để được tư vấn cụ thể. Giải đáp các thắc mắc và giúp các bạn đến với các khóa học phù hợp với ngành nghề, lĩnh vực và khả năng của các bạn.

Tham khảo các bài viết hữu ích khác của Uniace tại Chuyên Đề Excel: