automate xlsx to xls conversion

Loop through the files, opening and saving in xlExcel8 format?
 
  ActiveWorkbook.SaveAs Filename:="C:\Book1.xls", _
        FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False

Can you explain how to apply this in the context of the previous question.  I have about 500 Spreadsheets that i need to convert to .xls.  They have different names.  How i loop through without to type name each time.  I was hoping there is a for the macro to grab the spreadsheet, open it and save it the existing name.  Please help

I started out a bit ago with a simple solution and kept coming up with conditions and some error checking, as I think this is a handy tool to have.  So, here goes:

My first pass just prompts for a directory, then searches for .XLSX files and converts them KILLING (erasing) the old .XLSX files (you can comment the kill command out, make backups before use!)

Sub loopAndConvertFirstPass()
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

    Application.DisplayAlerts = False 'no user prompting, taking all defaults
    
    fPath = GetFolderName("Select Folder for XLSX to XLS conversion")
    
    If fPath = "" Then
        MsgBox "You didn't select a folder", vbCritical, "Aborting!"
        Exit Sub
    Else
        fName = Dir(fPath & "\*.xlsx")
        If fName = "" Then
            MsgBox "There aren't any .XLSX files in the " & fPath & " directory", vbCritical, "Aborting"
            Exit Sub
        Else

            'application.EnableEvents = False 'turn off events so macros don't fire on excel file opens (not a big issue for .xlsx as macro's don't exist
            
            Do
            
                '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!

                fSaveAsFilePath = fPath & "\" & Mid(fName, 1, Len(fName) - 5) & ".XLS"
                Set wBook = Application.Workbooks.Open(fOriginalFilePath)

                wBook.SaveAs Filename:=fSaveAsFilePath, FileFormat:=xlExcel8
                wBook.Close savechanges:=False
                numconverted = numconverted + 1
                
                'optionally, you can delete the file you converted from
                   
                Kill fOriginalFilePath


                fName = Dir
                
            Loop Until fName = ""
        End If
    End If
    
processComplete:
    On Error GoTo 0
    MsgBox "Completed " & numconverted & " .XLSX to .XLS conversions", vbOKOnly
    'application.enableevents = true 'uncomment if doing other conversions where macros are involved in source workbooks
    Application.DisplayAlerts = True
    Exit Sub
    
errHandler:
    MsgBox "For some reason, could not open/save the file: " & fPath & "\" & fName, vbCritical, "Aborting!"
    Resume processComplete
    
End Sub

Select allOpen in new window

Then, I had a bit of fun...
this app prompts you for a directory, searches that directory for .XLSX files and then tells you how many are going to be processed, with a chance to cancel.  Also, you are prompted if you want to delete the "old" .XLSX files as you are processing (backups please), and finally, you are prompted if a file already exists/would be overwritten.  At the beginning you can advise whether you want any prompts or not.  I tested it out with the combinations and seems to work well.

Here's the code:  

Sub loopAndConvert()
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


    xMsg = MsgBox("Do you want prompting?" & Chr(10) & "Silent Mode:  Overwrite existing files, and Deletes when processing complete", vbYesNo, "Hit NO for Silent mode")
    silentMode = False
    If xMsg = vbNo Then
        silentMode = True
    End If
    
    Application.DisplayAlerts = False 'no user prompting, taking all defaults
    
    fPath = GetFolderName("Select Folder for XLSX to XLS conversion")
    
    If fPath = "" Then
        MsgBox "You didn't select a folder", vbCritical, "Aborting!"
        Exit Sub
    Else
        fName = Dir(fPath & "\*.xlsx")
        If fName = "" Then
            MsgBox "There aren't any .XLSX files in the " & fPath & " directory", vbCritical, "Aborting"
            Exit Sub
        Else

            Do
                ReDim Preserve fFilesToProcess(cntToConvert) As String
                fFilesToProcess(cntToConvert) = fName
                cntToConvert = cntToConvert + 1
                
                fName = Dir
                
            Loop Until fName = ""
            
            If Not silentMode Then
                xMsg = MsgBox("There are " & cntToConvert & " .XLSX files to convert to .XLS.  Do you want to delete the .XLSX 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
                killOnSave = True
                pOverWrite = True
            End If
            
            'application.EnableEvents = False 'turn off events so macros don't fire on excel file opens (not a big issue for .xlsx as macro's don't exist
            
            For i = 0 To cntToConvert - 1
            
                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) - 5) & ".XLS"
                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
                    End If
                Else
                    overWrite = pOverWrite
                End If
                If overWrite Then
                    Set wBook = Application.Workbooks.Open(fOriginalFilePath)

                    wBook.SaveAs Filename:=fSaveAsFilePath, FileFormat:=xlExcel8
                    wBook.Close savechanges:=False
                    numconverted = numconverted + 1
                    
                    'optionally, you can delete the file you converted from
                    If killOnSave Then
                        Kill fOriginalFilePath
                    End If
                End If
                
            Next i
        End If
    End If
    
processComplete:
    On Error GoTo 0
    MsgBox "Completed " & numconverted & " .XLSX to .XLS conversions", vbOKOnly
    'application.enableevents = true 'uncomment if doing other conversions where macros are involved in source workbooks
    Application.DisplayAlerts = True
    Exit Sub
    
errHandler:
    MsgBox "For some reason, could not open/save the file: " & fPath & "\" & fName, vbCritical, "Aborting!"
    Resume processComplete
    
End Sub

Select allOpen in new window

Both versions are in the attached.  The button runs the more sophisticated version.

Enjoy!

Dave
loopAndConvert-r1.xlsm

It's your lucky day, dlmille has one he prepared earlier...

Hi Dave,

Thanks for you response.   When i apply the code. i got point where it tells me how many files will be converted, but then i got 0 file converted.  I am attaching the message here.  Can you please tell me what i am doing wrong?


xslx-to-xls-conversion.docx

What options did you select when you were prompted?

Hi Dave,

It worked like a charm.  I initially selected yes on "Do you want prompting".  Now I select "No" and it worked.
Thank you very much for sharing this magnificient code.  I actually have a code that created the multiple spreadsheet.  I wonder if there is a way to force the excel 2007 to create  .xls

Thanks Dave.  I will forever be greatful

Well, if you select "Yes", you want prompting, make sure you don't hit "Cancel" (unless you want to abort) on the subsequent prompts.  Remember, the way it is set up is that "No" on prompting (Silent mode), it will DELETE the .XLSX files after conversion.

>>I actually have a code that created the multiple spreadsheet.  I wonder if there is a way to force the excel 2007 to create  .xls
 
I'm not sure what you mean by this?  Please elaborate.

Dave

Do you mean:

To change default file save options in Office Excel 2007

1.Double-click Microsoft Office Excel 2007, double-click Excel Options, and click Save.
2.In the right pane, right-click Save Excel files as, and select Properties.
3.In Save files in this format, select Enabled.
4.In the drop-down box, select a default file save format.
5.Click Apply to save the settings.

Hi Dave,

I created the multiple spreadsheet that i now need to convert into .xls using a macro in excel 2007.  I am wondering if there is a way to force excel 2007 to create a .xls instead of of .xlsx.  The option suggested by c1nmo did not work from my earlier try...

>>multiple spreadsheet
do you mean several spreadsheets?  several files?

Are they .XLSX?

Does not the macro provided work for this?

I'm a bit confused, please elaborate.  If the data is not sensitive (or if you can obfuscate), please post.

Dave

I found one minor bug in the solution...

Line 73 should read:


  Else
     overwrite = true
  End If

here's the revised file

Dave

loopAndConvert-r2.xlsm

@fb - Not sure I answered your question - >>I am wondering if there is a way to force excel 2007 to create a .xls instead of of .xlsx.  

See article here: http:/A_8269.html, which built upon this solution.  It will allow you to do this.  Vote Yes if helpful :)

Dave

Thank you very much dave!