Community Pick: Many members of our community have endorsed this article.

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

Published:
Updated:
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
71,631 Views

Comments (7)

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.
Worked great! Saved me a lot of work.
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.
T X

Commented:
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!
Thank you so much this is such a great tool

View More

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.