Excel For Mac: QueryTable.Refresh huge memory leak eats 60GB of memory in a few seconds

Question

I have been plodding through my code which runs fine on Excel 2016 fo PC, reworking for Mac compatibility. 

Right now I have this one which I am stumped with: 

When I issue a QueryTable.Refresh on a TableQuery, Excel starts gobbling up memory nonstop, and in less than a minute reaches 60GB RAM consumption, at which point macOS kills it. 

The Query which populates this Table references other tables in the Excel file. 

Anyone else also getting this kind of error?

Answer 1

Hi

This is a user-to-user forum. On occasion Microsoft employees visit here and sometimes participate, but that is not generally the case.

You didn't say what version and build of Excel you are using. You can determine this by going to the Excel menu and choosing About Excel. My reply is based on Excel version 15.27 build 160920.

If you right-click in the querytable and choose Refresh from the pop-up menu, this message displays:

Unfortunately, when you attempt to use code to get around this, Excel just goes off to la-la land, as you observed.

You should be able to generate a querytable result set from another worksheet within a workbook using the Actual Technologies Access ODBC driver. The caveat is that you can not refresh the querytable once you make it. Your code would have to generate a new querytable each time.

If you want to beg for support for the querytable connection you are currently using, you can do it here:

https://outlook.uservoice.com/forums/293343-outlook-for-mac

Answer 2

Still no reply.

Does any Microsoft developer read these forums?

Is there any way to get their attention at all?

I have an Office 365 E3 subscription and they stonewalled me telling I had to get support from the community, and closed my case.

Answer 3

Sample document with reduced case:

Works fine on Windows.

Excel stops responding, eats 60GB of RAM on Mac in around a minute, Mac asks user to kill the app. 

Just check the VBA code inside, it's very small.

Link to file: https://drive.google.com/open?id=0B_5XrYwti51tSFBsMjBTUURSdjQ

Answer 4

I said in the OP, it's Office 2016.

It's the O365 one, updated to the latest version as of today (21/09/2016). 

More specifically, version 15.26 (160910) on Mac.

Answer 5

Hi Tiago,

To isolate your concern, we would like to know which version of Excel for Mac are you using?

Regards.