批量处理Excel从格式xls到xlsx

详细介绍

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

```
Unexpected record type (HyperlinkRecord)
```

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

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


所以要使用VBA

做法就是在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执行,静候佳音即可