Theo thời gian, rất dễ dàng tạo và thu thập một số lượng lớn sổ làm việc Excel. Giả sử rằng bạn có cả đống sổ làm việc mà bạn cần thực hiện cùng một thay đổi. Ví dụ: bạn có thể cần thay đổi giá trị được lưu trữ trong ô A10 của mỗi trang tính trong mỗi sổ làm việc.

Nếu bạn chỉ có một vài sổ làm việc để thay đổi, nhiệm vụ khá dễ dàng: Tải từng sổ làm việc và lần lượt thực hiện thay đổi đối với từng sổ làm việc đó. Nếu bạn có vài trăm sổ làm việc cần thực hiện thay đổi, thì nhiệm vụ sẽ trở nên khó khăn hơn.

Nếu bạn dự đoán chỉ cần thực hiện tác vụ này một lần, thì giải pháp đơn giản nhất là tạo tệp văn bản chứa đường dẫn và tên tệp của mỗi sổ làm việc, mỗi sổ làm việc một dòng. Ví dụ: bạn có thể kết thúc với một tệp có các mục nhập như sau:

c:\myfiles\first workbook.xlsx c:\myfiles\second workbook.xlsx c:\myfiles\third workbook.xlsx

Tệp có thể có bao nhiêu dòng trong đó nếu cần; nó không thực sự quan trọng. Điều quan trọng là mỗi dòng là một đường dẫn và tên tệp hợp lệ, và không có dòng trống nào trong tệp.

Bạn có thể dễ dàng tạo một tệp như vậy bằng cách hiển thị cửa sổ nhắc lệnh, điều hướng đến thư mục chứa các sổ làm việc và đưa ra lệnh sau:

dir /b > myfilelist.txt

Mỗi tệp trong thư mục kết thúc bằng tệp myfilelist.txt. Bạn sẽ cần tải tệp văn bản vào một trình soạn thảo văn bản và kiểm tra nó để có thể xóa các mục nhập không liên quan. (Ví dụ: myfilelist.txt sẽ kết thúc trong danh sách.) Bạn cũng sẽ cần thêm tên đường dẫn vào đầu mỗi dòng trong tệp.

Khi tệp hoàn tất, bạn có thể khởi động Excel và sử dụng macro để đọc tệp văn bản, tải từng sổ làm việc được liệt kê trong tệp văn bản, từng bước qua từng trang tính trong sổ làm việc đó, thực hiện thay đổi thích hợp và lưu sổ làm việc. Macro sau sẽ thực hiện các tác vụ này một cách độc đáo.

Sub ChangeFiles1()

Dim sFilename As String     Dim wks As Worksheet

Open "c:\myfiles\myfilelist.txt" For Input As #1     Do While Not EOF(1)

Input #1, sFilename  ' Get workbook path and name         Workbooks.Open sFilename

With ActiveWorkbook             For Each wks In .Worksheets                 ' Specify the change to make                 wks.Range("A1").Value = "A1 Changed"

Next         End With

ActiveWorkbook.Close SaveChanges:=True     Loop     Close #1 End Sub

Mặc dù cách tiếp cận này hoạt động tốt nếu bạn chỉ phải xử lý một loạt tệp sổ làm việc, nhưng nó có thể được thực hiện linh hoạt hơn nhiều nếu bạn dự đoán cần thực hiện những thay đổi như vậy trong tương lai. Tất nhiên, rắc rối lớn nhất là tập hợp tệp myfilelist.txt lại với nhau mỗi khi bạn muốn xử lý một loạt tệp. Tính linh hoạt được thêm vào nếu macro chỉ có thể sử dụng một thư mục và sau đó tải từng sổ làm việc từ thư mục đó.

Sub ChangeFiles2()

Dim MyPath As String     Dim MyFile As String     Dim dirName As String     Dim wks As Worksheet

' Change directory path as desired     dirName = "c:\myfiles\"



MyPath = dirName & "*.xlsx"

MyFile = Dir(MyPath)

If MyFile > "" Then MyFile = dirName & MyFile

Do While MyFile <> ""

If Len(MyFile) = 0 Then Exit Do

Workbooks.Open MyFile

With ActiveWorkbook             For Each wks In .Worksheets                 ' Specify the change to make                 wks.Range("A1").Value = "A1 Changed"

Next         End With

ActiveWorkbook.Close SaveChanges:=True

MyFile = Dir         If MyFile > "" Then MyFile = dirName & MyFile     Loop End Sub

Macro này sử dụng bất kỳ thư mục nào bạn chỉ định cho biến dirName.

Mọi tệp sổ làm việc (kết thúc bằng phần mở rộng .Xlsx) đều được tải và xử lý.

Một cách tiếp cận khác là để macro hỏi người dùng thư mục nào nên được xử lý. Bạn có thể sử dụng hộp thoại Tệp Excel tiêu chuẩn để thực hiện việc này, theo cách được hiển thị trong macro sau.

Public Sub ChangeFiles3()

Dim MyPath As String     Dim MyFile As String     Dim dirName As String

With Application.FileDialog(msoFileDialogFolderPicker)

' Optional: set folder to start in         .InitialFileName = "C:\Excel\"

.Title = "Select the folder to process"

If .Show = True Then             dirName = .SelectedItems(1)

End If     End With

MyPath = dirName & "\*.xlsx"

myFile = Dir(MyPath)

If MyFile > "" Then MyFile = dirName & MyFile

Do While MyFile <> ""

If Len(MyFile) = 0 Then Exit Do

Workbooks.Open MyFile

With ActiveWorkbook             For Each wks In .Worksheets                 ' Specify the change to make                 wks.Range("A1").Value = "A1 Changed"

Next         End With

ActiveWorkbook.Close SaveChanges:=True

MyFile = Dir         If MyFile > "" Then MyFile = dirName & MyFile     Loop End Sub

_Lưu ý: _

Nếu bạn muốn biết cách sử dụng các macro được mô tả trên trang này (hoặc trên bất kỳ trang nào khác trên trang ExcelTips), tôi đã chuẩn bị một trang đặc biệt bao gồm thông tin hữu ích.

ExcelTips là nguồn của bạn để đào tạo Microsoft Excel hiệu quả về chi phí.

Mẹo này (8939) áp dụng cho Microsoft Excel 2007, 2010, 2013, 2016, 2019 và Excel trong Office 365. Bạn có thể tìm thấy phiên bản của mẹo này cho giao diện menu cũ hơn của Excel tại đây: