Solved

How can I control instances of Excel from Access vba?

Posted on 2008-10-12
17
569 Views
Last Modified: 2013-11-29
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.

0
Comment
Question by:tonewell
  • 8
  • 8
17 Comments
 
LVL 18

Expert Comment

by:jmoss111
Comment Utility
You can set .visible = False to turn off Ecel displaying. I have a module thats checks if  Excel is open and then prompts the user to close before continuing but I'm not near my computer at the moment. If nobody else posts a solution I'll post when I have the code at hand.
0
 

Author Comment

by:tonewell
Comment Utility
Yes, that is one answer - but I was hoping for a method that does not interfere with the user's work.  If no better solution is suggested I would be grateful for your code.
0
 
LVL 23

Expert Comment

by:irudyk
Comment Utility
Try doing something like the following in your code
Sub CreateReport() 'this would be the name of your existing subroutine
 

Dim xl As Object

Set xl = CreateObject("Excel.Application") 'creates a new instance of Excel
 

'use the xl reference to control the report creation in the new instance of Excel

'for example, add a workbook and put 1 cell A1

'in your case put the code that creates your report here but use the xl object reference to refer to the correct instance of Excel

xl.WorkBooks.Add

xl.ActiveSheet.Cells(1, 1) = 1
 

'at the end of the report's creation, show the new isntance of Excel

'so that the user can view the end result

xl.Visible = True
 

End Sub

Open in new window

0
 

Author Comment

by:tonewell
Comment Utility
irudyk, you seem to be thinking that I am trying to create a report in Excel.  In fact I am trying to create it in Access.  I am wanting to create another instance of Excel only to "take" the flicker generated by the process of creating the Access report.

Still, looking at your code I wondered if my problem could be solved by opening a new instance of Excel just prior to creating the report; maybe Access would then automatically direct the flicker there.  So I tried this, but it did not work.  (For your information, I tried the following code:
    Dim xl As Object
    Set xl = CreateObject("Excel.Application") 'creates a new instance of Excel
    xl.Visible = False
    xl.ScreenUpdating = False
    xl.Interactive = False
    xl.DisplayAlerts = False
    xl.WorkBooks.Add
    xl.ActiveSheet.Cells(1, 1) = 1
0
 
LVL 23

Expert Comment

by:irudyk
Comment Utility
Okay, so you are creating an Access report that have subreports which use OLE links to graphs or tables in Excel workbooks.  Well, somewhere in your Access code you must be launching Excel to do whatever it is that you need to do so that the results get brought back into the Access report.  Is that correct?  If so, using
    Dim xl As Object
    Set xl = CreateObject("Excel.Application") 'creates a new instance of Excel
would create a new Excel object that you could control within your code.  It's visibility would automatically be set to False and as such how an invisible application would still result in a flickering effect is not clear to me.  Also, you posting some kind of sample code/file would be very helpful in attempting to investigate the issue further.

0
 

Author Comment

by:tonewell
Comment Utility
irudyk, the problems here are: (1) the "flicker" (that occurs when controls for subreports with OLE links are added to my report) seems to be beyond direct programmatic control - it seems to be an inadvertent side-effect of the "CreateReportControl" method.  (2) While I can create another instance of Excel in the manner you describe, I do not know how to tell Access to use this instance while running the CreateReportControl method.  In another post I found reference to "instance handles" (eg, in chapter 11 of "Access Cookbook"), and I have had a brief look at this chapter, but it does not seem to be quite what I want.

I have attached a code snippet that contains "CreateReportControl".  Note that at this point in the code there are (currently) no open instances of Excel.  (In the original statement of my problem I mentioned that the application "processes various databases and Excel workbooks"; this is achieved by invoking macros in these databases and workbooks.)
        If docts!ReportOrientation = "Landscape" Then

            Set subrpt = CreateReportControl(rpt.Name, acSubform, acDetail, , , 1417, lngSubrptPosition, 14170, 100)

        Else

            If booFirstSubreport = False Then Set pgbreak = CreateReportControl(rpt.Name, acPageBreak, acDetail, , , 0, lngSubrptPosition)

            Set subrpt = CreateReportControl(rpt.Name, acSubform, acDetail, , , 1417, lngSubrptPosition, 9072, 100)

        End If

Open in new window

0
 
LVL 23

Expert Comment

by:irudyk
Comment Utility
Hmm, okay, well have you, in the applicable locations in your Access VBA code, turned the screen updating off and then back on in the Access application?
    'at the start of the report's creation
    Application.Echo False   'turn screen updating off

    'at the end on the report's creation
    Application.Echo True     'turn screen updating on
0
 

Author Comment

by:tonewell
Comment Utility
Thanks for your new suggestion.  I've just tried it: it does not work either.  It turns off Access' screen display, but makes no diference to Excel flicker.
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 23

Expert Comment

by:irudyk
Comment Utility
Okay, well without some more detailed code (e.g. the entire sub routine code you are running), I can't think of anything more to suggest to you.  From my end, it is practically impossible for me to try and replicate the issue you are having.
0
 

Author Comment

by:tonewell
Comment Utility
Thanks.  My code is complex, would take far too long to explain, and anyway knowing how it works probably would not help in solving this specific problem.  I have decided to simply advise users that they will not be able to use Excel while the flicker-inducing process runs.  I will leave this question open for awhile in the hope that another solution is forthcoming...
0
 
LVL 23

Expert Comment

by:irudyk
Comment Utility
Okay, well the few lines of code you provided show how you are creating the control it does not indicate how and when Excel starts coming into play (i.e. when Excel gets opened by your process). Maybe you could at least post that part of the code?
0
 

Author Comment

by:tonewell
Comment Utility
Well, I think that I have already done that.  My database contains predefined subreports containing OLE links to Excel graphs and tables.  When, using the "CreateReportControl" method, I place controls for these subreports on my report, Access seems to need to open and close the linked-to workbooks (to check that they exist? or that they are valid?) as part of the process of creating the control.  If an independently-opened instance of Excel exists at the start of this process, it will flicker as each workbook is opened and closed.  Brief glimpses of the contents of these workbooks can be seen on screen as this happens.  If no instance of Excel is open then this process runs without showing anything on screen.

Apart from this, the code does not do anything to start Excel.  (Some other parts of my code run macros in Excel workbooks, but this part of the code is not always required to run; the problem with flicker occurs regardless.)
0
 

Author Comment

by:tonewell
Comment Utility
A further thought: as I mentioned above, if I have two instances of Excel open, only one gets the "flicker".  I suspect that the instance that gets the flicker is the first one that Access finds.  What I am wondering is, if I open another instance of Excel, is there some way of making this the "first" instance, so that it takes the flicker, instead of the instance the user opened?
0
 
LVL 23

Expert Comment

by:irudyk
Comment Utility
As a test if you create a new report with all/some of the subreport preset within it and then run the report, do you get Excel opening up/the flicker effect?
If not expand this test so that instead of using the CreateReportControl method, you insert all of the applicable subreport into the main report and then set each subreport's visible property to True or False depending on whether they are applicable or not (i.e. so where the CreateReportControl method codes are used, alter them to set the subreport's visibility property).
0
 
LVL 23

Accepted Solution

by:
irudyk earned 500 total points
Comment Utility
Okay, scrap that last comment.  I found that if you first open the Excel file that will be placed into a subreport control in a 2nd instance of Excel, then the flickering does not happen in any pre-exisitng instance of Excel that the user might already have open.
To accomplish this, try the following (I found that this worked for me):
At the beginning of the routine that builds the report, create a new instance of Excel
   Dim ObjExcel As Object
    Set ObjExcel = CreateObject("Excel.Application")

As you loop through each subreport control to be created, prior to using the CreateReportControl method, open the Excel file that the subreport is using as it's source - e.g.
    ObjExcel.Workbooks.Open "C:\ExcelFileName.xls"
Next use your existing CreateReportControl code.
Once the control is created close the Excel file that was opened using
   ObjExcel.ActiveWorkbook.Close 2    'close without saving changes
After you have finished adding all of the subreport controls, quit the Excel application object
   ObjExcel.Quit
    Set ObjExcel = Nothing

 
0
 
LVL 23

Expert Comment

by:irudyk
Comment Utility
Just wondering if you had any luck with the above posted suggestion?
0
 

Author Closing Comment

by:tonewell
Comment Utility
Sorry, I should have got back to you.  Your last suggestion was excellent, and it looks like it would solve the problem that I originally posed.  You also asked another very good question "As a test if you create a new report with all/some of the subreport preset within it and then run the report, do you get Excel opening up/the flicker effect?"  I have found that the answer to this question is yes.  Unfortunately I needed to get rid of "flicker" not only during the report design phase, but also when the report is run.  So I have not actually put your solution into effect, and have decided to simply ask the user to avoid using Excel during the part of the process causing flicker.  But you undoubtedly deserve the points.  Thanks very much
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now