Convert from "xls" to "xlsm" format

[K]

Hi all, I am looking for macro which convert all "xls" format files
into "xlsm" format in the specified folder.
Macro should also pop up message boxes on conditions (see below)
1 - If all files in folder already in in "xlsm" formate then macro
should pop up message box saying "No file needed to Convert"
2 - When macro finish converting files from "xls" to "xlsm" format
then message box come up saying " 85 files been converted " (85 is
given as example here that how many files have been converted - macro
should workout how many files has been converted and put number on
message box)
3 - if in folder some files are already in "xlsm" format and some are
in "xls" then after converting "xls" message box come up saying " 3
files not converted & vbnewline & 12 files been converted" (3 and 12
are given as examples which means No. of files - again macro should
workout how many files converted and how many not converted and put
numbers on message box)
Please can any friend help me on this

 

[Bernie Deitrick]

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... it is
left as your task to do the counting and messages...


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

 

[K]

Thanks Bernie , your code works perfect just small question that how
can i kill or delete the old format files because i only want new
formated files in the folder

 

[K]

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... it is
left as your task to do the counting and messages...

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

--
HTH,
Bernie
MS Excel MVP






- Show quoted text -

Thanks, just a question that how can i delete or kill old format
files as i need only new formated files in the folder

 

[Bernie Deitrick]

Add in the Kill after you've opened and saved the file:


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

 

[K]

   Add in the Kill after you've opened and saved the file:

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

--
HTH,
Bernie
MS Excel MVP






- Show quoted text -

thats brilliant thanks lot. just last question sorry to be pain. how
can i convert them back to "xls" . just for knowledge

 

[Bernie Deitrick]

thats brilliant thanks lot. just last question sorry to be pain. how
can i convert them back to "xls" . just for knowledge

You would need to change the name to remove the m from the end, and change

xlOpenXMLWorkbookMacroEnabled

to.... some other value - I don't have the list available, but it should be readily apparent when
you look at the list, for XL97 to 2003 format. Sorry for being so cryptic, but I don't have 2007
readily available.

HTH,
Bernie
MS Excel MVP

 

[Ron de Bruin]

See
http://www.labnol.org/software/bulk-convert-office-documents-in-2007-format/4291/

I test it and it is working Ok