Looped .Refresh causing slow Excel

Question

I am trying to use VBA in Excel for Mac to query a database. A simple query works fine and I've been using it for years. Now I'm getting into more complex queries, where the results of one query (about 1,000 records) are used sequentially to query the database again (so about 1,000 consecutive queries). The results (about 5,000 records) return in about 2.5 minutes, but clicking in the Excel sheet is not responsive for about a minute afterward. This behavior continues for another 2 minutes or so before clicking becomes pretty much instantaneous. Running the macro again gives similar results but slower. The 3rd time is even slower. I suspect a memory leak. Restarting Excel makes the problem reset. Here is the code for the actual query:

    With ActiveSheet.QueryTables.Add(Connection:=strURL, Destination:=Range(strStartCell))
        .PostText = "user=" & strUserName & ";password=" & strUserPassword
        .RefreshStyle = xlOverwriteCells
        .SaveData = True
        .BackgroundQuery = False
        .Refresh
    End With

I've tried to actually send just the first query (so it gets data from which the other queries can be built). The rest of them, I build the query, but don't send it. So, in this experiment, the above code got used only once. Running it this way, it comes back in about 8 seconds. So the other 2 minutes and 20+ seconds are back and forth between my computer and the database. More importantly, after running this way, there is no lag after it is done running. So it seems like if it is a memory leak, the leak is in the query process, or maybe the actual writing of the data.

I have programmatically turned off all of the screen updating, page break showing, and calculating at the beginning and returned them to the original settings at the end.

My computer is a Mac mini (Late 2014) 3GHz Intel Core i7 with Office 2011, but I've tried running it on a newer M1 with the newest version of Excel also. It was much faster, but the lag after the results were returned, though shorter, was still a problem. My computer is representative of where the spreadsheet will be run for the near future.

The lag afterwards is really going to kill this part of the project. Has anybody seen this problem before? Is there something I can do to trace what is causing the problem and if there is a way around it?

Answer 1

ChatGPT solved the crux of my issue. It pointed out that I'm creating a separate Query Table for each of my 1000+ queries and those are eating up my memory, causing my application to become slow. It said that if I add a .Delete line after the With... End With block it would wipe out the previous Query Table and not use up all that memory. The answer was slightly inaccurate. The .Delete needed to go inside the block (at the end). Anyhow, I tried that and it made my code somewhat slower, but when it was done, the application was not slow. That is a win in my book.

It further suggested that ADO might do a better job at this task. So I guess that is something else to learn. Thought this might be useful for someone else experiencing a similar issue.

another edit: I'm working on a Mac, so ADO will not do the trick for me.

Here was the working query:

With ActiveSheet.QueryTables.Add(Connection:=strURL, Destination:=Range(strStartCell))
        .PostText = "user=" & strUserName & ";password=" & strUserPassword
        .RefreshStyle = xlOverwriteCells
        .SaveData = True
        .BackgroundQuery = False
        .Refresh
        .Delete
End With