Excel VBA Compatibility: Converting Excel 2003 Workbooks to Excel 2007 Format Automatically

Excel VBA Compatibility: Converting Excel 2003 Workbooks to Excel 2007 Format

Working with Excel VBA compatibility between versions can be challenging. When you run an Excel 2003 macro in Excel 2007, it operates in Compatibility Mode, preventing access to newer features like the expanded 16,384 columns. This Excel VBA compatibility solution automatically converts your workbooks to the appropriate format based on the Excel version being used.

The key is adding code to the Auto_Open subroutine that detects the Excel version and converts the file format when necessary. This ensures your macros can take full advantage of Excel 2007’s enhanced capabilities.

The Excel VBA Compatibility Solution

Here’s the VBA code that handles the Excel version compatibility conversion automatically:

Public Sub auto_open()
    'add some smarts if opened in Excel 2007 or later
    Dim oldFileName As String
    oldFileName = ThisWorkbook.Name
    Dim tempi As Integer 'used to store position of '.' before file extension in workbook file name
    tempi = InStrRev(oldFileName, ".xls", -1, vbTextCompare)
    
    If Application.Version > 11 And Len(oldFileName) - tempi = 3 Then 'assume running in compatability mode
        Application.DisplayAlerts = False
        Dim newFileName As String
        newFileName = Mid$(oldFileName, 1, tempi) & "xlsm"
        
        If fileExist(newFileName) Then 'if the new workbook version already exists, then open it and close this one
            'open the new workbook by emulating double clicking the file, as this is the only way to run the auto_open
            Shell "Excel """ & ThisWorkbook.Path & "\" & newFileName & """", 3 '3 = vbMaximizedFocus
            'close this Excel application
            Application.Quit
        Else 'if the new workbook version doesn't exist, then save it as new workbook version
            'save as macro enables office 2007 workbook
            ActiveWorkbook.SaveAs Filename:=newFileName, FileFormat:=52, CreateBackup:=False '52 = xlOpenXMLWorkbookMacroEnabled
            'create a timer to call the same new workbook as it will be now opened in non compatibility mode
            Application.OnTime Now + TimeValue("00:00:01"), "auto_open"
            Workbooks(newFileName).Close
        End If
        
        Application.DisplayAlerts = True
    End If
    
    UserForm1.Show
End Sub

How the Excel VBA Compatibility Code Works

This Excel VBA compatibility solution works through the following process:

Detects if the workbook is running in Excel 2007 or later (Version > 11)
Checks if the current file has an .xls extension (Excel 2003 format)
If a converted version (.xlsm) already exists, it opens that file instead
If no converted version exists, it saves the current workbook in Excel 2007 macro-enabled format (.xlsm)
Uses a timer to reopen the newly saved file, ensuring it runs in non-Compatibility Mode

The most challenging aspect was getting the Auto_Open or Workbook_Activate events to fire properly when reopening the file. The solution involves using the Shell command to simulate double-clicking the file, which ensures the Auto_Open event runs correctly.

This approach can be simplified by moving certain portions to the Workbook_Activate subroutine, but I wanted to provide a complete copy-and-paste solution that requires minimal modification.

Excel VBA Compatibility Resources

For more information about Excel VBA compatibility between versions, check out these helpful resources:

The only modification needed for your implementation is changing “UserForm1” to match the name of your actual user form.

Have you encountered other Excel VBA compatibility issues between versions? Let me know in the comments!

Excel VBA Compatibility: Converting Excel 2003 Workbooks to Excel 2007 Format Automatically