How to Automate the closing of a window?

  • sqlnoob2015

    SSC Veteran

    Points: 257

    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

    SSC Guru

    Points: 89885

    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: 286955

    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.

  • sqlnoob2015

    SSC Veteran

    Points: 257

    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: 993884

    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
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. πŸ˜‰

    Helpful Links:
    How to post code problems

  • sqlnoob2015

    SSC Veteran

    Points: 257

    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: 7350

    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: 257

    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: 993884

    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
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. πŸ˜‰

    Helpful Links:
    How to post code problems

  • Jeff Moden

    SSC Guru

    Points: 993884

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. πŸ˜‰

    Helpful Links:
    How to post code problems

  • sqlnoob2015

    SSC Veteran

    Points: 257

    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: 993884

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. πŸ˜‰

    Helpful Links:
    How to post code problems

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

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