Solved

automate xlsx to xls conversion

Posted on 2011-09-02
16
2,037 Views
Last Modified: 2012-05-12
Is there a way to reverse this process and convert all files with .xlsx to xls and uncheck the compatibility check box.  I have a bunch of .xlsx file that i need to make available to 97-2003 users and making sure the warning message about compatibilty is not displayed
0
Comment
Question by:fb1990
  • 7
  • 6
  • 3
16 Comments
 
LVL 6

Expert Comment

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

Author Comment

by:fb1990
ID: 36476682
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
0
 
LVL 41

Expert Comment

by:dlmille
ID: 36477001
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

Open 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

Open in new window


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

Enjoy!

Dave
loopAndConvert-r1.xlsm
0
 
LVL 6

Expert Comment

by:c1nmo
ID: 36477178
It's your lucky day, dlmille has one he prepared earlier...
0
 
LVL 1

Author Comment

by:fb1990
ID: 36477978
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
0
 
LVL 41

Expert Comment

by:dlmille
ID: 36478280
What options did you select when you were prompted?
0
 
LVL 41

Accepted Solution

by:
dlmille earned 250 total points
ID: 36478289
Also, what did your status bar say, when you got the last messagebox?

Try it again - what options are you selecting, and if you get the last messagebox with 0 files converted, what does it say in the statusbar?

Dave
0
 
LVL 1

Author Comment

by:fb1990
ID: 36478454
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
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 1

Author Closing Comment

by:fb1990
ID: 36478456
Thanks Dave.  I will forever be greatful
0
 
LVL 41

Expert Comment

by:dlmille
ID: 36478464
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
0
 
LVL 6

Expert Comment

by:c1nmo
ID: 36478571
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.
0
 
LVL 1

Author Comment

by:fb1990
ID: 36479392
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...
0
 
LVL 41

Expert Comment

by:dlmille
ID: 36479401
>>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
0
 
LVL 41

Expert Comment

by:dlmille
ID: 36585637
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
0
 
LVL 41

Expert Comment

by:dlmille
ID: 36975639
@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
0
 
LVL 1

Author Comment

by:fb1990
ID: 36975741
Thank you very much dave!
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now