I have an excel file which does some SAP automation, part of it being that it extracts information from SAP and then imports it. Due to the fact that the retrieval of said data in SAP takes 5-10 minutes, i was forced in the past to use the workaround to kill the "waiting for another application to complete an ole action" message, as it defeated the purpose of the automation. Code i use is:
Public Declare PtrSafe Function _
CoRegisterMessageFilter Lib "OLE32.DLL" _
(ByVal lFilterIn As Long, _
ByRef lPreviousFilter) As Long
Dim lMsgFilter as Long
CoRegisterMessageFilter 0&, lMsgFilter 'to supress it
CoRegisterMessageFilter lMsgFilter, lMsgFilter 'to restore it
(all of this put neatly in their own respective subs to supress and restore,of course). All worked well for years, actually, until the latest Office update we got; somehow these lines of code area breaking the Power Query functionality.The symptoms are as follows: when i open the file in question, click on Data --> Queries and Connections this error appears: "excel ran into an unspecified error"
Steps i did to confirm that those lines of code are the culprit:
- disabled all add-ins
- make sure in the VBA code i have everything is late binded
- started excel in safe to see the behaviour
Most significant test was that i put those lines in a brand new Excel file and i was able to reproduce my issue. The weird thing is that , by doing this, i stumbled upon a workaround: if i open a new, blank Excel file and click on Data-->Queries and Connections, the pane and all the options there work just fine. If i then open my culprit file it also works again and the error is gone!
By checking what Excel does when i do this workaround, i managed to see in the statusbar that when the pane loads ok i can see the ".Net framework loading..." message in the statusbar BUT when the error appears, the message is not there. This leaves me to believe that it is somehow related.
My question is: what could possible be in the last Office update that triggers this behaviour?