我已经编写了这段代码,用于批量将一堆 .xlsx 工作簿转换为 .xls(我有很多终端用户使用 2003 版本)。虽然它能完成任务,但速度非常慢。我在本地测试了20个工作簿,每个文件大小只有约30 kb,执行时间为9.78秒。通过我的 SharePoint 服务器,这需要262秒,但我认为 SharePoint 的极慢速度是另一个问题。
代码:
Option Explicit
Sub Convert_to972003()
Dim orgwb As Workbook
Dim mypath As String, strfilename As String
Dim nname As String
'--> Error Handling
On Error GoTo WhatHappened
'--> Disable Alerts
With Application
.DisplayAlerts = False
.ScreenUpdating = False
End With
'--> Specify location of workbooks
mypath = "C:\xxx"
strfilename = Dir(mypath & "\*.xlsx", vbNormal)
'--> Check the specified folder contains files
If Len(strfilename) = 0 Then Exit Sub
'--> Start Loop, end when last file reached
Do Until strfilename = ""
'--> Open a workbook
Set orgwb = Application.Workbooks.Open _
(mypath & "\" & strfilename)
'--> Create new Filename, Save in new File Format and Close
nname = Replace(strfilename, ".xlsx", ".xls")
orgwb.SaveAs mypath & "\" & nname, FileFormat:=xlExcel8
orgwb.Close
strfilename = Dir()
Loop
'--> Enable Alerts
With Application
.DisplayAlerts = True
.ScreenUpdating = True
End With
Exit Sub
WhatHappened: MsgBox Err.Description
End Sub
问题
是否有一种比循环遍历文件夹/打开/保存/关闭更快的方法来转换文件格式?