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
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).
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With