SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to Automate the closing of a window?


How to Automate the closing of a window?

Author
Message
sqlnoob2015
sqlnoob2015
SSC-Enthusiastic
SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)

Group: General Forum Members
Points: 105 Visits: 62
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?
Sue_H
Sue_H
SSC-Forever
SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)SSC-Forever (48K reputation)

Group: General Forum Members
Points: 48331 Visits: 12347
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
Ed Wagner
SSC Guru
SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214943 Visits: 11979
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
sqlnoob2015
SSC-Enthusiastic
SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)

Group: General Forum Members
Points: 105 Visits: 62
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
Jeff Moden
SSC Guru
SSC Guru (691K reputation)SSC Guru (691K reputation)SSC Guru (691K reputation)SSC Guru (691K reputation)SSC Guru (691K reputation)SSC Guru (691K reputation)SSC Guru (691K reputation)SSC Guru (691K reputation)

Group: General Forum Members
Points: 691175 Visits: 45613
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
sqlnoob2015
sqlnoob2015
SSC-Enthusiastic
SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)

Group: General Forum Members
Points: 105 Visits: 62
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
Mike Good
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5122 Visits: 1144
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
sqlnoob2015
SSC-Enthusiastic
SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)SSC-Enthusiastic (105 reputation)

Group: General Forum Members
Points: 105 Visits: 62
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
Jeff Moden
SSC Guru
SSC Guru (691K reputation)SSC Guru (691K reputation)SSC Guru (691K reputation)SSC Guru (691K reputation)SSC Guru (691K reputation)SSC Guru (691K reputation)SSC Guru (691K reputation)SSC Guru (691K reputation)

Group: General Forum Members
Points: 691175 Visits: 45613
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (691K reputation)SSC Guru (691K reputation)SSC Guru (691K reputation)SSC Guru (691K reputation)SSC Guru (691K reputation)SSC Guru (691K reputation)SSC Guru (691K reputation)SSC Guru (691K reputation)

Group: General Forum Members
Points: 691175 Visits: 45613
Now... about those deadlocks you don't want to hear about... Wink

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search