当我们将xlsx版本的工作簿另存为xls版本时,经常会遇到兼容性检查的提示,在VBA中操作时可以使用CheckCompatibility参数来取消兼容性检查,示例如下:
Sub SaveAs2003xls()
'
' Save_2007_WorkSheet_As_97_2003_Workbook
'
Dim GetDesktopPath As Object, DesktopPath As String
Set GetDesktopPath = CreateObject("WScript.Shell")
DesktopPath = GetDesktopPath.SpecialFolders(4) ' 获取桌面路径
Set GetDesktopPath = Nothing
Dim FilePath As String, Filename As String, File As String
FilePath = DesktopPath & "\"
Filename = "Excel 97-2003 WorkBook " & Format(Now, "yyyy-mm-dd hh-mm-ss")
File = FilePath & Filename & ".xls" ' 指定保存的文件名
Dim ShName As String
ShName = "Sheet1" ' 指定需要另存的表名称
' 设置工作簿的默认保存格式,56或xlExcel8为97-2003版本,xlExcel12为2007-2010版本
Application.DefaultSaveFormat = xlExcel8
'ActiveSheet.Copy ' 将当前工作表复制为新工作簿
Worksheets(ShName).Copy ' 将指定的工作表复制为新工作簿
ActiveWorkbook.CheckCompatibility = False ' 取消当前工作簿的兼容性检查
' 工作表另存为新工作簿,指定路径和格式
ActiveWorkbook.SaveAs Filename:=File, _
FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.Save ' 保存新工作簿
ActiveWorkbook.Close ' 关闭新工作簿
End Sub