After 365 upgrade, VBA statement Sheet2.ListObjects(1).QueryTable.Refresh (False) crashes

Question

This report has been working normally until upgrading to MS365. I use MSQuery as I prefer using SQL with my reporting.

I suspected autosave is trashing the file and removed autosave and autorecovery with no change in results.

I have ran in safe mode with the same problems. There are six queries in the file, they all behave similarly.

I can run normal as long as I open the file without making a single change in any query, or change, save, exit Excel,restart.

After receiving the automation error, I can open MSquery and return results in MSQuery, but If I return to Exel, Excel stops working:

Amy ideas would be most helpful, these are automated reports, so all changes must be in VBA

Answer 1

Have you tried refreshing one query in a new workbook.

It looks multiple query caused excel crash which is an unexpected behivior (may be bug).

Answer 2

Hi Snow,

I have created a workbook with a single query, and I can update without crashing. These are all parameter queries using MS Query which is my preferred editor. I could setup Power Query, but I dont like the mashing of SQL, and I use Excel VBA to control my report, my preferred report writer for over three decades.

I should mention when I had two workbooks open, they both had multiple paramryrt queries from different Access databases.

Answer 3

Try remove all the settings of "Headers" and "footers".

Is it possible to call IT to rollback office update if the steps above not working?

Answer 4

Hi Snow Lu,

I have removed all headers and footers in the report, all queries execute as in all prior versions, I can run once, crashes Excel if ran 2nd time, or if more than one Excel workbook is open, crashing all open workbooks per Excel instance.

This report also has Forms, which have been removed, and custom Ribbon, which has also been removed, prior to removing headers and footers. All queries are parameter queries, which all parameters have been removed, also prior to headers-footers version.

I will be contacting IT as we have dozens of reports using the same technologies, Windows 10, Office 365, Excel 2019 MSO (16.0.10393.20026), MS Access database back end (multiple ACCDB databases), ODBC 32 bit drivers, MSQuery, 64 bit Excel.

We are using Access from MS Office Professional Plus 2010 Version 14.0.4760.1000 (32 Bit) due to problems with other county IT systems built on earlier versions of Access.

Thanks again for your support.

Happy Holidays

Answer 5

Hi Snow Lu,

The report I have been working with had 5 queries, each on a separate worksheet, I removed one query at a time and Excel consistently crashed with 4 and 3 queries. When I had only two queries, I changed both queries to parameter queries, added and removed numerous fields in both queries with no Excel crashes.

I opened a second workbook in the same Excel Instance with 3 queries and ran the report with two queries, and Excel crashed, both workbooks closed. I did have AutoRecover options set to Disable AutoRecover for this workbook only, but as I accepted Excel to restart, Auto-Recover ran, but did not destroy the workbook as stated above.