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

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


"K" wrote in message
...
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

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

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


"K" wrote in message
...
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

On Sep 5, 3:22*pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
* *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

"K" wrote in message

...



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- Hide quoted text -

- 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

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

On Sep 5, 3:03*pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
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

"K" wrote in message

...



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- Hide quoted text -

- 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

See
http://www.labnol.org/software/bulk-...7-format/4291/

I test it and it is working Ok

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"K" wrote in message ...
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