Detect "Office" dialog which is stalling VBA ("This experience is unavailable")

AOB

Well-known Member
Joined
Dec 15, 2010
Messages
613
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. 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"

This experience is unavailable.png


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
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
38,913
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
I would think a Windows timer or hook should be able to do this, as long as you can figure out what window it is that you are looking for.
 

AOB

Well-known Member
Joined
Dec 15, 2010
Messages
613
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
Thanks @RoryA I was sceptical of using hooks as I don't want to incorporate something at the expense of overall system performance and I've heard hooks can be detrimental. Also, not entirely sure "where" the hook should reside (do I reference the APIs within the same Excel workbook, or does it need to be created independently somewhere?) Not familiar with Windows timers at all.

Have just had an idea to do this as a Windows service, I might see if that's viable...
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,098
Messages
5,852,162
Members
431,488
Latest member
ePayslip

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top