PDA

View Full Version : Solved: Control Workbook from another Application Window



Stargazer
09-09-2010, 08:52 AM
Hiya,

We've developed a countdown clock that closes a workbook after 3mins of inactivity. It works great, except for one small problem... the clock stops ticking down if the user leaves puts any workbook cell into edit.

Said user then leaves a cell in edit, goes away from her desk for an hour and in the meantime nobody else can access the file to be allocated new orders for validation.

The Workbook ust not be shared because the idiots I work for would just blunder about and ruin it without realising they are breaking it. At least if only one person can run it at a time, it minimises the potential damage done significantly.

To get back to the subject at hand, we beleive that if we can run the clock independantly in a separate application window, it won't be subject to the cell edit problem.

On file launch, we plan for our file (that we've nick-named Nova) to open and display a splashscreen. While the user is admiring our logo, Excel will call the countdown timer file into a new window which is rapidly hidden leaving behind only a small userform. This we can do.

What we can't do is tell Nova to reset the countdown clock on a button press, nor can we get the countdown clock to close Nova. We've made the discovery of the fabulous AppActivate function, but merely selecting the destination window isn't enough.

Can anyone here tell us if it is possible (and if so how) to manipulate a workbook opened in a seperate excel application window?

And and all help is greatly appreciated.

Thanks in advance,

Rob.

Bob Phillips
09-09-2010, 09:23 AM
If the clock is in another application, it can countdown fine, but how will it know there has been no activity is another executing application. And grabbing its handle to stop it is fine if there is just one, but if there are many it could stop the wrong one.

I would give up on that idea and try educating the users (or sack them!).

Stargazer
09-09-2010, 09:39 AM
Heh...

Sadly, it's the idiots that I work 'for' that are breakignt hings here. As for educating them... It's impossible.

Nova V1 didn't have a clock. Instead it came with an instruction that "when you're not using it, close it. That way other poeple can allocate out work if you have to leave your desk and the workstation has locked itself."

They didn't pay attention. So we made a point of telling our co-workers to make a big deal everytime they had to sit around for an hour waiting for them to come back and allocate more orders because the sheet had been left open... No difference.

Hence why we have a time-out function. Even that launches another file while closing itself and leaves a massive message on-screen reminding them that they should have closed it manually. They still don't get the message.

I thought it was just a quirky little saying, but it really is true... Artificial intelligence is no match for natural stupidity!

If two windows can talk to each other, we have everything in place already... It would just be a simple matter of modifying a couple of subs to account for the extra window.

We can probably work out the rest if we could just be shown something simple like, how to write the word "Hurrah" to a another application window, and if we knew how to call a sub-routine from the other window.

Every day, Excel VBA kicks my ass. Most days, we eventually find something that works. This has eaten up an entire afternoon of trialling and erroring. So it's time to break out the big guns of the VBAX community.

I'm counting on there being a way.

I'll do a Christian Horner kind of promise if need be... I'll jump naked into a swimming pool if it can be done! Just please, please, please... Tell me it can be done.

Rob.

Stargazer
09-10-2010, 01:59 AM
We're a small step closer here...

The guy who works alongside me has managed to find some code that will open a new workbook in a anew application window and in cell A1 write a small message.

What we're having problems with is getting it to do something with an existing application window as opposed to a new one.

So instead of dim'ing xlApp as New Application, is it possible to dim xlApp as an existing Application?

Bob Phillips
09-10-2010, 04:35 AM
Dim xlApp As Object

On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
On Error GoTo 0
If xlApp Is Nothing Then

Set xlApp = CreateObject("Excel.Application")
End If

Stargazer
09-10-2010, 06:04 AM
:bow: Thank you! :bow:

We have begun to modify your code and tagging our own instructions to it and it appears to be doing what we need it to do.

Your title of VBA Overlord is well deserved!

I forsee my marking this thread as resolved, however, I will wait just a couple of hours before doing so in case we have an immediate follow up, but the future is looking bright.

Again, Thank you ever so much. Your help has been invaluable. As a mark of respect, I'll see if our next project can be named after you. :)

Cheers,

Rob.

Bob Phillips
09-10-2010, 06:50 AM
:As a mark of respect, I'll see if our next project can be named after you. :)

LOL!