Hướng dẫn sử dụng VBA làm việc với file trong thư mục khác

Bạn sẽ làm thế nào nếu muốn tập hợp dữ liệu từ nhiều file (workbook) vào chung 1 file? Chắc hẳn chúng ta thường làm là mở từng file đó ra rồi copy / paste dữ liệu. Vậy thì có cách nào để mở từng file đó một cách tự động?
Câu trả lời rất đơn giản: Sử dụng VBA để tự động hóa các thao tác.
Sau đây chúng ta hãy cùng Học Excel Online tìm hiểu những câu lệnh trong VBA giúp mở 1 file trong máy tính một cách tự động:

1. Lấy danh sách tên file và đường dẫn tới thư mục chứa file

	Sub GetDirNames() 'Lấy danh sách tên file trong thư mục
	Const sPath="C:\Test\" 'Đường dẫn tới thư mục
	Dim sFil As String
	sFil=Dir(sPath & "*.xl*") 'đuôi file .xlsx .xlsb .xlsm... đều sử dụng được
	Do While sFil <> ""
	Range("D65536").End(xlUp)(2).Value=sPath & sFil 'Lấy danh sách các tên file trong thư mục
	sFil=Dir
	Loop
	End Sub

Để lấy danh sách tên file trong thư mục, chúng ta cần xác định các yếu tố:

• Đường dẫn tới thư mục
• Loại file: căn cứ vào đuôi file (phần sau dấu chấm, thường mặc định là ẩn đi trong tên file)
• Để lấy được tất cả các tên file thì chúng ta sẽ xét theo 1 vòng lặp với những file có tồn tại

2. Mở file và sao chép nội dung

Trong trường hợp 1, chúng ta đã nắm được cách lấy tên và đường dẫn tới 1 file nằm trong 1 thư mục bất kỳ. Việc mở thư mục đó và copy nội dung sang file khác sẽ được thực hiện như sau:

	Sub OpenImp() 'Mở file và sao chép dữ liệu sang file khác
	Const sPath="C:\Test\" 'Thư mục chứa file
	Dim sFil As String
	Dim owb As Workbook
	Dim ws As Worksheet
	Set ws=Sheet1 'Xác định tên Sheet chứa kết quả (đích)
	sFil=Dir(sPath & "*.xl*") 'Xác định loại file cần lấy
	Do While sFil <> ""
	Set owb=Workbooks.Open(sPath & sFil) 'Mở file cần copy
	'Copy vùng dữ liệu từ A2 đến dòng cuối cột F
	Range("A2", Range("F" & Rows.Count).End(xlUp)).Copy ws.Range("A" & Rows.Count).End(xlUp)(2)
	'Đóng workbook mà không lưu
	owb.Close False
	sFil=Dir
	Loop
	End Sub* Lưu ý: Xác định rõ 2 đối tượng: File cần mở để lấy nội dung, gồm sheet chứa nội dung, vùng nội dung cần copy File đích cần lấy kết quả, gồm Sheet chứa kết quả, vùng kết quả Với mỗi đối tượng cần xác định được quy trình, thứ tự thực hiện rõ ràng để tránh nhầm lẫn. 3. Lấy nội dung theo điều kiện từ 1 workbook khác Trong trường hợp chúng ta cần lấy dữ liệu theo điều kiện xác định thì sẽ làm thế nào?  Khác với trường hợp 2 là lấy tất cả nội dung, trường hợp này chúng ta cần kết hợp với việc  lọc dữ liệu, sau đó lấy kết quả lọc được để sao chép sang file khác. Câu lệnh như sau: Sub OpenImp3() 'Lấy nội dung từ 1 vùng xác định Const sPath="C:\Test\" 'Đường dẫn tới thư mục Dim sFil As String Dim owb As Workbook Dim ws As Worksheet Dim lr as long Set ws=Sheet1 sFil=Dir(sPath & "*.xl*") Do While sFil <> "" Set owb=Workbooks.Open(sPath & sFil) 'Mở workbook cần lấy nội dung 'Lọc nội dung cần lấy trong vùng từ A1 tới dòng cuối cột F, giá trị lọc là England ở cột A Range("A1", Range("F" & Rows.Count).End(xlUp)).AutoFilter Field:=1, Criteria1:="England" 'Nếu có kết quả lọc thì copy dữ liệu lr=Range("F" & Rows.Count).End(xlUp).Row If lr > 1 Then Range("A2", Range("F" & Rows.Count).End(xlUp)).Copy ws.Range("A" & Rows.Count).End(xlUp)(2) 'Đóng workbook đã mở, không lưu file owb.Close False sFil=Dir Loop End Sub