Checking status of Excel

Question

Is there any way to check whether excel is busy doing something using vbscript so that I can perform some task on the opened workbook OR close the workbook only when its status is free?

Is there any similar property exists for word and PowerPoint also if it exists for Excel?

Note: I want to check the status of excel because if I perform some task on excel when its status is busy, task is not getting performed successfully.

---

Vijay

Answer 1

It depends what you mean by "busy doing something". For some scenarios, particularly while starting, you could check the xlApp.Ready state. Normally though if you try to do something and if Excel is working (eg calculating) your code will pause until Excel is ready to respond.

One scenario which can cause problems is if Excel is in Edit mode, eg user has pressed F2. This would be difficult to check in a vbscript, though in a different type of app (even VBA) could look where the "EXCEL6" window is, ie in order before or after any EXCEL7 workbook windows (requires a series of API calls)

Peter Thornton

Answer 2

Thanks Peter for the reply.

Actually, we are automating an Excel Plugin. While launching Excel,PowerPoint and Word, we are experiencing variable amount of time each time we launch any office application to load that plugin and then, we have to wait for that variable amount of time to perform any action.

Now, the issue is: we want our code to wait for that variable amount of time:

1) when the excel is launched,

2) when we perform any action on excel or that plugin, we want to wait for that variable amount of time before moving to another line of code

3) before exiting the excel application, we want to wait for variable amount of time before the excel is ready(because some action is performed before exiting the excel app).

If we don't wait and exit the excel app through code, then our plugin get disabled during the next launch due to which many issues are happening.

So, we want to wait for that variable amount of time in the above scenarios.

Is there any solution to this?

---

Vijay

Answer 3

What you mean by "an Excel plug-in", and are you first automating a new instance of Excel then loading the with automation loading your plug-in (whatever that is), and what's the app that's doing the automation

Peter Thornton

Answer 4

Excel Plug-In is a Plug-In that loads within Excel window and appears as an option in the ribbon on the top of the window.

and I am automating using HP's QTP (it uses VB Script and COM Object Model)

---

Vijay

Answer 5

Actually, we are automating an Excel Plugin. While launching Excel,PowerPoint and Word, we are experiencing variable amount of time each time we launch any office application to load that plugin and then, we have to wait for that variable amount of time to perform any action.

Indeed the time to start Excel and load any associated addins can vary considerably, typically quite long first time in a session, subsequent times much faster.

Normally when automating Excel you don't need to be concerned with the time, or build in delays, because the process is NOT asynchronous. IOW, your code suspends until each activity is completed,

Now, the issue is: we want our code to wait for that variable amount of time:

1) when the excel is launched,

Try this in VBScript or (or even Word-VBA but you'd need to switch to Word to see the Msgbox)

[/vbnet]
t1 = Timer
Set xl = CreateObject("excel.application")
t2 = Timer
While xl.ready = False
Wend
t3 = Timer
Set wb = xl.workbooks.Add
t4 = Timer
xl.Visible = True
t5 = Timer
MsgBox t2 - t1 & vbCr & t3 - t2 & vbCr & t4 - t3 & vbCr & t5 - t4
[/vbnet

There's no single line of code that willl give say when Excel is ready for your action, but as you can see in this simple demo your, your code is supended until Excel activity has completed.

The only slight exception is waiting for the xl.Ready state. Typically this is not something to be concerned about at startup though it can be in the OnLoad events of certain ComAddins, and more likely first time in a session. But even in ComAddins this is only  of a concern in relatively few scenarios

2) when we perform any action on excel or that plugin, we want to wait for that variable amount of time before moving to another line of code

I don't follow why you need to wait, if Excel is working your code will suspend until available.

3) before exiting the excel application, we want to wait for variable amount of time before the excel is ready(because some action is performed before exiting the excel app).

Again if you try to do some automation activity your code will suspend until Excel is available.

If we don't wait and exit the excel app through code, then our plugin get disabled during the next launch due to which many issues are happening.

I don't follow, why would your addin get disabled in the next launch. What sort of addin is it, does it not cleanly unload automatically when Excel closes.

Peter Thornton

Answer 6

I understand that code suspend until the current activity is completed. But the thought to wait for excel to become ready is coming from the fact that sometimes, when we clear some residual processes (for excel, word or PPT) through code after a function is complete, then the add-in is disabled when that corresponding office app is opened next time.

Note that it happens randomly but frequently but not always.

But as you said that code is suspended until the current activity is completed, then what is the need of ready property?

Also, is there any property similar to ready for word/powerpoint?

---

Vijay

Answer 7

Can you clarify more about this aspect

"when we clear some residual processes (for excel, word or PPT) through code after a function is complete, then the add-in is disabled when that corresponding office app is opened next time.

What triggers your code to clear some residual processes, after a function in 'what' is complete, in what way is the add-in disabled.

There are relatively rare occasions when the .Ready property returns false, one can be during start-up as ComAddins load before Excel has fully loaded and depending on what add-in's load event does it might be worth waiting 'While' .Ready = false. Personally I've never noticed any reason to look at it during shutdown.

I don't know about Word or PowerPoint in respect to all this.

Peter Thornton

Answer 8

I can give you an example Peter. Suppose, you are refreshing formulas in excel and after the refreshing takes place, I want to take the screenshot of the excel sheet. But in my case, the code takes screenshot before the refresh is completed. So, is there any way that I can wait for that amount of time till the refresh is completed?

Also, I want to tell you that till the refresh is completed, the mouse pointer status is busy.

Also, can you please let me know, what type of Excel Addins are there?

---

Answer 9

Excel will not tell you directly when it has finished working, but if you try to read some Excel property it won't return a value until it has finished calculating. Try xlApp.Ready, it won't return false but if/when ready it should return True.

As I mentioned before, the main problem of Excel not being ready is if the user has put it into Input mode, though you can check for that along the lines I also described.

As for Add-ins, there are Workbook which host VBA (extension *.xla/m) and if added to the Add-ins collection and Installed=True they will load at Excel start-up (except in automated new instances) and remain hidden. They can respond to events, you can install your own menu system. XLL addins can also be added to the addins collection. Both types can host UDFs, ie custom functions that can be used in worksheet cell formulas (apart from receiving inputs and returning values they cannot change the interface.

Other types are added to the COM-Addins collection, this is quite a large subject but a simple search will give you a lot more information (could be in various code languages but not VBA). There are also Automation Add-ins (not VBA)which host UDFs only.

Peter Thornton

<gupta vijay> wrote in message news:[e-mail]...

I can give you an example Peter. Suppose, you are refreshing formulas in excel and after the refreshing takes place, I want to take the screenshot of the excel sheet. But in my case, the code takes screenshot before the refresh is completed. So, is there any way that I can wait for that amount of time till the refresh is completed?



Also, I want to tell you that till the refresh is completed, the mouse pointer status is busy.



Also, can you please let me know, what type of Excel Addins are there?

--