Excel 2003 - 2007 File Converter breaks workbook-relative file paths to other workbooks

Question

We have a product based on a set of Excel workbooks which runs on Excel 2003 onwards. Some of the workbooks open other workbooks in the same directory to use as data stores. Recently, in trying to port this to the Mac Excel 2011 platform, we converted the workbooks from .xls to .xlsm format. After a struggle with a log of compatibility issues, we have the product working on Excel 2007 onwards.

However, when we went back to test on Excel 2003 with the Converter module installed, our self-relative workbook links all break. This is because the converter makes a copy of the workbook in the users Temp directory which is not anywhere near the product directory. The user has a choice of where to install the product, so the path to the product directory has always been self-relative, which has worked fine up until now. Oddly enough, once the workbook is open, if you have run the Workbook_Open code, it returns the correct path. Only when the work book is actually opening, do you have the problem. e.g.

Private Sub Workbook_Open()
    Dim appPath As String
    Dim FileName As String
    . . .
    appPath = Me.Path
#If Win32 Or Win64 Then
    FileName = appPath & "\" & "MMDataStore.xlsm"
#Else
    ' MAC support
    FileName = appPath & ":" & "MMDataStore.xlsm"
#End If
    MsgBox FileName
    Application.Workbooks.Open FileName
    MsgBox "Activate"
    Workbooks("MMDataStore.xlsm").Activate
    Me.Activate

...

The first time through, as the workbook opens, the message box indicates the Filename path (appPath) is in the Temp directory (e.g. C:\Users\njohnson\AppData\Local\Temp\MMDataStore.xlsm. If you then open up Microsoft Visual Basic and step through the same workbook open code, it now shows the sheet as being open in the correct directory. Does anyone have any thoughts on how to work around this?

Thanks, Neil

Answer 1

This appears to be a Microsoft bug. Our workaround was to move the code to open the workbook into the function that required it, just as it required it. e.g. When the user wanted to retrieve data from the workbook or store data to the work book, we would check if it was already open, and if not, open it at then. By this time, all of the internal pointers to the workbook seem to have been resolved, and the Workbook open worked correctly. This solution works on Excel 2003 with the converters.