问题描述
我有大约200个Excel 2003格式的Excel文件。我需要将它们全部保存为Excel xml - 与打开每个文件基本相同,并选择另存为... ,然后选择另存为:
你会知道任何简单的自动执行任务的方法吗?
这是一个例程,将转换具有.xls扩展名的单个目录中的所有文件。
需要一个直接的方法。工作簿中的任何VBA代码被删除,工作簿不会以.xlsm扩展名保存。任何不兼容性警告都不会被忽略,而是自动接受更改。
Sub Convert_xls_Files()
Dim strFile As String
Dim strPath As String
应用程序
.EnableEvents = False
.DisplayAlerts = False
.ScreenUpdating = False
结束
'关闭事件,警报和屏幕更新
strPath =C:\temp\excel\
strFile = Dir(strPath&* .xls)
'将路径更改为必需
尽管strFile<>
Workbooks.Open(strPath& strFile)
strFile = Mid(strFile,1,Len(strFile) - 4)& .xlsx
ActiveWorkbook.SaveAs文件名:= strPath& strFile,FileFormat:= xlOpenXMLWorkbook
ActiveWorkbook.Close True
strFile = Dir
循环
'打开工作簿,设置文件名,保存新格式并关闭工作簿
与应用程序
.EnableEvents = True
.DisplayAlerts = True
.ScreenUpdating = True
结束
'打开事件,警报和屏幕更新
End Sub
I have about 200 Excel files that are in standard Excel 2003 format.
I need them all to be saved as Excel xml - basically the same as opening each file and choosing Save As... and then choosing Save as type: XML Spreadsheet
Would you know any simple way of automating that task?
Here is a routine that will convert all files in a single directory that have a .xls extension.
It takes a straight forward approach. Any VBA code in a workbook is stripped out, the workbook is not saved with a .xlsm extension. Any incompatability warning are not dislayed, instead the changes are automatically accepted.
Sub Convert_xls_Files()
Dim strFile As String
Dim strPath As String
With Application
.EnableEvents = False
.DisplayAlerts = False
.ScreenUpdating = False
End With
'Turn off events, alerts & screen updating
strPath = "C:\temp\excel\"
strFile = Dir(strPath & "*.xls")
'Change the path as required
Do While strFile <> ""
Workbooks.Open (strPath & strFile)
strFile = Mid(strFile, 1, Len(strFile) - 4) & ".xlsx"
ActiveWorkbook.SaveAs Filename:=strPath & strFile, FileFormat:=xlOpenXMLWorkbook
ActiveWorkbook.Close True
strFile = Dir
Loop
'Opens the Workbook, set the file name, save in new format and close workbook
With Application
.EnableEvents = True
.DisplayAlerts = True
.ScreenUpdating = True
End With
'Turn on events, alerts & screen updating
End Sub
这篇关于如何自动将Excel xls文件转换为Excel xml格式?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!