How to Automate the closing of a window?

Question

How to Automate the closing of a window?

sqlnoob2015

SSC Veteran

Points: 287
More actions
December 11, 2017 at 12:36 pm

#151600

What is the best way to automate the clearing out of an error message that pops up a few times a day in Excel?
Basically every now and then I get a "deadlock error" which causes a small window to pop up on the screen. Someone then has to go in and click OK and then refresh to resume the Excel process.
No need to get into the deadlock issue. We know what is causing it but still want to find a work around.
I'm trying to find a windows macro, VBA marco or something similar that will detect when this message comes up and OK's past it to resume the auto-refresh process in Excel.
What is the best way to go about this?

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

sqlnoob2015 - Monday, December 11, 2017 12:36 PM
What is the best way to automate the clearing out of an error message that pops up a few times a day in Excel?
Basically every now and then I get a "deadlock error" which causes a small window to pop up on the screen. Someone then has to go in and click OK and then refresh to resume the Excel process.
No need to get into the deadlock issue. We know what is causing it but still want to find a work around.
I'm trying to find a windows macro, VBA marco or something similar that will detect when this message comes up and OK's past it to resume the auto-refresh process in Excel.
What is the best way to go about this?

If the message box has the focus then I would guess a macro in the app itself isn't going to be of much use. That's part of the reason that unattended processing like that with Excel isn't supported.
It seems that something would need to be incorporated in whatever the Excel process is so look at whatever that code is kicking of the Excel process - use some type of error handling. Worst case would be doing something to simply ignore the error which often ends up in ignoring other crucial errors and making things worse. To do that in Excel, I believe it's something like Application.DisplayAlerts = False

Sue

Ed Wagner SSC Guru Points: 287026 More actions · Answer 2

Sue_H - Tuesday, December 12, 2017 7:20 AM
sqlnoob2015 - Monday, December 11, 2017 12:36 PM
What is the best way to automate the clearing out of an error message that pops up a few times a day in Excel?
Basically every now and then I get a "deadlock error" which causes a small window to pop up on the screen. Someone then has to go in and click OK and then refresh to resume the Excel process.
No need to get into the deadlock issue. We know what is causing it but still want to find a work around.
I'm trying to find a windows macro, VBA marco or something similar that will detect when this message comes up and OK's past it to resume the auto-refresh process in Excel.
What is the best way to go about this?
If the message box has the focus then I would guess a macro in the app itself isn't going to be of much use. That's part of the reason that unattended processing like that with Excel isn't supported.
It seems that something would need to be incorporated in whatever the Excel process is so look at whatever that code is kicking of the Excel process - use some type of error handling. Worst case would be doing something to simply ignore the error which often ends up in ignoring other crucial errors and making things worse. To do that in Excel, I believe it's something like Application.DisplayAlerts = False
Sue

Or skip the error display with On Error Resume Next before you make the call to the database. The On Error syntax also allows you to trap the error and take some appropriate action if you wish.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

sqlnoob2015 SSC Veteran Points: 287 More actions · Answer 3

Thanks Ed and Sue. I guess i was thinking there was some type of windows macro (mouse more and click) that will clear out messages like the one I have. But I'll give the options you mentioned a shot.

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 4

sqlnoob2015 - Monday, December 11, 2017 12:36 PM
What is the best way to automate the clearing out of an error message that pops up a few times a day in Excel?
Basically every now and then I get a "deadlock error" which causes a small window to pop up on the screen. Someone then has to go in and click OK and then refresh to resume the Excel process.
No need to get into the deadlock issue. We know what is causing it but still want to find a work around.
I'm trying to find a windows macro, VBA marco or something similar that will detect when this message comes up and OK's past it to resume the auto-refresh process in Excel.
What is the best way to go about this?

What is Excel being use for here? I'm asking because you might not even need for it to be running to update it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sqlnoob2015 SSC Veteran Points: 287 More actions · Answer 5

I'm pulling in sales numbers from a database that is updated every 15 minutes and displayed on a monitor for our salespeople. The numbers are displayed in a graphical manner within Excel using the refresh option (in Connection Properties).

Mike Good SSCertifiable Points: 7415 More actions · Answer 6

For many years I've been using a free utility called ClickOff, by Johannes Hübner. Works well. Easy to find on the internet.

sqlnoob2015 SSC Veteran Points: 287 More actions · Answer 7

Mike Good - Wednesday, December 13, 2017 9:49 AM
For many years I've been using a free utility called ClickOff, by Johannes HÃ¼bner. Works well. Easy to find on the internet.

Perfect. This is pretty much what I had in mind. Although eventually I'll need to address the core issue, this fixes the problem for now. Thanks Mike!

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 8

sqlnoob2015 - Tuesday, December 12, 2017 4:17 PM
I'm pulling in sales numbers from a database that is updated every 15 minutes and displayed on a monitor for our salespeople. The numbers are displayed in a graphical manner within Excel using the refresh option (in Connection Properties).

If it's that important, then stop having Excel do the refresh. Create two identical reporting tables. For purposes of discussion, we'll call them TableA and TableB. Have one "TableActive" view and a "TableWork" view (call them what ever you like). To start with, the TableActive view would point at TableA and the "TableWork" view would point at TableB. The spreadsheet would read only from the TableActive view.

Have a stored proc in a job that outputs to the updateable TableWork view, which is currently pointing at TableB. When it's done with the update (or whatever), alter the TableWork view to point at TableA and the TableActive view to point at the freshly updated TableB. Next run, do the exact same thing except reverse what the views are pointing at.

If a deadlock occurs, don't execute the code that repoints the views. Excel will never know the difference.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 9

Now... about those deadlocks you don't want to hear about... 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sqlnoob2015 SSC Veteran Points: 287 More actions · Answer 10

Jeff Moden - Wednesday, December 13, 2017 7:42 PM
sqlnoob2015 - Tuesday, December 12, 2017 4:17 PM
I'm pulling in sales numbers from a database that is updated every 15 minutes and displayed on a monitor for our salespeople. The numbers are displayed in a graphical manner within Excel using the refresh option (in Connection Properties).
If it's that important, then stop having Excel do the refresh. Create two identical reporting tables. For purposes of discussion, we'll call them TableA and TableB. Have one "TableActive" view and a "TableWork" view (call them what ever you like). To start with, the TableActive view would point at TableA and the "TableWork" view would point at TableB. The spreadsheet would read only from the TableActive view.
Have a stored proc in a job that outputs to the updateable TableWork view, which is currently pointing at TableB. When it's done with the update (or whatever), alter the TableWork view to point at TableA and the TableActive view to point at the freshly updated TableB. Next run, do the exact same thing except reverse what the views are pointing at.
If a deadlock occurs, don't execute the code that repoints the views. Excel will never know the difference.

Jeff, I'm confused on how that will prevent Excel from alerting to a Deadlock message. I'm trying to think through the logic of your instructions but I might be missing something. Currently I already have a SP that updates a table within Excel. The table is updated every 15 minutes with raw data. I have formulas that pull data from that table along with a few other tables are eventually displayed in the graphs. The error occasionally occurs when the table is refreshed. How can I get around Excel doing the refresh?

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 11

sqlnoob2015 - Thursday, December 14, 2017 3:20 PM
Jeff Moden - Wednesday, December 13, 2017 7:42 PM
sqlnoob2015 - Tuesday, December 12, 2017 4:17 PM
I'm pulling in sales numbers from a database that is updated every 15 minutes and displayed on a monitor for our salespeople. The numbers are displayed in a graphical manner within Excel using the refresh option (in Connection Properties).
If it's that important, then stop having Excel do the refresh. Create two identical reporting tables. For purposes of discussion, we'll call them TableA and TableB. Have one "TableActive" view and a "TableWork" view (call them what ever you like). To start with, the TableActive view would point at TableA and the "TableWork" view would point at TableB. The spreadsheet would read only from the TableActive view.
Have a stored proc in a job that outputs to the updateable TableWork view, which is currently pointing at TableB. When it's done with the update (or whatever), alter the TableWork view to point at TableA and the TableActive view to point at the freshly updated TableB. Next run, do the exact same thing except reverse what the views are pointing at.
If a deadlock occurs, don't execute the code that repoints the views. Excel will never know the difference.
Jeff, I'm confused on how that will prevent Excel from alerting to a Deadlock message. I'm trying to think through the logic of your instructions but I might be missing something. Currently I already have a SP that updates a table within Excel. The table is updated every 15 minutes with raw data. I have formulas that pull data from that table along with a few other tables are eventually displayed in the graphs. The error occasionally occurs when the table is refreshed. How can I get around Excel doing the refresh?

It will prevent deadlock messaging because Excel will never see a deadlock. Excel will simply read from a view that points to a table that is guaranteed to be static.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)