How can I batch convert 97-2003 .xls files to 2007 .xlsx files

[Guest]

I have a lot to do, so it would be nice to batch them up!

 

[Bernie Deitrick]

Dave,

You could run a macro, below. Change the path to where you have the files stored. With Excel 2007,
you have a few options - I'm showing how to save files that may have macros or other code...

HTH,
Bernie Deitrick
MS Excel MVP

Sub TrandformAllXLSFilesToXLSM()

Dim myPath As String

myPath = "C:\Excel\"
WorkFile = Dir(myPath & "*.xls")

Do While WorkFile <> ""
If Right(WorkFile, 4) <> "xlsm" Then
Workbooks.Open FileName:=myPath & WorkFile
ActiveWorkbook.SaveAs FileName:= _
myPath & WorkFile & "m", FileFormat:= _
xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
ActiveWorkbook.Close
End If
WorkFile = Dir()
Loop
End Sub

 

[Jim Rech]

I would just add that if any of those workbooks had macros the files have to
get the XLSM extension or Excel will refuse to run them.

--
Jim
| Dave,
|
| You could run a macro, below. Change the path to where you have the files
stored. With Excel 2007,
| you have a few options - I'm showing how to save files that may have
macros or other code...
|
| HTH,
| Bernie Deitrick
| MS Excel MVP
|
| Sub TrandformAllXLSFilesToXLSM()
|
| Dim myPath As String
|
| myPath = "C:\Excel\"
| WorkFile = Dir(myPath & "*.xls")
|
| Do While WorkFile <> ""
| If Right(WorkFile, 4) <> "xlsm" Then
| Workbooks.Open FileName:=myPath & WorkFile
| ActiveWorkbook.SaveAs FileName:= _
| myPath & WorkFile & "m", FileFormat:= _
| xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
| ActiveWorkbook.Close
| End If
| WorkFile = Dir()
| Loop
| End Sub
|
| | >I have a lot to do, so it would be nice to batch them up!
|
|

 

[Guest]

You could run a macro, below.

Thanks Bernie! In my naivety I had supposed that MS would have supplied a
way to do this (other than one at a time), but had forgotten to tell anyone
about it.

 

[Matt Campbell]

Thanks, Bernie. Would like to point you et al. to this:

http://www.rondebruin.nl/saveas.htm

It mentions the importance of specifying the sub-format with Excel 2007
since it has about 10 million to choose from just to make life interesting.

By the way, does anyone know if there is any kind of share/freeware utility
that will do these conversions faster than some VBA-powered macro? The
reason we want to do it here at our office is because the 2007 formatted docs
are so much smaller than the earlier versions and we are running low on file
server space.