Access 2013 VBA automating Excel losing windows

Question

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...

Is there a reason the code is using early binding, but opening the Workbook as if it was late bound? — Comintern, Jun 05 '15 at 17:10
Does it work properly if you first set `objXLApp` directly and then use its `WorkBooks.Open` method to open the file? — HansUp, Jun 05 '15 at 17:23
If you're using office 2013 are your excel sheets ".xls" or ."xlsx"? — Gene, Jun 05 '15 at 17:33
Is the workbook open when the code is run, or does running the code open the workbook? — Comintern, Jun 05 '15 at 17:49

score 3 · Accepted Answer · answered Jun 05 '15 at 18:19

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.

yes, I avoid them, too, but wanted to minimize the changes. I added a global public sub to wrap the 2 lines of code and changed the other 16 references. The users are familiar with errors if they rerun a report, they have to close one instance before opening another. That could be what you're referring to, but I'll wait for them to ask before making more changes. btw, the object variables are scoped at the global level. — Beth, Jun 05 '15 at 20:33

Access 2013 VBA automating Excel losing windows

1 Answers1