如何自动将Excel xls文件转换为Excel xml格式？ [英] How to automate converting Excel xls files to Excel xml format?

问题描述

我有大约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格式？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！