EXCEL VBA零基础教程：文件批量修改方法汇总（办公室职场必备）

导语：

相比于公式，VBA在处理多个EXCEL文件或工作表具有强大的优势，今天承接上期的内容，重点汇总一下常见的多文件操作。

本期重点讲解这几个问题：

• 单文件操作：创建、打开、保存、关闭工作薄（批量修改EXCEL文件基础）。

• 多文件夹操作：批量创建文件夹

• 多文件夹操作：批量创建EXCEL文件

• 多文件夹操作：批量修改EXCEL文件

文件新建，打开，保存，另存为操作，关闭操作。主要用的是Workbooks对象。

Workbooks操作	操作说明	类似于快捷键功能
Workbooks.Add	新建一个空白的工作簿，相当于“文件->新建”命令。	Ctrl N
Workbooks.Open	打开文件。相当于“文件->打开”命令。	Ctrl O
Workbooks.Save	保存文件	Ctrl S
Workbooks.SaveAs	另存为文件
Workbooks.Close	关闭文件	Ctrl W

主要用法：

• Workbooks.Add：直接这样写就可以新建一个空的工作表。

Sub 新建工作薄()

Workbooks.Add

End Sub

• Workbooks.Open ：两种写法 。参数为：文件存放路径 文件名

1. Workbooks.Open FileName:='D:今日头条80502Excel演示.xls'

2. Workbooks.Open 'D:今日头条80502Excel演示.xls'

示例：

Sub 打开文件()

Workbooks.Open 'D:\今日头条\20180512\EXCEL 培训文件20180512.xls'

End Sub

• Workbooks（参数）.Save：参数为打开文件的顺序或者文件名。如果保存已激活的工作表直接用ActiveWorkbook.Save就可以了。

示例：

Sub 保存文件文件名()

Workbooks('膜系数.xls').Save

'或 ActiveWorkbook.Save

End Sub

Sub 保存文件序号()

Workbooks(3).Save

End Sub

• Workbooks.SaveAs：当前打开文件另存为。

Workbooks(1).SaveAs ('D:\今日头条\20180512\工作表2.xls')

• Workbooks.Close:关闭已打开的文件。

1. Workbooks.Close‘关闭当前已打开的文件。这个如果未保存的话会提示是否保存。

2. Workbooks(1).Close SaveChanges:=False '关闭工作簿，并且不保存，改为True则是保存并关闭。

小结：一个完整的文件操作一般是这样的流程的：打开文件（或新建）>修改文件（或读取）>保存或不保存文件>关闭文件。每个步骤使用对应的对象操作就可以了。熟练之后每次只需要将 修改文件 的部分修改下就可以了。Word、PPT、Acess中的VBA也是这个流程，也有对应的操作方法。

下面看一个完整的示例（打开文件、修改文件、保存文件、关闭文件）：

演示VBA宏代码功能：向EXCEL文件中第一个工作薄前十行前十列依次写入“今日头条”，并设置字体大小和背景颜色。

演示代码：

Sub 打开修改文件并保存()

Path = 'D:\今日头条\20180512\测试.xls'

Workbooks.Open Filename:=Path '打开文件

Sheets(1).Activate '修改文件

For i = 1 To 10

For j = 1 To 10

Cells(i, j) = '今日头条'

Cells(i, j).Font.Size = 18

Cells(i, j).Interior.ColorIndex = i

Next

Next

ActiveWorkbook.Save '保存文件

ActiveWorkbook.Close '关闭文件

End Sub

文件夹、多文件操作。

在网上看到VBA中常用的比较常用的遍历文件的方法是Dir函数。但是自从某次工作中发现DIR函数不能识别所有的文件名，就改用FSO对象系统了。FSO的英文全称是File System Object，翻译成中文叫文件系统对象。具有逻辑简单，操作方法丰富的方法（其实常用的就那几个），百度词条上有对这个对象的详细说明，本文重点讲解几个常用的对象和方法。

File System Object系统主要有这么几个对象：FSO对象、Drive对象、Folder对象、FIle对象、TextStream对象。常用的是FSO对象，Folder对象、File对象。主要功能是获取我们要操作的文件或文件夹。

• FSO文件夹操作说明:
  

对象操作方法	用法说明	参数说明
CreateFolder	创建一个文件夹。	object.CreateFolder(文件夹名称)
DeleteFolder	删除一个文件夹。	object.DeleteFolder(文件夹名称)
MoveFolder	移动一个文件夹。	object.MoveFolder(源文件夹,目标文件夹)
CopyFolder	复制一个文件夹。	object.CopyFolder(源文件夹,目标文件夹)
FolderExists	查找一个文件夹是否在驱动器上。	object.FolderExists(文件夹名称)
GetFolder	获得已有Folder对象的一个实例。	object.GetFolder(文件夹名称)
GetParentFolderName	找出一个文件夹的父文件夹的名称。	object.GetParentFolderName(文件夹名称)
GetSpecialFolder	找出系统文件夹的路径。	object.GetSpecialFolder文件夹名称)

看起来挺多的，用的最多的是 GetFolder方法。偶尔会用到创建、删除、移动、复制几个方法，其他了解下就可以，做到想用的时候知道有这么个方法能够实现。Folder对象和FIle对象有类似的方法，比如创建、删除、移动、复制等，大家知道有这个方法，想用的时候百度一下就可以。

GetFolder方法主要目的是为了获取某一个文件下的说用文件或文件夹名称。获取到名称之后，就可以用上面的EXCEL文件操作方法进行批量修改。

• 用法：要创建FSO对象可以采用两种方法：

1、一种是将一个变量声明为FSO对象类型：

Dim fsoTest As New FileSystemObject

2、另一种是通过CreateObject方法创建一个FSO 对象，在实际使用中具体采用哪种声明方法，可根据个人的使用习惯而定。

Set fsoTest = CreateObject（“Scripting.FileSystemObject')

学会的常用的几个对象和方法基本上就可以操作我们硬盘里面的所有文件。

我们先用CreateFolder按照月份创建12文件夹：

按照月份创建了12个文件夹，代码如下：

Sub 创建文件夹()

i = 1

Set fso = CreateObject('scripting.filesystemobject') '设置FSO对象

For i = 1 To 12

fso.CreateFolder ('D:\今日头条\测试\2017年' Str(i) '月')

Next

End S

然后我们输出一下创建的文件夹名称：

主要通过fso.getfolder 获取文件夹对象。然后通过ff.SubFolders 获取到子文件件对象。

Sub 获取子文件夹()

i = 2

Set fso = CreateObject('scripting.filesystemobject') '设置FSO对象

Set ff = fso.getfolder('D:\今日头条\测试\') ' 获取文件夹对象

For Each folder In ff.SubFolders ' 遍历文件夹内所有子文件

Cells(i, 1) = folder.Name '文件夹名称

Cells(i, 2) = folder.Path '文件夹路径

Cells(i, 3) = folder '文件夹路径,和上面folder.Path意义相同

i = i 1

Next

End Sub

文件夹里面还没有文件，我们结合前面将的workbooks.add、workbooks.saveAs等方法在每个文件里面添加两EXCEL表格文件 。

这里使用的是Workbook.Add创建的文件，如果文件已经存在，那么将workbooks.add 换成 workbooks.open 就可任意的修改文件夹里面的表格文件了。

下面我们获取到所有已创建文件夹名称：

如果直接读取当前文件夹中的文件，可以这样写：

演示代码：

Set fso = CreateObject('scripting.filesystemobject') '设置FSO对象

Set ff = fso.getfolder('D:\今日头条\') ' 获取文件夹对象

For Each File In ff.Files

Cells(i, 1) = File.Name '文件名称

Cells(i, 2) = File.Path '文件路径

Cells(i, 3) = File '文件路径

i = i 1

Next

End Sub

最后我们要批量修改文件夹中的EXCEL文件。

需要将循环体中的内容修改下：

For Each File In ff.Files

Workbooks.open'打开工作薄

Sheets(1).Activate'激活sheet(1)表

修改代码（Cells（修改））

ActiveWorkbook.Save'保存文件

ActiveWorkbook.Close'关闭文件

Next

如果要有多个工作表 需要添加一个这样的循环

For Each Sheet In Sheets

Sheet .Activate

修改代码（Cells（修改））

Next

我们将之前所有创建的文件的第一行的背景颜色改一下，字体加粗一下：

基本上所有的大批量的表格修改都是按照这种逻辑的，有时还需要加入判断判定文件内容是否要修改。学会这个框架后，只需要将精力放在调整格式或内容上面的代码就可以了。然后写成单独的模块放到这个框架中，设置好文件夹路径，就可以修改了。

总结：如果工作中需要处理大量的表格文件，学会常用的FSO对象之后，基本上重复性的文件修改工作会减少一大半，更多的精力是需要对我们的VBA代码进行优化。有时这种方式修改的数据并不是十分的完善，需要对我们修改过的数据进行记录，对比，核对、优化等。如果文件超过1000多个的时候，我们还需要进一步的优化，有些录制的代码并不是十分的高效。

其他方面的文件的应用还有文件重命名、多文件数据合并，对比分析，去重。这样的内容会在稍后讲解。

---

最后，大家觉得还不错 ，求关注、求转发、求收藏。童鞋们有什么问题尽管提，有机会会作为作为一篇文章仔细讲解。