Search code examples
excelms-accessvbaoffice-automation

Access 2013 VBA automating Excel losing windows

I'm supporting an Access application that's been in production for years and the Excel automation part of it stopped working with our Office 2013 upgrade and conversion from .mdb to .accdb.

The Access databases contain a reference to Microsoft Excel 15.0 Object Library.

The Excel objects are declared:

Public objXLApp As Excel.Application       
Public objXLBook As Excel.Workbook

and set:

Set objXLBook = GetObject(strReportPath & strTitle & ".xls")
DoEvents
Set objXLApp = objXLBook.Parent

and at this point, objXLApp.visible = false. Also, objXLApp.Windows.Count = 1, which is correct. If in the immediate window I set objXLApp.visible = true, then I lose my windows: objXLApp.Windows.Count = 0 and references to the expected window return a 'Subscript out of range' error.

Running it from the .mdb file generates the same behavior.

Any ideas?

Comintern, again, the code was written several years ago by someone else, so if there's a better method to set the workbook, I'm open to suggestions.

HansUp, I can try your suggestion. Can you post an example? If it works, I'll mark your answer as correct.

Gene, Yes, the original files are in .xls format, they have not been upgraded to .xlsx.

Comintern, the code stops execution at the last line of code, then I use the immediate window to check values and change the visible property and check the values again.

HansUp, that fixed it. I changed the code to:

Set objXLApp = New Excel.Application
DoEvents
Set objXLBook = objXLApp.Workbooks.Open(strReportPath & strTitle & ".xls")

If you post an answer, I'll mark yours as correct.

Now I just have to change it everywhere else in the code...

Solution

  • My suggestion was to see whether you could eliminate the problem by first setting the objXLApp variable directly, and then using its WorkBooks.Open method to open the Excel workbook file:

    Set objXLApp = New Excel.Application
Set objXLBook = objXLApp.Workbooks.Open(strReportPath & strTitle & ".xls")

    Meanwhile, you tested that approach and found it worked. However, I'm not certain that is the final solution because your object variables are scoped at the module level ...

    Public objXLApp As Excel.Application       
Public objXLBook As Excel.Workbook

    If you will be calling the code repeatedly, you could wind up altering those object references when they might still be needed elsewhere. On the other hand, if you won't ever run the code repeatedly, I don't see a reason to care about objXLApp.Windows.Count.

    I just don't know what might happen here; I avoid global variables.