|
1.需求场景
工作中常遇到需要将大量数据填入表格模板，并保存为单独的Excel文件的情况。例如HR部门将掌握的人员基本信息批量输出为统一格式的信息表；学校将汇总的全部成绩数据为每名学生生成格式统一的成绩单；将汇总的工作记录信息生成格式统一的日志报表……诸如此类的情况。例如为下面表格中的人员批量生成个人信息表（套模板）。
2.解决思路
此类场景下的特点是，基础数据为大量汇总的格式一致但内容不同的数据，需要输出的表格为格式统一的模板化表格。因此可以把这个处理过程按照手动处理的顺序想象为一个反复套模板的过程。既然是反复重复一个相同的过程，就可以用一个循环来解决。模板可以提前设计好，模板中不变的内容事先在表格中填好，变化的数据从基础数据表格中读取。
3.VBA实现
'定义工作簿变量
Dim wb As Workbook
'定义工作表变量
Dim ws, sh As Worksheet
'定义临时存储基本信息数据各字段的变量
Dim pName, pSex, pBirthday, pJob, pLocation, pSchool, pLevel, pUnit, pWorkYear, pPhone, pCareer, pAwards As String
'循环变量
Dim i As Integer
Public Sub 按模板输出工作表()
'设置工作簿为当前工作簿
Set wb = Workbooks(1)
'设置基础数据表、填表模板到表变量
Set ws = Worksheets("数据")
Set sh = Worksheets("模板")
'关闭屏幕刷新
Application.ScreenUpdating = False
'逐行读取人员基本信息，每行信息(即每个人的信息)填入表格后另存为单独文件
For i = 2 To 22
'设置数据表为当前激活的表格
ws.Activate
'将当前行各列单元格信息存至临时变量
pName = Cells(i, 1).value
pSex = Cells(i, 2).value
pBirthday = Cells(i, 3).value
pJob = Cells(i, 4).value
pLocation = Cells(i, 5).value
pSchool = Cells(i, 6).value
pLevel = Cells(i, 7).value
pUnit = Cells(i, 8).value
pWorkYear = Cells(i, 9).value
pPhone = Cells(i, 10).value
pCareer = Cells(i, 11).value
pAwards = Cells(i, 12).value
'设置模板表格为当前激活的工作表
sh.Activate
'将临时变量信息写入模板对应单元格
Cells(2, 2).value = pName
Cells(2, 4).value = pSex
Cells(2, 6).value = pBirthday
Cells(3, 2).value = pJob
Cells(3, 4).value = pLocation
Cells(4, 2).value = pLevel
Cells(4, 4).value = pSchool
Cells(5, 2).value = pWorkYear
Cells(5, 4).value = pUnit
Cells(5, 6).value = pPhone
Cells(6, 2).value = pCareer
Cells(7, 2).value = pAwards
'填好表信息的模板工作表复制为新工作表
sh.Copy
'活动工作表名以人员姓名命名
ActiveSheet.Name = pName
'文件另存至当前文件夹“\人员信息表\”子目录下
ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\人员信息表\" & i & ".人员信息表(" & pName & ").xlsx"
'关闭保存过的文件
ActiveWorkbook.Close
Next i
'释放工作表和工作簿变量
Set sh = Nothing
Set ws = Nothing
Set wb = Nothing
'开启屏幕刷新
Application.ScreenUpdating = True
'显示提示信息
MsgBox "生成完毕,请到“人员信息表”目录下查看。"
End Sub
4.运行效果
运行过程中屏幕暂停刷新，运行结束后出现提示信息，模板显示出最后一次填入的数据信息。
打开生成文件所在目录可以看到，所有表格一次生成了，只是文件名的序号缺少了“1”，因为我在拼接输出文件名字符串时候用了循环变量i，而基础数据中有标题行，i是从第2行开始的。如果想要文件名序号从1开始，只需要把拼接文件名的语句中i改为i-1即可。
打开一个文件查看生成的文件表格是否达到预期效果。可以看到表名称已经以人员姓名命名了。模板中的空格填入了正确的人员信息。如果对输出格式还不满意，比如字体、对齐方式……只需要修改源文件中的模板表格格式即可，将原来生成目录下的文件清空，重新运行一次。收工下班！丝滑！
至于如何向表格中插入证件照片（图片）、如何输出为pdf文件等，后续会逐一与大家分享。
本着一次一个小技巧，上手就能学得会的原则，这次批量输出excel文件的方法就与大家分享到这里。
如果有帮助的话，欢迎点赞、回复或关注。
|