Excel crash in a specific condition at the end of a power query

[PhilippeMuniesa]

Hello everyone.

I built a project based on excel sheets (2019), containing powerquery queries and powerpivot tables.

All the sheets include VBA event macros, to facilitate use by users.

I noticed a malfunction without finding a workaround.

When a workbook (workbook 1) containing queries is opened, and the queries are called by the VBA instruction:
VBA >>>> QueryTable.Refresh BackgroundQuery: = False

If at the end of the update, we go back to another workbook (workbook 2), and we try to enter data in a cell, EXCEL systematically crashes.

I also noticed that the scroll was inoperative with the mouse wheel in workbook 2.

It seems to me that this dysfunction is due to the MASHUP EVALUATION process. Indeed, this situation persists as long as the MASHUP EVALUATION is in operation (occupies the processor) in the classifier 1 which called the POWERQUERY request, even though the data resulting from the requests have been injected into EXCEL.

launching a macro, by clicking on a Command button on the workbook 2 will also prevent the crash.

I tried a lot of background query solutions: = Tru or false

Authorization, as well as various parameters in the request options, but nothing helps.

====

Example: Workbook 2 has a toggle command button that enables or disables responsiveness to events (application.enableevents), another command button that lets you insert hyperlinks into the sheet, and a hyperlink that points to a workbook 1 containing PowerQuery queries.

Once the queries of workbook 1 have been updated by VBA QueryTable.Refresh BackgroundQuery: = False, if at the end of the data update, we reactivate workbook 2 (without closing workbook 1), and that we between the data in a cell, validation by ENTER crashes EXCEL.

If we wait for the end of the use of the processors by Mashup evaluation, or if for example I click on one of the command buttons of the workbook 1, normal operation is restored. I tried to launch an event macro when activating workbook 1, but that does not change, the macro must be started manually.

===

MY QUESTION (after this long description). Are you aware of this BUG, ​​and do you know how to get around it.

Thank you in advance.

[watkinnc]

This is why there IS Power Query, so we don't have to use macros for everything!

[This is the actual answer :)]

--Nate

[PhilippeMuniesa]

I don't know if it's macros or VBA, I did the following test

New open workbook containing nothing.

Binder 2 containing open PQ requests

Update a query by right clicking on the destination table and UPDATE.

Right at the end of the update, go back to class 1 and validate an entry in a cell.

Excel crash.

Another test, same scenario, but workbook 2 in creation mode (developer tab in creation mode) which in principle interrupts VBA interactions.

Same operations, manual update of the Powerquery query and immediate return to workbook 1, (F2 key and ENTER).

Excel crash.

Another scenario

1 workbook, without macro, one of which contains a query. Opening of this workbook.
While updating the request, opening a blank workbook, entering data in a cell and ENTER.

Excel crash

So for me it's not macros.

Is this a problem specific to my system, or is it a General Bug.

I attach the files that have been uploaded. (must be in c:\TEMP\)

https://www.dropbox.com/sh/zt8gbloxa9xe5m8/AACuZpIKIabQgNj8_nopTnxga?dl=0

The request is short and executed fairly quickly, so you have to go from a binder quickly and enter a data quickly in a cell of a blank binder to obtain the phenomenon (and somefosi repeat the maneuver several times), but with me it Crahs finally, and i don't see in these files what ui can cause this.

Thank you for any help or suggestion on this subject

Philippe Muniesa