Macro to convert all my documents from xls to xlsm and saved macro enabled?

[tonywatsonhelp]

Hi Everyone,
I have a folder called "Old Data" that is stored in my documents,
in it I have around 500 excel docs saved as .xls
I'd like a quick way to save them all as .xlsm docs
I also have another set of just around 50 I'd like to become .csv files

can anyone give me a macro that can do this please?

thanks

Tony

 

[par60056]

I had a couple macros that work on all files in a folder. I modified one to do what I think you want.

You need to add a reference to "Microsoft Scripting Runtime" under the tools->references menu.

Sub runConvert()
Dim fs As FileSystemObject
Dim theFolder As Folder
Dim theFile As File
Dim haveTarget As Boolean
Dim targetPath As String


haveTarget = False
Set fs = CreateObject("Scripting.FileSystemObject")
targetPath = ActiveWorkbook.Path & "\"


While Not haveTarget
    targetPath = InputBox("Result Folder", "Target Folder", targetPath)
    If targetPath = "" Then
        haveTarget = True
    Else
        haveTarget = fs.FolderExists(targetPath)
    End If
Wend
If targetPath <> "" Then
    Set theFolder = fs.GetFolder(targetPath)
    For Each theFile In theFolder.Files
        If (Left(theFile.Name, 1) <> "~" And LCase(Right(theFile.Name, 3)) = "xls") Then
            Workbooks.Open theFile.Path
            ActiveWorkbook.SaveAs theFile.Path & "m", xlOpenXMLWorkbookMacroEnabled
            ActiveWorkbook.Close
        End If
    Next
End If


End Sub

I did not actually run it. you should set up a small test folder to make sure it does what you want before unleashing it on the 500+

 

[tonywatsonhelp]

Hi thanks for your help, I'll have a little play about with it now,
would this work converting the XLSM docs to csv as well (if so what bits do I change?)

 

[par60056]

converting xlsm to csv is a little more work but the concept is the same. Once the xlsm document is opened, you would want to cycle through the sheets in the file, build an output string with something like this:

csvFileName = left(thefile.path,len(thefile.path)-4) & activesheet.name & ".csv"

Use that as the file name on the saveas and change the file format to xlCSVWindows

 

[par60056]

oh and you have to change where the IF is looking for the last 3 to be XLS to the last 4 = XLSM

 

[tonywatsonhelp]

ok mate,
that's brilliant,
well I think I've got the first part working so lets see how the next bit goes?
thanks very much for your help, i'll give you a shout if I get stuck


thanks

Tony

 

[tonywatsonhelp]

also if you have a quick solution, the xlsm to csv documents only have one sheet in them?

 

[par60056]

If they only have 1 sheet, then you don't need to loop through the sheets. I would still put the sheetname in the CSV file name. Look at post #4 to see how to change the saveas from XLSM to CSV. It you have a problem post the code and I'll try to change it. I may not check over the weekend. But it only takes a few minutes to update.

 

[tonywatsonhelp]

hi that's fine, I'll test and play about with it over the weekend and post on Monday if I get stuck
again thanks for all your help