Access 2013 VBA automates Excel losses
I maintain an Access app that has been in production for years and some of the Excel automation has stopped working with updating and converting Office 2013 from .mdb to .accdb.
Access databases contain a link to the Microsoft Excel 15.0 Object Library.
Excel objects are declared:
Public objXLApp As Excel.Application
Public objXLBook As Excel.Workbook
and install:
Set objXLBook = GetObject(strReportPath & strTitle & ".xls")
DoEvents
Set objXLApp = objXLBook.Parent
and at this moment objXLApp.visible
= false. Also, objXLApp.Windows.Count
= 1, which is correct. If in the nearest window I set objXLApp.visible
= true, then I lose my windows: objXLApp.Windows.Count
= 0 and the links to the expected window return an "Invalid range" error.
Running from a .mdb file produces the same behavior.
Any ideas?
Comintern, again, the code was written years ago by someone else, so if there is a better way to install the book, I'm open to suggestions.
HansUp, I can try your suggestion. Can you post an example? If it works, I will answer correctly.
Gene, Yes, the source files are in .xls format, they have not been updated to .xlsx.
Comintern, the code stops executing on the last line of code, then I use the immediate window to check the values and change the visibility property and check the values again.
HansUp who 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 mark yours as correct.
Now I just need to change it everywhere in the code ...
source to share
My suggestion was to find out if the problem could be fixed by first setting the objXLApp variable directly and then using its method WorkBooks.Open
to open the Excel workbook file:
Set objXLApp = New Excel.Application Set objXLBook = objXLApp.Workbooks.Open(strReportPath & strTitle & ".xls")
In the meantime, you've tested this approach and found that it worked. However, I'm not sure if this is the final solution, because your object variables are limited to the module level ...
Public objXLApp As Excel.Application Public objXLBook As Excel.Workbook
If you call the code again, you can end up changing these object references if you still need them elsewhere. On the other hand, if you never run the code again, I see no reason to care about objXLApp.Windows.Count
.
I just don't know what might happen here; I am avoiding globals.
source to share