• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3422
  • Last Modified:

Convert xlsm files to xlsx files in folder

The following code seems to be giving me multiple copies of the xlsx files instead of just one file.

What I need is to convert all the xlsm files in a folder to xlsx, and then delete the xlsm files.

What is happening now is below, then I have another macro to delete the xlsm files.  I'd like to be able to do this with one button.

12_Table One.xlsm
to
12_Table One.xlsx
12_Table One..xlsx

etc.
Private Sub CommandButton10_Click()
'Sub Convert_xls_Files()

Dim strFile As String
Dim strPath As String

    With Application
        .EnableEvents = False
        .DisplayAlerts = False
        .ScreenUpdating = False
    End With
'Turn off events, alerts & screen updating

        strPath = "C:\Users\szyynr\Documents\Constraint Database\"
        strFile = Dir(strPath & "*.xls")
'Change the path as required

    Do While strFile <> ""
        Workbooks.Open (strPath & strFile)
        strFile = Mid(strFile, 1, Len(strFile) - 4) & ".xlsx"
        ActiveWorkbook.SaveAs Filename:=strPath & strFile, FileFormat:=xlOpenXMLWorkbook
        ActiveWorkbook.Close True
        strFile = Dir
    Loop
'Opens the Workbook, set the file name, save in new format and close workbook

    With Application
        .EnableEvents = True
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With
'Turn on events, alerts & screen updating

MsgBox "Step 4 - Converted XLS Files Completed - Go to Step 5"

End Sub

Open in new window

0
mato01
Asked:
mato01
  • 7
  • 6
  • 2
  • +2
1 Solution
 
Getsum_BloodlustCommented:
silly question, can it be done by simply renaming the files?
0
 
Amit KhilnaneyCommented:
renaming each and every file can take bit longer..

open notepad

type following

ren *.xslm *.xslx

Save File -> Save as Type (all files) -> (file name) renameall.bat

Put the bat file in the folder where all those files are i.e. xlsm

Run the file and it wont take more than few seconds...


0
 
Getsum_BloodlustCommented:
^^ That is what i was eluding to Amit..
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Amit KhilnaneyCommented:
ren *.xlsm *.xlsx  (typo)
0
 
dlmilleCommented:
Try this utility - an app I wrote recently for just this type of activity:  

Don't forget to mark Y if you found the article helpful.

http:/A_8269.html

Dave
0
 
dlmilleCommented:
DO NOT rename *.xlsm to *.xlsx.  An Excel file conversion must happen to convert between Excel versions.  You have to do the equivalent of File Save As.

The utility: http:/A_8269.html will facilitate converting between Excel .XLS, .XLSX, and .XLSM files.  Its designed to run on all files in a folder and has options to delete old files on conversion.

Cheers,

Dave
0
 
mato01Author Commented:
The reason I was using the code above is that I needed the process to run in a seamless process.  No user intervention. It seemed to work at first, but now the files are duplicating themselves.  Not sure why.
0
 
ScriptAddictCommented:
I would use a collection instead of a do while loop.  Something like

   For each file in Dir(strPath & "*.xls")
        Workbooks.Open (strPath & file)
        file= Mid(file, 1, Len(file) - 4) & ".xlsx"
        ActiveWorkbook.SaveAs Filename:=strPath & file, FileFormat:=xlOpenXMLWorkbook
        ActiveWorkbook.Close True
        file = Dir
    Next

Open in new window


Alternatively I would change this :

 Do While strFile <> ""
        Workbooks.Open (strPath & strFile)
        strNewFile = Mid(strFile, 1, Len(strFile) - 4) & ".xlsx"
        ActiveWorkbook.SaveAs Filename:=strPath & strNewFile, FileFormat:=xlOpenXMLWorkbook
        ActiveWorkbook.Close True
        strFile = Dir
    Loop

Open in new window

0
 
ScriptAddictCommented:
Hey I just noticed your MID str formula is off.

Your appending .xlsx  = 5 chars.  This means that I think you want:

mid(strFile,1,Len(StrFile)-5) & ".xlsx"
0
 
dlmilleCommented:
@mateo - the utility I posted works without user intervention, if you run in silent mode.  You can copy the utility and post it in your workbook, setting all the defaults and running in silent mode.

Dave
0
 
mato01Author Commented:
I received the following compile error:

For Each may only iterate over a collection object or an array
0
 
ScriptAddictCommented:
Go back to your original code and just change to -5 instead of -4
0
 
ScriptAddictCommented:
 Do While strFile <> ""
        Workbooks.Open (strPath & strFile)
        strFile = Mid(strFile, 1, Len(strFile) - 5) & ".xlsx"
        ActiveWorkbook.SaveAs Filename:=strPath & strFile, FileFormat:=xlOpenXMLWorkbook
        ActiveWorkbook.Close True
        strFile = Dir
    Loop

Open in new window

0
 
ScriptAddictCommented:
Oh I'm sorry I'm just not reading all the code.  Give me a minute and I'll post something useful.
0
 
dlmilleCommented:
@mato01 - I have located your only error.  I had gotten it as well, when I wrote the article, I mentioned.

You are getting repeats because you are doing the command:

Dir *.xls <- this will return files of .xls, .xlsm, .xlsx, or whatever.  As a result, this is what was starting to feed your duplicating process..

you need to screen out anything except what you're looking for when you do the DIR command.

As a result, you're getting the repeat's you mentioned.

Here is your corrected code, which I have tested:

   
Private Sub CommandButton10_Click()
'Sub Convert_xls_Files()

Dim strFile As String, strFileNew As String
Dim strPath As String

    With Application
        .EnableEvents = False
        .DisplayAlerts = False
        .ScreenUpdating = False
    End With
'Turn off events, alerts & screen updating

        strPath = "C:\Users\szyynr\Documents\Constraint Database\"
        'strPath = ActiveWorkbook.Path & "\loopConvertXLSM\"
        strFile = Dir(strPath & "*.xls")
'Change the path as required

    Do While strFile <> ""
        If UCase(Right(strFile, 3)) = "XLS" Then
            Workbooks.Open (strPath & strFile)
            strFileNew = Mid(strFile, 1, Len(strFile) - 4) & ".xlsx"
            ActiveWorkbook.SaveAs Filename:=strPath & strFile, FileFormat:=xlOpenXMLWorkbook
            ActiveWorkbook.Close True
            
            Kill strPath & strFile

        End If
        strFile = Dir
    Loop
'Opens the Workbook, set the file name, save in new format and close workbook

    With Application
        .EnableEvents = True
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With
'Turn on events, alerts & screen updating

MsgBox "Step 4 - Converted XLS Files Completed - Go to Step 5"

End Sub

Open in new window


Note - I also put the KILL command in to delete the files after conversion.

Let me know how this works for you.

See attached workbook.
excelConvert-r1.xlsm
0
 
ScriptAddictCommented:
It looked like this was originally designed to convert *.xls files to 2010 files.  
I've altered the code below to convert *.xlsm files to *.xlsx files.  Sorry about not being very detailed.  But this should do the trick for you.

Private Sub CommandButton10_Click()
'Sub Convert_xlsm_Files()

Dim strFile As String
Dim strPath As String
Dim strNewFile As String

    With Application
        .EnableEvents = False
        .DisplayAlerts = False
        .ScreenUpdating = False
    End With
'Turn off events, alerts & screen updating

        strPath = "C:\Users\szyynr\Documents\Constraint Database\"
        strFile = Dir(strPath & "*.xlsm")
'Change the path as required

    Do While strFile <> ""
        Workbooks.Open (strPath & strFile)
        strFile = Mid(strFile, 1, Len(strFile) - 5) & ".xlsx"
        ActiveWorkbook.SaveAs Filename:=strPath & strFile, FileFormat:=xlOpenXMLWorkbook
        ActiveWorkbook.Close True
        strFile = Dir
    Loop
'Opens the Workbook, set the file name, save in new format and close workbook

    With Application
        .EnableEvents = True
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With
'Turn on events, alerts & screen updating

MsgBox "Step 4 - Converted XLS Files Completed - Go to Step 5"

End Sub

Open in new window


That should get you where you want to go, although the message box I would eliminate by putting a ' in front of it.  Since it will likely require the user intervention your trying to elminate.
0
 
ScriptAddictCommented:
why go to all that trouble to keep it a *.xls in the beginning?  why not base it on *.xlsm?

0
 
dlmilleCommented:
I did spot one error in my submittal.  It was associated with having another variable for the new file name, keeping strFile for the KILL command.  If you're trying to convert from .XLSM (rather than .XLS) I've updated that code as well.

PS - Caution - I've put the KILL command in to delete the old file

Here's the lastest code:
Private Sub CommandButton10_Click()
'Sub Convert_xls_Files()

Dim strFile As String, strFileNew As String
Dim strPath As String

    With Application
        .EnableEvents = False
        .DisplayAlerts = False
        .ScreenUpdating = False
    End With
'Turn off events, alerts & screen updating

        strPath = "C:\Users\szyynr\Documents\Constraint Database\"
        'strPath = ActiveWorkbook.Path & "\loopConvertXLSM\"
        strFile = Dir(strPath & "*.xlsm")
'Change the path as required

    Do While strFile <> ""
        If UCase(Right(strFile, 4)) = "XLSM" Then
            Workbooks.Open (strPath & strFile)
            strFileNew = Mid(strFile, 1, Len(strFile) - 4) & ".xlsx"
            ActiveWorkbook.SaveAs Filename:=strPath & strFileNew, FileFormat:=xlOpenXMLWorkbook
            ActiveWorkbook.Close True
            
            Kill strPath & strFile

        End If
        strFile = Dir
    Loop
'Opens the Workbook, set the file name, save in new format and close workbook

    With Application
        .EnableEvents = True
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With
'Turn on events, alerts & screen updating

MsgBox "Step 4 - Converted XLS Files Completed - Go to Step 5"

End Sub

Open in new window


See attached.

Dave
excelConvert-r1.xlsm
0
 
dlmilleCommented:
One final update: line 22 should be -5 (with the .XLSM versus .XLS) as ScriptAddict pointed out, earlier.

Dave
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 7
  • 6
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now