VBA—批量转化表格格式（xls转xlsx、csv转xlsx）

Excel可以通过另存为方式改变表格格式，如果遇到大批表格需要转化格式，手动另存为效率尤其慢。

我们可以使用VBA批量转化表格格式。

分享两种常用的表格格式转化：

一、xls转xlsx

把所有需要转化的报表放在同一个文件下，打开表格—复制以下代码即可。

'***********访问当前文件夹下所有子文件夹及文件,Dim iFile(1 To 100000) As StringDim count As IntegerSub xls2xlsx()iPath = ThisWorkbook.PathOn Error Resume Nextcount = 0zdir iPathFor i = 1 To countIf iFile(i) Like "*.xls" And iFile(i) <> ThisWorkbook.FullName ThenMyFile = iFile(i)FilePath = Replace(MyFile, ".xls", ".xlsx")If Dir(FilePath, 16) = Empty ThenSet WBookOther = Workbooks.Open(MyFile)Application.ScreenUpdating = FalseActiveWorkbook.SaveAs Filename:=FilePath, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=FalseWBookOther.Close SaveChanges:=False'解决不能close 文件问题Application.ScreenUpdating = TrueEnd IfKill myfile '删除原xls文件End IfNextEnd SubSub zdir(p) '访问当前文件夹下所有子文件夹及文件Set fs = CreateObject("scripting.filesystemobject")For Each f In fs.GetFolder(p).FilesIf f <> ThisWorkbook.FullName Then count = count + 1: iFile(count) = fNextFor Each m In fs.GetFolder(p).SubFolderszdir mNextEnd Sub

二、csv转xlsx

同上面的代码，只是将xls改为csv。

'***********访问当前文件夹下所有子文件夹及文件,Dim iFile(1 To 100000) As StringDim count As IntegerSub xls2xlsx()iPath = ThisWorkbook.PathOn Error Resume Nextcount = 0zdir iPathFor i = 1 To countIf iFile(i) Like "*.csv" And iFile(i) <> ThisWorkbook.FullName Thenmyfile = iFile(i)FilePath = Replace(myfile, ".csv", ".xlsx")If Dir(FilePath, 16) = Empty ThenSet WBookOther = Workbooks.Open(myfile)Application.ScreenUpdating = FalseActiveWorkbook.SaveAs Filename:=FilePath, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=FalseWBookOther.Close SaveChanges:=False'解决不能close 文件问题Application.ScreenUpdating = TrueEnd IfKill myfile '删除原csv文件End IfNextEnd SubSub zdir(p) '访问当前文件夹下所有子文件夹及文件Set fs = CreateObject("scripting.filesystemobject")For Each f In fs.GetFolder(p).FilesIf f <> ThisWorkbook.FullName Then count = count + 1: iFile(count) = fNextFor Each m In fs.GetFolder(p).SubFolderszdir mNextEnd Sub