AOB
Well-known Member
- Joined
- Dec 15, 2010
- Messages
- 667
- Office Version
- 365
- 2016
- 2013
- Platform
- Windows
Hi all,
I have a VBA macro that runs on a task scheduler locally. Has been running for the guts of a year, no issues (it maintains a dashboard on a SharePoint site)
Recently, the code gets interrupted by what appears to be an Office-context dialog (as distinct from an Excel-context dialog) telling me "This experience is unavailable"
Now, I know why this is happening, and I know precisely where in the code it gets thrown. However - the code still runs perfectly fine. Once I click the OK button on this dialog, the code resumes and completes without issue. So - there is no error or exception thrown within Excel (such that I could trap it and handle it accordingly) The code just pauses until I click the OK button.
The problem with this, of course, is that this code is running on a scheduler. Which means that every time that task is triggered, if I'm not at my desk to manually click this OK button, the code hangs and the task never completes. And subsequent instances of the task never trigger at all because the scheduler can see that the first instance is still running. So the whole thing falls over.
What I'm looking to do, is to find some way of detecting this dialog and acknowledging it (e.g. via a SendKeys command?) such that, if/when it pops up, it can be autonomously acknowledged and the code can resume immediately. Bear in mind, this popup isn't within Excel (or else DisableAlerts would do the trick here) and there isn't any exception (so I can't trap it with an error handler).
Initially, my inclination was to just incorporate some kind of do while / loop function to check for window handlers / captions that matched a criteria, pass the <Enter> key across, confirm the window disappears and then resume. But I can't do that in the existing VBA code because once the line that causes the dialog to appear has been run, the next line isn't "set" until after the dialog has been acknowledged. So the only way to do that, would be to have some kind of separate thread running in parallel, checking for that window handler continuously and closing it, for the duration of the run. And, of course, Excel doesn't "do" multi-threading, so I presume that option is out the window.
The other thing I was trying to figure out was, whether there might be a workbook-level or application-level event that might get triggered when the dialog appears, which I might leverage, á la Workbook_WindowDeactivate(ByVal Wn As Window) - but while debugging the code, I was never able to fire such an event (annoyingly, the dialog doesn't promote itself to be the active window, it hides itself in the background and you have to go looking for it - you can imagine my frustration when this first started happening...) If anybody knows of such an event that I could trap, I would be all ears!
The last option I can come up with is to create a second workbook, or perhaps just a Windows script, that I start just prior to running the macro, which performs the parallel scanning of open windows for the detect-and-acknowledge, and then stops again once the macro has finished. This seems a bit fiddly to me and I'm not sure if I'd be able to modify the existing task to achieve this anyway. If anybody has done anything similar to this themselves, again, I would be very keen to learn how you approached it.
Of course, if anybody has any other suggestions as to how to get around this problem, I would be delighted!
Thanks!
AOB
I have a VBA macro that runs on a task scheduler locally. Has been running for the guts of a year, no issues (it maintains a dashboard on a SharePoint site)
Recently, the code gets interrupted by what appears to be an Office-context dialog (as distinct from an Excel-context dialog) telling me "This experience is unavailable"
Now, I know why this is happening, and I know precisely where in the code it gets thrown. However - the code still runs perfectly fine. Once I click the OK button on this dialog, the code resumes and completes without issue. So - there is no error or exception thrown within Excel (such that I could trap it and handle it accordingly) The code just pauses until I click the OK button.
The problem with this, of course, is that this code is running on a scheduler. Which means that every time that task is triggered, if I'm not at my desk to manually click this OK button, the code hangs and the task never completes. And subsequent instances of the task never trigger at all because the scheduler can see that the first instance is still running. So the whole thing falls over.
What I'm looking to do, is to find some way of detecting this dialog and acknowledging it (e.g. via a SendKeys command?) such that, if/when it pops up, it can be autonomously acknowledged and the code can resume immediately. Bear in mind, this popup isn't within Excel (or else DisableAlerts would do the trick here) and there isn't any exception (so I can't trap it with an error handler).
Initially, my inclination was to just incorporate some kind of do while / loop function to check for window handlers / captions that matched a criteria, pass the <Enter> key across, confirm the window disappears and then resume. But I can't do that in the existing VBA code because once the line that causes the dialog to appear has been run, the next line isn't "set" until after the dialog has been acknowledged. So the only way to do that, would be to have some kind of separate thread running in parallel, checking for that window handler continuously and closing it, for the duration of the run. And, of course, Excel doesn't "do" multi-threading, so I presume that option is out the window.
The other thing I was trying to figure out was, whether there might be a workbook-level or application-level event that might get triggered when the dialog appears, which I might leverage, á la Workbook_WindowDeactivate(ByVal Wn As Window) - but while debugging the code, I was never able to fire such an event (annoyingly, the dialog doesn't promote itself to be the active window, it hides itself in the background and you have to go looking for it - you can imagine my frustration when this first started happening...) If anybody knows of such an event that I could trap, I would be all ears!
The last option I can come up with is to create a second workbook, or perhaps just a Windows script, that I start just prior to running the macro, which performs the parallel scanning of open windows for the detect-and-acknowledge, and then stops again once the macro has finished. This seems a bit fiddly to me and I'm not sure if I'd be able to modify the existing task to achieve this anyway. If anybody has done anything similar to this themselves, again, I would be very keen to learn how you approached it.
Of course, if anybody has any other suggestions as to how to get around this problem, I would be delighted!
Thanks!
AOB
