Excel Workbook Conversion Macro

[Rudi]

Hi,

I work with a lot of training material, and often need to convert old files into the newer file formats for Office 2007. I created this macro (below) to convert *.xls files into *.xlsm files, and it works great, but I have two questions to ask to improve the macro.

Code: Select all

Sub ConvertFromXL2003()
    Dim myFile As String
    ChDir "D:\Test\EDA"
    myFile = Dir("*.xls")
    
    Application.ScreenUpdating = False
    Do Until myFile = ""
        
        Workbooks.Open myFile
        ActiveWorkbook.SaveAs _
            Filename:="D:\Test\EDA2007\" & myFile & "m", _
            FileFormat:=xlOpenXMLWorkbookMacroEnabled, _
            CreateBackup:=False
        Workbooks(myFile & "m").Close SaveChanges:=False
        myFile = Dir
    Loop
    Application.ScreenUpdating = True
End Sub

1. I would like to save the workbook as an *.xlsx if there is no code in the workbook and as a *.xlsm file if there is code in it.

2. I would like some progress indicator to inform of the progress, but since I cannot determine how many files are in the folder when I use the DIR function, I cannot calculate the duration to display on the status bar. Any ideas for how to show some form of progress as to the time duration the procedure will still take?

TIA for any brilliant ideas or comments.

[Jan Karel Pieterse]

Excel 2007 has a new boolean property for a workbook called HasVBAProject, with obvious results...

If you want to show progress, you could loop through the dir once, pushing each found file into a string array. Then use a second loop against that array (of which you now know the dimensions) and process each file. Easy to show progress now.

[HansV]

I see that Jan Karel has already replied while I was composing my reply. The following code is in accordance with his suggestions:

Code: Select all

Sub ConvertFromXL2003()
  Dim myFile As String
  Dim strOldPath As String
  Dim strNewPath As String
  Dim wbk As Workbook
  Dim i As Long
  Dim n As Long
  strOldPath = "D:\Test\EDA\"
  strNewPath = "D:\Test\EDA2007\"

  ' Loop to get file count
  myFile = Dir(strOldPath & "*.xls")
  Do Until myFile = ""
    n = n + 1
    myFile = Dir
  Loop

  Application.ScreenUpdating = False

  ' Process workbooks
  myFile = Dir(strOldPath & "*.xls")
  Do Until myFile = ""
    i = i + 1
    Application.StatusBar = "Processing workbook " & i & " of " & n
    Set wbk = Workbooks.Open(strOldPath & myFile)
    If wbk.HasVBProject Then
      wbk.SaveAs _
        Filename:=strNewPath & myFile & "m", _
        FileFormat:=xlOpenXMLWorkbookMacroEnabled, _
        CreateBackup:=False
     Else
      wbk.SaveAs _
        Filename:=strNewPath & myFile, _
        FileFormat:=xlOpenXMLWorkbook, _
        CreateBackup:=False
    End If
    wbk.Close SaveChanges:=False
   myFile = Dir
  Loop
  Application.StatusBar = False
  Application.ScreenUpdating = True
End Sub

[Rudi]

Thanks Jan Karel and Hans.

Hans, TX esp. for the code. How you generate this so quickly...I dunno!!!

Just something I've picked up whilst running your code.

The line:
Set wbk = Workbooks.Open(strOldPath & myFile)
If wbk.HasVBAProject Then

debugs, because the workbook that has been opened is a *.xls workbook and does not have that new boolean property. So the code needs to change so that this property applies only after the workbook is converted to 2007. I dunno how this will apply, but maybe it must save first as a *.xlsm, then test the workbook to see if there is code, and then resave as a *.xlsx. Any better ideas?

Ignore above...
Cheers

[Rudi]

Woah....stop the boat!!!

Hans, your code worked awesome...just one correction and one extra change!

Change VBAProject to VBProject
EG:
Set wbk = Workbooks.Open(strOldPath & myFile)
If wbk.HasVBProject Then

ALSO Include :

Application.Statusbar = False
at the end of the macro to reset the statusbar .

TX sooooo much for this awesome code. Its gonna save tons of time!

PS: I will now alter it to reverse the process and save 2007 files to 2003. (Will post the code when I have it working )

[HansV]

Thanks, I'll edit my reply to correct the error and to incorporate your addition.

[Jan Karel Pieterse]

Sorry for giving you guys the wrong name for the property!

[Rudi]

Hi,

Here is the macro to convert Excel 2007 workbooks back into Excel 2003...(Tx to Hans's code!)

Code: Select all

Sub ConvertToXL2003()
  Dim myFile As String
  Dim strOldPath As String
  Dim strNewPath As String
  Dim wbk As Workbook
  Dim i As Long
  Dim n As Long
  strOldPath = "D:\Test\EDA2007\"
  strNewPath = "D:\Test\EDA\"

  ' Loop to get file count
  myFile = Dir(strOldPath & "*.xls*")
  Do Until myFile = ""
    n = n + 1
    myFile = Dir
  Loop

  Application.ScreenUpdating = False

  ' Process workbooks
  myFile = Dir(strOldPath & "*.xls*")
  Do Until myFile = ""
    i = i + 1
    Application.StatusBar = "Processing workbook " & i & " of " & n
    Set wbk = Workbooks.Open(strOldPath & myFile)
      wbk.SaveAs _
        Filename:=strNewPath & myFile, _
        FileFormat:=xlExcel8
    wbk.Close SaveChanges:=False
   myFile = Dir
  Loop
  Application.ScreenUpdating = True
  Application.StatusBar = False
End Sub

One question:
Is there a way to prevent the compatibility checker from popping up during the process? Any ideas of a argument to instruct the macro to continue just saving?

TX

[Rudi]

Sorry for giving you guys the wrong name for the property!

What sorry!!!

You got the ball rolling. We must thank you!!

[HansV]

Is there a way to prevent the compatibility checker from popping up during the process?

You could add a line

Application.DisplayAlerts = False

at the beginning of the macro, and

Application.DisplayAlerts = True

at the end.

[Rudi]

OK...will do.