随着时间的流逝,创建和收集大量Excel工作簿非常容易。假设您有一堆需要进行相同更改的工作簿。例如,您可能需要更改每个工作簿中每个工作表的单元格A10中存储的值。

如果只需要更改几个工作簿,则任务非常简单:加载每个工作簿,然后依次对每个工作簿进行更改。如果您有数百本需要进行更改的工作簿,那么任务将变得更加艰巨。

如果您预计只需要执行一次此任务,那么最简单的解决方案是创建一个文本文件,其中包含每个工作簿的路径和文件名,每行一个工作簿。例如,您可能最终得到一个具有如下条目的文件:

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

该文件中可以包含必要的多行;没关系。重要的是,每一行都是有效的路径和文件名,并且文件中没有空行。

通过显示命令提示符窗口,导航到包含工作簿的目录,然后发出以下命令,您可以最轻松地创建这样的文件:

dir /b > myfilelist.txt

目录中的每个文件都以myfilelist.txt文件结尾。您需要将文本文件加载到文本编辑器中,然后将其检出,以便删除多余的条目。 (例如,myfilelist.txt将最终出现在列表中。)您还需要将路径名添加到文件中每一行的开头。

文件完成后,您可以启动Excel并使用宏读取文本文件,加载文本文件中列出的每个工作簿,逐步浏览该工作簿中的每个工作表,进行适当的更改,然后保存该工作簿。下面的宏将很好地执行这些任务。

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

虽然这种方法在只需要处理一批工作簿文件的情况下效果很好,但是如果您预计将来需要进行此类更改,则可以使其变得更加灵活。当然,最大的麻烦是每次您要处理一批文件时都将myfilelist.txt文件放在一起。如果宏可以仅使用一个目录,然后从该目录加载每个工作簿,则将增加灵活性。

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

该宏使用您为dirName变量指定的任何目录。

任何工作簿文件(以.Xlsx扩展名结尾)都将被加载和处理。

另一种方法是让宏询问用户应该处理哪个目录。您可以按照以下宏中显示的方式使用标准的“ Excel文件”对话框来执行此操作。

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

注意:

如果您想知道如何使用此页面(或_ExcelTips_网站上的任何其他页面)中描述的宏,我准备了一个特殊页面,其中包含有用的信息。

_ExcelTips_是您进行经济高效的Microsoft Excel培训的来源。

本技巧(8939)适用于Microsoft Excel 2007、2010、2013、2016、2019和Office 365中的Excel。您可以在此处为Excel的较旧菜单界面找到此技巧的版本: