<

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option

Published on
73,225 Points
63,225 Views
15 Endorsements
Last Modified:
Approved
Community Pick
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option
David Miller (dlmille)

Intro
Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associated with down-converting Excel 2007/2010 .XLSX files to Excel 2003 .XLS files http:/Q_27289693.html, and also converting Excel 2007/2010 .XLSM files to .XLSX files http:/Q_27323432.html.

After constructing the first solution, it only required a couple tweaks to create the second.  I thought it would be a good idea to take my solution and create a more generic utility to help us navigate between different Excel version (2003, 2007, 2010) and enable elimination of macros, as well.

This article focuses on delivering a utility to convert Excel files in a folder from .XLS, .XLSX, or .XLSM to .XLS, .XLSX, or .XLSM with an option to remove macros as part of the process.  While converting to .XLSX automatically removes all macros, it may be the user's desire to sustain the same file extension without macros (e.g., from .XLS to .XLS or .XLSM to .XLSM).

Credits
To eliminate macros programmatically, and to help out with folder browsing API's, I leveraged tips from Erlandsen Data Consulting, http://www.exceltip.com/show_tip/Modules,_Class_Modules_in_VBA/Delete_all_macros_in_a_workbook/document_using_VBA_in_Microsoft_Excel/505.html, and http://www.exceltip.com/st/Select_folder_names_using_VBA_in_Microsoft_Excel/449.html.  I also leveraged a tip from Ken Puls at http://www.excelguru.ca/node/30 to check for file existance.

How to Use
Just download the attached file, select the Search for file type, and Convert to file type, select from the 3 options:  Remove Macros, Silent mode (auto overwrite), and Silent mode (auto delete), then hit the Loop and Convert command button to initiate the process.
Main InterfaceYou will then be prompted to select a folder.  The application searches that folder for the Search file type.  If you have not selected any of the Silent Mode options, you'll be prompted with the number of files found, and to proceed/cancel.  If the Convert to file exists already, you would be prompted to overwrite that file/cancel, and whether you'd like to delete the old file after conversion.  If you run in silent mode, the application can assume you want to delete old files after conversion, and to overwrite existing files during conversion.
Select FolderWhile this app has seen some testing, its always best to make a backup of your folders and test the app out for yourself.  Especially on mass conversions, until you're satisfied everything's working the way you think it should!  There are some "wrinkles" in the conversion process - recall converting Excel 2007 or 2010 files to Excel 2003 files is a down-conversion, hence some of the features, formatting, and capability could be lost in that conversion.  Having a backup, or not choosing the automatic delete feature on the old files during conversion may be a wise choice, which allows you to examine your converted files ahead of a manual delete process.
 Verbose Prompting (not silent mode)Verbose Prompting (not silent mode)At the end of the process, you will be advised the # of successful conversions.    
Success
Here's the primary code:
 
Option Explicit
Sub loopConvert()
Dim fPath As String
Dim fName As String, fSaveAsFilePath As String, fOriginalFilePath As String
Dim wBook As Workbook, fFilesToProcess() As String
Dim numconverted As Long, cntToConvert As Long, i As Long
Dim killOnSave As Boolean, xMsg As Long, overWrite As Boolean, pOverWrite As Boolean
Dim silentMode As Boolean
Dim removeMacros As Boolean
Dim fromFormat As String, toformat As String
Dim saveFormat As Long
Dim wkb As Workbook, wks As Worksheet

    Set wkb = ThisWorkbook
    Set wks = wkb.Sheets("Control Panel")
    
    removeMacros = IIf(wks.CheckBoxes("Check Box 1").Value = 1, True, False)
    silentMode = IIf(wks.CheckBoxes("Check Box 2").Value = 1, True, False)
    killOnSave = IIf(wks.CheckBoxes("Check Box 3").Value = 1, True, False)
    
    fromFormat = wkb.Names("fromFormat").RefersToRange
    toformat = wkb.Names("toFormat").RefersToRange
    saveFormat = IIf(toformat = ".XLS", xlExcel8, IIf(toformat = ".XLSX", xlOpenXMLWorkbook, xlOpenXMLWorkbookMacroEnabled))

    Application.DisplayAlerts = False 'no user prompting, taking all defaults
    Application.ScreenUpdating = False
    
    fPath = GetFolderName("Select Folder for " & fromFormat & " to " & toformat & " conversion")
    
    If fPath = "" Then
        MsgBox "You didn't select a folder", vbCritical, "Aborting!"
        Exit Sub
    Else
        fName = Dir(fPath & "\*" & fromFormat)
        If fName = "" Then
            MsgBox "There aren't any " & fromFormat & " files in the " & fPath & " directory", vbCritical, "Aborting"
            Exit Sub
        Else 'get a file count of files to be processed, then process them in the next step

            Do
                If UCase(Right(fName, Len(fromFormat))) = UCase(fromFormat) Then 'to differentiate between dir *.xls and inadvertently get *.xls???
                    ReDim Preserve fFilesToProcess(cntToConvert) As String
                    fFilesToProcess(cntToConvert) = fName
                    cntToConvert = cntToConvert + 1
                End If
                
                fName = Dir
                
            Loop Until fName = ""
            
            If cntToConvert = 0 Then 'we were looking for .XLS and there was only .XLS??? or nothing, then abort
                MsgBox "There aren't any " & fromFormat & " files in the " & fPath & " directory", vbCritical, "Aborting"
                Exit Sub
            End If
            
            If Not silentMode Then
                xMsg = MsgBox("There are " & cntToConvert & " " & fromFormat & " files to convert to " & toformat & ".  Do you want to delete the " & fromFormat & " files as they are processed?", vbYesNoCancel, "Select an Option")
                killOnSave = False 'already false, but just a reminder this is in here!
                
                If xMsg = vbYes Then
                    killOnSave = True
                ElseIf xMsg = vbCancel Then
                    GoTo processComplete
                End If
            Else
                pOverWrite = True
            End If
            
            Application.EnableEvents = False 'turn off events so macros don't fire on excel file opens
            
            For i = 0 To cntToConvert - 1 'process each file for conversion, displaying status as progress...
            
                Application.StatusBar = "Processing: " & i + 1 & " of " & cntToConvert & " file: " & fName
                
                fName = fFilesToProcess(i)
                'open and convert file
                On Error GoTo errHandler
                fOriginalFilePath = fPath & "\" & fName
                
                'you could also check to see if the save as file already exists, before you open convert and save on top!
                overWrite = False
                fSaveAsFilePath = fPath & "\" & Mid(fName, 1, Len(fName) - Len(fromFormat)) & toformat
                If Not pOverWrite Then
                    If FileFolderExists(fSaveAsFilePath) Then
                        xMsg = MsgBox("File: " & fSaveAsFilePath & " already exists, overwrite?", vbYesNoCancel, "Hit Yes to Overwrite, No to Skip, Cancel to quit")
                        If xMsg = vbYes Then
                            overWrite = True
                        ElseIf xMsg = vbCancel Then
                            GoTo processComplete
                        End If
                    Else
                        overWrite = True
                    End If
                Else
                    overWrite = pOverWrite
                End If
                If overWrite Then
                    Set wBook = Application.Workbooks.Open(fOriginalFilePath)
                    
                    If removeMacros And (toformat = ".XLS" Or toformat = ".XLSM") And (fromFormat <> ".XLSX") Then
                    'use Remove Macro Helper
                        Call RemoveAllMacros(wBook)
                    End If
                    
                    wBook.SaveAs Filename:=fSaveAsFilePath, FileFormat:=saveFormat
                    wBook.Close savechanges:=False
                    numconverted = numconverted + 1
                    
                    'optionally, you can delete the file you converted from
                    If killOnSave And fromFormat <> toformat Then
                        Kill fOriginalFilePath
                    End If
                End If
                
            Next i
        End If
    End If
    
processComplete:
    On Error GoTo 0
    MsgBox "Completed " & numconverted & " " & fromFormat & " to " & toformat & " conversions", vbOKOnly
    Application.EnableEvents = True 'uncomment if doing other conversions where macros are involved in source workbooks
    Application.StatusBar = False
    Application.DisplayAlerts = True
    Application.ScreenUpdating = False
    Exit Sub
    
errHandler:
    Application.StatusBar = False
    MsgBox "For some reason, could not open/save the file: " & fPath & "\" & fName, vbCritical, "Aborting!"
    Resume processComplete
    
End Sub

Open in new window


You may note that the primary command handling the conversion is the statement:

saveFormat = IIf(toformat = ".XLS", xlExcel8, IIf(toformat = ".XLSX", xlOpenXMLWorkbook, xlOpenXMLWorkbookMacroEnabled))

To enhance this further to your needs, it should be easy enough to modify this to add additional conversion types (e.g., Excel 97/97, 2000, CSV, etc.), whilst modifying the drop downs on the Control Panel tab of the workbook.

Attachment:  Attached, please find the loopAndConvert.xlsm workbook that contains the entire codeset.  I hope you enjoyed the article, and can get some time-saving benefit from the utility.

Enjoy!

Dave
loopAndConvert.xlsm
15
Author:dlmille
Enjoy this complimentary article view.

Get unlimited access to our entire library of technical procedures, guides, and tutorials written by certified industry professionals.

Get 7 days free
Click here to view the full article

Using this article for work? Experts Exchange can benefit your whole team.

Learn More
COLLABORATE WITH CERTIFIED PROFESSIONALS
Experts Exchange is a tech solutions provider where users receive personalized tech help from vetted certified professionals. These industry professionals also write and publish relevant articles on our site.
Ask questions about what you read
If you have a question about something within an article, you can receive help directly from the article author. Experts Exchange article authors are available to answer questions and further the discussion.
Learn from the best.