Converting LOTS of xls to xlsm

[BillCPA]

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?

 

[Barb Reinhardt]

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

 

[Barb Reinhardt]

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

 

[Shelia L.]

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?