Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automation Question
I have developed a macro which can be run two ways:
1. The usual way: Open an Excel application, open the workbook (call it myWB) and run the macro (call it myMacro). 2. The automation way: From a second workbook (call it MasterWB), create a new Excel application via automation, open the workbook (myWB) in the new application and run the macro (myMacro). Here's the issue that I'm having - the macro runs faster when run the "usual way" than the "automation way". I don't get it. Anybody have an idea why this happens? Thank you, Mark I open an Excel Application and workbook via automation. So there are two Excel Applications open at the same time. The first Excel Application opens the second Excel Application and a workbook. A macro in the workbook of the second Excel Application is run. What I notice is the following: The macro runs slower when run via the automation |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automation Question
When I run Excel via automation I find that the macros run at normal speed
as long as that instance of Excel is in the foreground. This puts it in the foreground: XL.Visible = True This presumably is due to how Windows doles out processor time among applications. If you need to keep the Excel instance hidden you might have to live with slower speeds. As you may know, you can set the priority of a running application using Task Manager; I don't know how you can set it programmatically (or how much it would improve performance if you could). -- Jim Rech Excel MVP "Mark" wrote in message ... |I have developed a macro which can be run two ways: | | 1. The usual way: Open an Excel application, open the workbook (call it | myWB) and run the macro (call it myMacro). | 2. The automation way: | From a second workbook (call it MasterWB), create a new Excel application | via automation, open the workbook (myWB) in the new application and run the | macro (myMacro). | | Here's the issue that I'm having - the macro runs faster when run the "usual | way" than the "automation way". I don't get it. | | Anybody have an idea why this happens? | | Thank you, | Mark | | | | | I open an Excel Application and workbook via automation. | | So there are two Excel Applications open at the same time. The first Excel | Application opens the second Excel Application and a workbook. | | A macro in the workbook of the second Excel Application is run. | | What I notice is the following: | The macro runs slower when run via the automation | | | | | |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automation Question
Don't think this is really surprising, since you have a lot more overhead
when you run it via automation. For one thing, you are taxing your computer's processor and memory more just by having two Excel sessions going at once. But also, when run in automation, Windows has to do a lot more than just run the macro: it needs to keep an eye on that new Excel session, see if any events have been triggered, etc. But that is a natural trade-off: by getting the computer to do more for you it also makes it run slower. You need to decide if the tradeoff is worthwhile or no. "Mark" wrote: I have developed a macro which can be run two ways: 1. The usual way: Open an Excel application, open the workbook (call it myWB) and run the macro (call it myMacro). 2. The automation way: From a second workbook (call it MasterWB), create a new Excel application via automation, open the workbook (myWB) in the new application and run the macro (myMacro). Here's the issue that I'm having - the macro runs faster when run the "usual way" than the "automation way". I don't get it. Anybody have an idea why this happens? Thank you, Mark I open an Excel Application and workbook via automation. So there are two Excel Applications open at the same time. The first Excel Application opens the second Excel Application and a workbook. A macro in the workbook of the second Excel Application is run. What I notice is the following: The macro runs slower when run via the automation |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automation Question
Thanks for your comments.
I have not had a chance to try the "XL.visible = true " yet, but will. I may also try NOT to run the macro through automation, but in the same session. While automation is critical to other parts of the process, it is not so critical withrunning this particualr macro. Mark "Mark" wrote: I have developed a macro which can be run two ways: 1. The usual way: Open an Excel application, open the workbook (call it myWB) and run the macro (call it myMacro). 2. The automation way: From a second workbook (call it MasterWB), create a new Excel application via automation, open the workbook (myWB) in the new application and run the macro (myMacro). Here's the issue that I'm having - the macro runs faster when run the "usual way" than the "automation way". I don't get it. Anybody have an idea why this happens? Thank you, Mark I open an Excel Application and workbook via automation. So there are two Excel Applications open at the same time. The first Excel Application opens the second Excel Application and a workbook. A macro in the workbook of the second Excel Application is run. What I notice is the following: The macro runs slower when run via the automation |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
General Macro/Automation Question | Excel Discussion (Misc queries) | |||
Automation 2-Part Question... | Excel Discussion (Misc queries) | |||
MFC Automation | Excel Programming | |||
Automation | Excel Programming | |||
Range.Formula and Range question using Excel Automation | Excel Programming |