How can I control instances of Excel from Access vba?
Posted on 2008-10-12
Here is the situation: I have written an Access application for users. The application processes various databases and Excel workbooks, then builds and produces a report. The report consists mostly of subreports. Many of these subreports contain OLE links to graphs or tables in Excel workbooks. Building of the report is controlled by vba code; a query is read to determine what subreports should be included in the report, and then controls are added to the report using the "CreateReportControl" method.
All of this is fine; it works. The problem is that if the user happens to be using Excel on another job during the automated procedure that creates report controls (referencing subreports with OLE links to Excel workbooks), then Excel will flicker, seemingly as each control is created and the referenced workbook is quickly opened and closed. This makes it difficult for the user to continue working with Excel when the automated procedure running in the background.
I have been unable to discover a way of turning off the Excel screen display during control creation. (Does anyone know of a way of doing this?) So at this point I can see two options for solving this problem. (1) I can put a message on the screen at the start of the "build report" procedure, inviting the user to close down any Excel job before continuing (easy to do, but annoying for the user). Or, (2) I can try to control the way that the "build report" procedure utilises different instances of Excel. I have observed that if I have two instances of Excel running, the "flicker" will affect only one of these instances. (If I close the instance of Excel with the flicker, then the second instance acquires the flicker.) My idea is that at the start of the "build report" procedure I should start a second instance of Excel, and then somehow "activate" this instance for Access to use, leaving the first instance (the one that the user is using) unaffected by flicker. The problem is that I have no idea how to direct Access to use a particular instance of Excel. Can anyone help? Is it even possible?
I will be most grateful for a solution to this problem.