如何自动将Excel xls文件转换为Excel xml格式?

I have about 200 Excel files that are in standard Excel 2003 format.

我有大约200个Excel文件是标准的Excel 2003格式。

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

我需要将它们全部保存为Excel xml——基本上与打开每个文件并选择Save as…然后选择Save as type: XML Spreadsheet。

Would you know any simple way of automating that task?

你知道自动化那个任务的简单方法吗?

6 个解决方案

#1

Here is a routine that will convert all files in a single directory that have a .xls extension.

下面是一个例程，它将在一个具有.xls扩展名的目录中转换所有文件。

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.

它采取一种直截了当的方法。工作簿中的任何VBA代码都被删除，工作簿没有通过.xlsm扩展保存。任何不完整的警告都不会被取消，而是自动接受更改。

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

#2

You could adapt the code I posted here:

你可以修改我在这里发布的代码:

http://www.atalasoft.com/cs/blogs/loufranco/archive/2008/04/01/loading-office-documents-in-net.aspx

It shows how to save as PDF (Word is shown in the blog, but if you download the solution, it has code for Excel and PPT).

它展示了如何保存为PDF (Word在博客中显示，但是如果下载解决方案，它有Excel和PPT的代码)。

You need to find the function for saving as the new format instead of exporting (probably the easiest way is to record a macro of yourself doing it in Excel and then looking at the code).

您需要找到保存为新格式而不是导出的函数(可能最简单的方法是用Excel记录自己的宏，然后查看代码)。

#3

Open them all up, and then press ALT+F11 to get to macro editor and enter something like:

打开它们，然后按ALT+F11进入宏编辑器，输入如下内容:

Sub SaveAllAsXml()
    Dim wbk As Workbook
    For Each wbk In Application.Workbooks
        wbk.SaveAs FileFormat:=XlFileFormat.xlXMLSpreadsheet
    Next
End Sub

And then press F5 to run it. May need some tweaking as I haven't tested it.

然后按F5运行它。可能需要一些调整，因为我还没有测试。

#4

Sounds like a job for my favorite-most-underrated language of all time: VBScript!!

Put this in a text file, and make the extension ".vbs":

这听起来像是我最喜欢、被低估的语言的工作:VBScript!!将它放入一个文本文件中，并使扩展名“.vbs”:

set xlapp = CreateObject("Excel.Application")
set fso = CreateObject("scripting.filesystemobject")
set myfolder = fso.GetFolder("YOURFOLDERPATHHERE")
set myfiles = myfolder.Files
for each f in myfiles
   set mybook = xlapp.Workbooks.Open(f.Path)
   mybook.SaveAs f.Name & ".xml", 47
   mybook.Close
next

I haven't tested this, but it should work

我还没有测试过这个，但是应该可以

#5

The simplest way is to record macro for one file and then manually edit macros to do such actions for files in folder using loop. In macro you can use standart VB functions to get all files in directory and to filter them. You can look http://www.xtremevbtalk.com/archive/index.php/t-247211.html for additional information.

最简单的方法是为一个文件记录宏，然后手工编辑宏，使用循环对文件夹中的文件执行此类操作。在宏中，您可以使用标准VB函数来获取目录中的所有文件并对它们进行过滤。您可以查看http://www.xtremevbtalk.com/archive/index.php/t-247211.html以获得更多信息。

#6

Const xlXLSX = 51

REM 51 = xlOpenXMLWorkbook (without macro's in 2007-2013, xlsx)
REM 52 = xlOpenXMLWorkbookMacroEnabled (with or without macro's in 2007-2013, xlsm)
REM 50 = xlExcel12 (Excel Binary Workbook in 2007-2013 with or without macro's, xlsb)
REM 56 = xlExcel8 (97-2003 format in Excel 2007-2013, xls)

dim args
dim file
dim sFile
set args=wscript.arguments

dim wshell
Set wshell = CreateObject("WScript.Shell")

Set objExcel = CreateObject("Excel.Application")

Set objWorkbook = objExcel.Workbooks.Open( wshell.CurrentDirectory&"\"&args(0))

objExcel.DisplayAlerts = FALSE

objExcel.Visible = FALSE

objWorkbook.SaveAs wshell.CurrentDirectory&"\"&args(1), xlXLSX

objExcel.Quit

Wscript.Quit

#1