How to automate converting Excel xls files to Excel xml format?

Question

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?

Answer 1

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

Answer 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).

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).