本文介绍了如何自动将Excel xls文件转换为Excel xml格式?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

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

08-23 21:29