批量处理Excel从格式xls到xlsx

时间：2024-02-23 08:15:17

我下载了400多个Excel,是2003的xls格式的,使用NPOI读取的时候报错

Unexpected record type (HyperlinkRecord)

没有找到解决此法的办法,发现打开Excel,点击一下保存,然后在使用NPOI读取是没问题的

问题是,400多个Excel,我不可能一个一个的去点击保存,所以想了另外一个办法,就是把这个xls格式的Excel都另存为xlsx格式即可,400多个xls改成xlsx格式的,手动改也是不现实的

所以要使用VBA,我参考的这篇文章,写的很好

使用VBA批量转换Excel格式，由.xls转换成.xlsx

做法就是在xls文件夹内新建一个Excel,开启宏,然后打开这个Excel,按下alt+F11,点击sheet1输入VB代码

Sub xls2xlsx()
Dim FilePath, MyFile, iPath, Name, OutPath As String
iPath = ThisWorkbook.Path
OutPath = Dir(iPath & "\xlsx", vbDirectory)
If OutPath = "" Then
    MkDir (iPath & "\xlsx")
End If
MyFile = Dir(iPath & "\*.xls")

If MyFile <> "" Then
Do
    On Error Resume Next
    If MyFile = ThisWorkbook.Name Then MyFile = Dir
    Workbooks.Open (iPath & "\" & MyFile)
    MyFile = Replace(MyFile, ".xls", ".xlsx")
    Name = "\" & MyFile
    FilePath = iPath & "\xlsx" & Name
    Application.ScreenUpdating = False
    ActiveWorkbook.SaveAs Filename:=FilePath, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    Workbooks(MyFile).Close True
    Application.ScreenUpdating = True
    MyFile = Dir
Loop While MyFile <> ""
End If
End Sub

然后按下F5执行,静候佳音即可

相关文章