Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting LOTS of xls to xlsm
I have lots and lots of Excel 2003 workbooks with macros - almost anything I
do anymore has some kind of macro in it. And there are even more archived files that I have to go back to every now and then. When moving to Excel 2007, do I have to open each workbook individually and save it as an .xlsm file, or is there some easy way to convert them all? -- Bill @ UAMS |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting LOTS of xls to xlsm
Since you know some VBA, I'll tell you how I'd do it. If they are all in the
same folder, you can convert them far faster. I'd have the user enter the folder URL (quick and dirty). I have code that allows the user to select the folder to search on, but it's pretty long. Then I'd use the DIR function to select each Excel workbook one by one in the folder. I'd open each xls file and then saveas .xlsm. You may want to test for code before you save as xlsm. Of course, you'll have to do this in Excel 2007. I'd -- HTH, Barb Reinhardt "BillCPA" wrote: I have lots and lots of Excel 2003 workbooks with macros - almost anything I do anymore has some kind of macro in it. And there are even more archived files that I have to go back to every now and then. When moving to Excel 2007, do I have to open each workbook individually and save it as an .xlsm file, or is there some easy way to convert them all? -- Bill @ UAMS |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting LOTS of xls to xlsm
Here's the code.
I commented out the saveas because I'm not on Office 2007 here yet. Option Explicit Public Sub SaveasXLSM() Dim FileName As String Dim oWB As Workbook Dim Security As MsoAutomationSecurity Const Folder = "C:\Documents and Settings\barbara.reinhardt\Desktop" FileName = Dir(Folder & "\*.xls") Do While FileName < "" Security = Application.AutomationSecurity Application.AutomationSecurity = msoAutomationSecurityForceDisable Set oWB = Workbooks.Open(Folder & "\" & FileName) Application.AutomationSecurity = Security Debug.Print oWB.Name Debug.Print oWB.Path newpath = oWB.Path & "\" & Replace(oWB.Name, ".xls", "xlsm") Debug.Print newpath 'oWB.saveas newpath oWB.Close FileName = Dir Loop End Sub -- HTH, Barb Reinhardt "BillCPA" wrote: I have lots and lots of Excel 2003 workbooks with macros - almost anything I do anymore has some kind of macro in it. And there are even more archived files that I have to go back to every now and then. When moving to Excel 2007, do I have to open each workbook individually and save it as an .xlsm file, or is there some easy way to convert them all? -- Bill @ UAMS |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting LOTS of xls to xlsm
What is the success rate with the Excel 2003 to 2007 macro conversions? Is
there information available somewhere on what bugs/pitfalls the conversion process may encounter? "Barb Reinhardt" wrote: Here's the code. I commented out the saveas because I'm not on Office 2007 here yet. Option Explicit Public Sub SaveasXLSM() Dim FileName As String Dim oWB As Workbook Dim Security As MsoAutomationSecurity Const Folder = "C:\Documents and Settings\barbara.reinhardt\Desktop" FileName = Dir(Folder & "\*.xls") Do While FileName < "" Security = Application.AutomationSecurity Application.AutomationSecurity = msoAutomationSecurityForceDisable Set oWB = Workbooks.Open(Folder & "\" & FileName) Application.AutomationSecurity = Security Debug.Print oWB.Name Debug.Print oWB.Path newpath = oWB.Path & "\" & Replace(oWB.Name, ".xls", "xlsm") Debug.Print newpath 'oWB.saveas newpath oWB.Close FileName = Dir Loop End Sub -- HTH, Barb Reinhardt "BillCPA" wrote: I have lots and lots of Excel 2003 workbooks with macros - almost anything I do anymore has some kind of macro in it. And there are even more archived files that I have to go back to every now and then. When moving to Excel 2007, do I have to open each workbook individually and save it as an .xlsm file, or is there some easy way to convert them all? -- Bill @ UAMS |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
converting an .xlsm file to .xml? | Excel Worksheet Functions | |||
Converting xls to xlsm changes some names | Excel Discussion (Misc queries) | |||
What does the extension '.xlsm' mean | Excel Discussion (Misc queries) | |||
xlsm | Excel Programming | |||
Lots of Frustration - Lots of Arrays, Dynamic Ranges Don't Work, Help With Options | Excel Programming |