<

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

Published on
61,710 Points
51,710 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
Comment
Author:dlmille
7 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
A great simple utility which will save lot of user time. Great work David as usual :)

Sid
0
 
LVL 1

Expert Comment

by:xllvr
Great idea!  Thanks for posting.
0
 

Expert Comment

by:javaprofessional
Thanks for sharing this information. I have also come across this Online Excel Converter
which uses java language to convert your excel file to any other format you want including xls to xlsx or xlsm and vice versa.
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 

Expert Comment

by:suedotson
Worked great! Saved me a lot of work.
0
 
LVL 4

Expert Comment

by:Michael_Leng
thanks for the information and I would appreciate if you can share the technique on how to fix a corrupted Excel which has been save and work in a different version of Microsoft Excel such as 2003, 2007 and 2013.
0
 

Expert Comment

by:T X
Ugh, The code in this project must be updated for use on 64-bit systems.

Could someone be so kind a please review and update the Declare statements and then mark them with the PtrSafe attribute. :)

Thank You!
1
 

Expert Comment

by:jazia katanani
Thank you so much this is such a great tool
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Join & Write a Comment

Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
A query can call a function, and a function can call Excel, even though we are in Access. This is Part 2, and steps you through the VBA that "wraps" Excel functionality so we can use its worksheet functions in Access. The declaration statement de…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month