利用VBA将xls文件批量转为xlsx
对于单个xls文件,只需要利用“另存为"操作来更改文件格式就能够简单地完成,但是对于多个xls甚至是几十个xls文件时,这个工作量就比较麻烦了,因此需要借助exel开发工具中带有的VBA (Visual Basic宏语言),它是一种应用程式视觉化的 Basic 脚本,可以便捷地处理重复性的操作。
在对xls进行处理之前,需要首先分辨一下文件格式
由于部分xls可能是由txt格式文件直接更改文件后缀名得到的,这些xls文件中的记录数会超过2^16=65536(xls格式所支持的最大记录数)。
这类直接改后缀名的文件在打开时可能会出现以下提示:
在某些软件中如果读取该类xls文件,会出现超过65536部分的记录(即超过65536的行)丢失的情况,比如MATLAB中的xlsread来读取xls文件。但是如果将xls文件另存为xlsx格式后,由于xlsx格式支持的最大2^20=1048576行,可以完整保存xls中所有数据,在软件中读取时也不会出现数据丢失的情况。
对于单个xls文件:
1.点击“文件”,然后选择“另存为”,并保存到桌面
2.然后点击“保存类型”,选择“Excel工作簿(*.xlsx)”,然后点“保存"
对于多个xls文件:
利用VBA脚本进行处理,使用代码如下:
'***********访问当前文件夹下所有子文件夹及文件,
Dim iFile(1 To 100000) As String
Dim count As Integer
Sub xls2xlsx()
iPath = ThisWorkbook.Path
On Error Resume Next
count = 0
zdir iPath
For i = 1 To count
If iFile(i) Like "*.xls" And iFile(i) <> ThisWorkbook.FullName Then
MyFile = iFile(i)
FilePath = Replace(MyFile, ".xls", ".xlsx")
If Dir(FilePath, 16) = Empty Then
Set WBookOther = Workbooks.Open(MyFile)
Application.ScreenUpdating = False
ActiveWorkbook.SaveAs Filename:=FilePath, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
WBookOther.Close SaveChanges:=False '解决不能close 文件问题
Application.ScreenUpdating = True
End If
End If
Next
End Sub
Sub zdir(p) '访问当前文件夹下所有子文件夹及文件
Set fs = CreateObject("scripting.filesystemobject")
For Each f In fs.GetFolder(p).Files
If f <> ThisWorkbook.FullName Then count = count + 1: iFile(count) = f
Next
For Each m In fs.GetFolder(p).SubFolders
zdir m
Next
End Sub在这里插入代码片
具体的操作过程如下:
1.在存放需要进行处理的xls文件的文件夹下新建一个xlsx文件
2.选择”开发工具“,选择Visual Basic,进入脚本编辑器
3.选择sheet1,贴入代码
4.点击上方的运行,选择”运行子过程/窗体“,即可完成操作
操作演示图如下:(该过程是在Office旧版,2013以上版本有所不同)
Tips:(注意事项)
1. 如果在excel工具栏中没有出现”开发工具选项“,可能是没有选择显示该功能,需要自行将该功能添加到工具栏,过程如下:
选择”文件“进入菜单栏,选择”选项“,进入excel选项中,然后选择”自定义功能区“,在右边列表中找到”开发工具选项“,打勾将其添加,确定后保存退出。
2. 在代码运行过程中会出现不断打开excel并关闭的现象,这是程序中设计的正常操作,这一过程中由于进行数据读取保存可能会有些电脑卡顿,是正常情况。当脚本执行完后,文件夹下会出现保存好的xlsx文件,原有的xls文件不会消失。
3. 如果需要反过来将xlsx变为xls,可以将代码中”replace“中的xls和xlsx调换位置,一般情况下不会对xlsx文件格式进行降级,因此谨慎修改。
参考来源:
如何将xls批量转换成xlsx