sol:what are pros & cons of using existing v new excel instances during automation?

hi all,

First of all, I apologise if this should be in another Forum (perhaps Dev'?). Mod's, please move it as you see fit.


Personally, I like the principle of keeping excel automation work (eg modifying excel from ppt or Access) separate from any existing excel instances. I feel this allows me to continue working in other excel instances when something is working slowly via automation. However, an office colleague (more skilled in Access) prefers to use an existing instance. My current concern is that some/most of my office's existing "instancing" code, such as a version of "DetectExcel" doesn't actually use a boolean flag to identify which approach (GetObject or CreateObject) is used in setting an Excel.Application reference.

I don't want to have tunnel vision when I discuss this with my colleague so I'm doing some research & would like to know everyone's thoughts regarding the below questions:
(note: any automation is limited to Report creation or initial data manipulation within a Reporting team of four who all use Excel 2007.)

Question 1: What are the pros & cons (limitations) of using existing excel instances versus creating & using new excel instances during automation?
(eg fewer system resources with fewer app's running, self contained/sandboxed, need for fully explicit referencing...)
Question 2: Do you personally prefer to create new instances or use existing references?
The next one may deserve a thread of it's own but I'll ask it here first...
Question 3: What are the limitations of using code like

Please Login or Register  to view this content.

, esp with respect to working with chart objects in Excel 2007?
(I'm considering it for code that has been thoroughly tested when xlapp is visible. Edit: I'll be reading up on Jon Peltier's site tomorrow)

tia
Rob

Hello Rob,

I am going to pick the instancing question regarding GetObject and CreateObject. It is important to understand the difference between the two. GetObject can be used in two ways. The first is to return an instance of an object that has been created and to also create a new instance of an object. Here is the syntax...

'Basic Syntax
GetObject([pathname] [, class])

'Attach to an existing object - If the pathname argument is omitted, GetObject returns a currently active object of the specified type
GetObject(, "Excel.Application")

'Create a New Instance of the object - If pathname is a zero-length string (""), GetObject returns a new object instance of the specified type.
GetObject("", "Excel.Application")

NOTE: If you don't specify the object's class, Automation determines the application to start and the object to activate, based on the file name you provide. Some files, however, may support more than one class of object.

Attaching to an existing object may or may not be a good practice. It depends on the object and what you want to do. The obvious advantage of attaching to an existing object is a reduction in system resources. If processing speed is an issue then it may not be advantageous to share system resources.

Using GetObject to attach to an object instance will attach you to that object. This done using the ROT or Running Object Table. Only one instance of any object will be in this table. Which instance is anybody's guess. In most cases this fine, but if you are using a program like Outlook then you can run into trouble. Outlook leaves stubs behind to handle background process like email. If you are expecting to attach to the full application object, you may not and your code could fail.

CreateObject does exactly that, it creates and returns a reference to an ActiveX object. GetObject can create a new instance only on the local machine. CreateObject can be used to create the object on a network server. The CreateObject syntax is as follows..

'Basic Syntax
CreateObject(class,[servername])

The class argument uses the syntax appname.objecttype and has these parts:
appname Required; Variant (String). The name of the application providing the object.
objecttype Required; Variant (String). The type or class of object to create

You can create an object on a remote networked computer by passing the name of the computer to the servername argument of CreateObject. That name is the same as the Machine Name portion of a share name: for a share named "\\MyServer\Public," servername is "MyServer."

hi Leith,

Thanks for the leading the charge - hopefully some others will join in too with responses for my other questions
(Others, see my first post for the three questions)

I see I should have listened to my own advice to other OP's!
1) Make the [F1] key my friend & 2) given specific examples so that you guys have come contextual borders for framing your answers.

Originally Posted by Leith Ross

...Attaching to an existing object may or may not be a good practice. It depends on the object and what you want to do. The obvious advantage of attaching to an existing object is a reduction in system resources. If processing speed is an issue then it may not be advantageous to share system resources.

My current examples are:
1) Using Powerpoint as the VBA host application to run VBA code which loops through specific Excel files (~max of 10), opening them, rezooming the view & copying two sheets (identified by std naming convention) into specific ppt slides as OLE objects (breaking links each time using Shyam Pillai's code), and making some minor formatting/title changes. The process takes between 1 and 5 minutes per ppt presentation and is manually repeated for 6 different presentations.
- I feel (?)) this example isn't too exhausting on system resources (esp as I've read that a created Excel Instance doesn't load XLstart folder files or addins etc). So I lean towards creating a separate instance which will allow me to continue working on other unrelated excel files.
- I don't drink coffee or smoke but even if I did, I can't imagine taking that many "breaks" in 1/2 an hour while watching & waiting for each macro to finish!
Question 1: Do you think this example is worth a separate instance?
(eg using GetObject with a zero length path string before initiating the loop through excel files section of code.)

2) Using Access to do the grunt work of calculations & querying etc. Then using Access as the VBA host application & pushing recordsets (or refreshing external data ranges) into a standard "Template" xls or xlsx file (no macros for "ease" of later distribution - based on receivers' excel savvy), some formatting of used ranges within excel, and then saving the excel "template" using a std naming convention. These types of examples take anywhere between 1 & five-10 minutes and some have the potential for optimising but, as we prepare for a IT system change, they are considered along the lines of "it works & if it ain't broke...".
- Again, if I'm in the process of working on other excel files I'd rather not have to stop each time.

As you may be able to see my peripheral vision is getting cloudy as my tunnel vision comes on!

Originally Posted by Leith Ross

...Using GetObject to attach to an object instance will attach you to that object. This done using the ROT or Running Object Table. Only one instance of any object will be in this table. Which instance is anybody's guess. In most cases this fine, but if you are using a program like Outlook then you can run into trouble. Outlook leaves stubs behind to handle background process like email. If you are expecting to attach to the full application object, you may not and your code could fail.

Hmm, Thanks Leith, I can see a couple of hours of reading coming on as I investigate what the ROT is and does, and how I may be able to make use of it
I've only ever used code once in Outlook and don't think I really use Outlook's native functionality to its potential so I'm unlikely to get to into this, but thanks for the warning about stubs. Where I'm interacting with Outlook from Excel I use Ron Debruin's tried & true code

Originally Posted by Leith Ross

...CreateObject does exactly that, it creates and returns a reference to an ActiveX object. GetObject can create a new instance only on the local machine. CreateObject can be used to create the object on a network server.

(perhaps I need to do some more reading on activex objects too...)
As mentioned above, my tunnel vision puts CreateObject right in my sights. At this stage, I think I'm unlikely to create an object on a network server because 1) I don't really understand when that would be suitable, 2) the servers are in the States, and 3) I imagine I.S. have things locked down quite tightly.

You've taught me that using a zero length Path string with GetObject will create a new instance (even if it was just a quote of Help files (thankyou, it was a nice gentle reminder). I'm now curious...
Question 2: Are there performance differences (or specific limitations) between using GetObject("","...") and CreateObject("...")?

Question 3: Based on my above examples, which approach would be the best way to go?



Thanks
Rob

Hello Rob,

As for question #1, I think time considerations warrant using separate instances. Your system is probably of sufficient size to handle it. Why wait when you can be getting other things done?

Not knowing exactly what your Excel files are and how they are laid out, I am going to assume they are database files if you want to use Access. If the Excel files are databases then Access is definitely the way to go in terms of processing speed and manipulation of the data. If not then the decision would have to based on the files' themselves as to which automation method would be best.

The ROT is something that system and COM programmers can make use of but not VBA programmers. For VBA users, it is little more than footnote and for programmers a curiosity. If nothing else it could led you to discover more about the internal workings of Windows, which could lead to ... "The API". You can not really do any serious programming in Windows without learning, at least something about, the Applications Programming Interface. It isn't for everyone, but if you are serious about making Windows work for you then it is must.

I explained the finer points of difference between GetObject and CreateObject in my previous post. To sum it up (in an oversimplified manner) use GetObject when you want to attach to an existing instance and CreateObject when you want a separate instance. CreateObject will work locally as well as across a network.

I hope my rambling is of some help to you in making a decision. After all, you are the one who can best decide what is or isn't going to work in your situation.

Hi Leith,

LOL!
You're hardly rambling when compared against my posts. Yes, you're right, I do have to decide - well... negotiate with my colleague

Yes, the Excel files are database style files. Access is being stretched slightly (hopefully the future upgrade will change this), as it runs some SQL queries on millions of records in an AS400 system. These records are summarised into ~hundreds of thousands of records, for further crunching in Access, with the final "summary" of thousands of records being pushed out to Excel.
- The size of some of the data sets has been a big eye opener for me (I'm 2 mths into a new role) and it's accelerating my Access learning at the same time as it expands my understanding of some of Excel 2007's "Big Grid" limitations. Now I have first hand experience, instead of just the knowledge from reading Charles William's Decison Models site!

Oh well, I've Googled & opened a few ROT related tabs, so I'll have a read of them over the weekend - even if it is just becomes a footnote while I'm in my current status.
I'm serious about making Windows work for me so it is definitely time for me to get learning "the API"*. At the moment it is a completely foreign language to me. On the few occasions where I have made API calls within my VBA code (I hope this is the right terminology?) it has been with blind faith using existing code from various websites or Forums with absolutely no understanding.

*However, it will have to wait a wee while while I get myself upto "more than competent, but always learning" in Access. Do you have any recommended resources for when I do start investigating API?

Who would be the best people to ask about Question 3 in my first post?


Thanks again
Rob

Responding only because requested, I just don't do any any amount of batch processing in Excel, and so have nothing to contribute to the discussion.

My only comment regarding Q3 is that whatever processing you're doing in a hidden, non-interactive instance better be deterministic, finite, and bulletproof.

Thanks Shg,

I wasn't sure if you (or others) would have anything to offer but I know that some of you guys have had much more experience than me so you may have come across situations I haven't yet seen - even if it's quite probable that I could see them in the very near future. Thankyou I appreciate the response.

Yes, it's definitely finite, and it's meant to be deterministic, but my colleague's told me about some situations where things, seemingly* as unrelated as network printer settings (*ie there are no printing or page-break previews etc are included in the code), have an impact. This (& uncertain connectivity with American based SharePoint/servers) has made me doubt my ability to make it really bulletproof.
Also, I've just read "Zorvek's" responses in Colo's Cell Master's section. Edit: He recommends avoiding homogenised error handling because this can mask the cause of errors. He suggests that instead of using error handling, a better coding practice involves the "preflight" assertion of input variables to assist in making app's rock solid /End Edit. This has made me think a LOT about the variety of potential problems & how I can quickly check/overcome for them without taking too much extra time in development.

The processing time is short & mostly performed on a "temp" copy before being reviewed & then being published as a final document. This, plus the fact I've seen a more processes killed with the three finger salute in 6 weeks at my new job in comparison to my previous two jobs, makes me slightly happier about running a non-interactive instance (ie not too much time is lost & people know how to respond). BUT I would still like to get any code as good as I can make it so that no time is lost.

Thanks
Rob

Hello Rob,

When I was in Navy school, my instructors always told me "Nothing is foolproof because fools are so ingenious." Same holds true for programming.

LOL!

yep, I guess that is part of the challenge and fun of programming - how close to their levels of ingenuity can we get?

Rob

He recommends avoiding masking errors by using homogenised error handling & instead suggests "preflight" assertion of input variables to assist in making app's rock solid.

Good advice.

I have code that I've modified a dozen times in as many years because I reuse it with different data and it breaks. I regarded it as rock-solid in each iteration. Of course, now it really is rock solid ...

My code would be much better if I wrote it more frequently for other people to use, and they returned it to me, tattered and lifeless, to fix.

Blimey!
My lack of punctuation in the sentence you quoted was atrocious.

Lol, tattered & lifeless creations can definitely make us more thorough for the next version/project.
I think it is good advice too. In fact, I'm sure there's a thread in the Watercooler where a few of us have discussed the quality of code (versus speed of responses) in our responses & the potential impact on the habits of new coders.
Rob

Originally Posted by broro183

Also, I've just read "Zorvek's" responses in Colo's Cell Master's section.

FWIW, I disagree with a lot of Kevin's thoughts on error handling. I don't think a user should ever see a Debug button (a tester, yes; user, no) for instance.

Thanks RomperStomper,

Your thoughts are definitely worth sharing

hmmm...
Yes, actually, when I think about it, I agree that a user shouldn't see the Debug button. It certainly doesn't lead to initial confidence from the users or even testers (depending on the context/experience). I haven't done much work that gets distributed, so my only personal experience of having testers for vba work involved asking them to send me screenshots if the debug window ever popped up.
I guess I skim read that part (above) because the point that has stuck in my mind is the "preflight assertion". I've become more focused on thinking about all the possible errors & how I could code (in the initial development) to prevent them from occuring - before even handing files over to others for testing.

Thanks
Rob

I do agree it's best to anticipate and avoid the most likely errors. I don't go as far as he has in the past, which was to suggest you shouldn't have any error handling at all. I don't think he still holds to that (it was a fun argument though) but I could be wrong. For a smart guy, he has some odd ideas at times...

Yep, it would be a fun argument & I guess... it would be a boring world if everyoone was all the same!