MS Excel uses 64 Gigs of Memory

[Mortaliton]

Good Afternoon

 

I have a issue with Microsoft Excel 2021 64 bit. I cannot link my files due to personal keys linked to my API's so I will do my best to explain the situation. Basically Excel is using 64 gigs of RAM and keeping it maxed for anywhere between 20 minutes to a hour.

 

I have12 Excel .xlsx files. I have 1 main sheet the uses VLOOKUP/XLOOKUP to pull from the other 11 sheets. 1 sheet contains only my formula's, it helps to keep my other sheets clean. Once its updated I close it out so it does not have to be open. The other 10 excel sheets contain a few hundred API calls to a server using the DATA tab, from web, inserting the html address, and getting inserted into the sheet in a table. The 10 sheets with the API calls once updated are closed so I will only have my main sheet open. Also when updating my 10 sheets with the API calls, they are open only one at a time.

 

The Main sheet and the Formula sheet have 0 issues when there open and being used as intended. The issue is the 10 sheets with the API calls. All sheets have the same issue. Although it is random and does not always occur in the same spot. I separate all my Queries and Connections into folders with 25 API calls each. Each folder having a Macro to refresh them, and a main macro to refresh all of them sequentially. I tried this to see if the problem was caused by refreshing 350 connections at the same time. I will have that macro below.

 

I can manually refresh each folder and sometimes its smooth. It stays about idle RAM usage at 4-6 gigs. Other times it creeps up to 64 gigs maxed steadily and stays there for 20 minutes to 1 hour. Sometimes jumping down to 40 gigs used and going back up. Even after the refresh of the 25 API calls is complete the RAM is still maxed at 64 gigs for a very long time. 20-30 minutes. Sometimes I can use the main macro and everything refreshes just fine with no RAM jump. It might peek at 10 gigs, but this is rare. The macros all work as intended and everything will refresh. On one of my sheets that has 250 queries it can take up to a hour to complete the refresh because the RAM maxes out. When this happens a full sheet refresh will take up to a hour for 350 refreshes.

 

Random thing I noticed. even though its stored on a M.2 it would max my HDD for storage at 100%? I unhooked it and now my M.2 gets hit but nothing as significant. like a couple %.

 

I have googled until my eyes are sore and I am probably just not using the particular keywords to find my solution. Does anyone have any idea why my sheets use so much data when I have no operations being processed? It starts when I refresh my queries but it continues to stay maxed after it is completed. Any help or Ideas is appreciated.

 

Thank You in advance.

 

I have tried

-Turning off background downloads

-Turning off automatic cell formula refresh

-Refreshing the queries in groups of 25 instead of all of them at the same time.

-reinstalling excel

-restarting the computer

-Turning off Type detection

-Clearing the cache

 

 

(Main macro refresh all)

Sub Refresh()
     Refresh1
     Refresh2
     Refresh3
     Refresh4
     Refresh5
     Refresh6
     Refresh7
     Refresh8
     Refresh9
     Refresh10
     Refresh11
     Refresh12
     Refresh13
     Refresh14
End Sub

 

 

Sub Refresh1()
'
' Refresh1 Macro
'

'
    ActiveWorkbook.Connections("Query - TAIT").Refresh
    ActiveWorkbook.Connections("Query - TAOP").Refresh
    ActiveWorkbook.Connections("Query - TAP").Refresh
    ActiveWorkbook.Connections("Query - TBLT").Refresh
    ActiveWorkbook.Connections("Query - TC").Refresh
    ActiveWorkbook.Connections("Query - TCN").Refresh
    ActiveWorkbook.Connections("Query - TEO").Refresh
    ActiveWorkbook.Connections("Query - TER").Refresh
    ActiveWorkbook.Connections("Query - TJX").Refresh
    ActiveWorkbook.Connections("Query - TKC").Refresh
    ActiveWorkbook.Connections("Query - TRI").Refresh
    ActiveWorkbook.Connections("Query - TRKA").Refresh
    ActiveWorkbook.Connections("Query - TRNS").Refresh
    ActiveWorkbook.Connections("Query - TSEM").Refresh
    ActiveWorkbook.Connections("Query - TTCF").Refresh
    ActiveWorkbook.Connections("Query - TTMI").Refresh
    ActiveWorkbook.Connections("Query - TXRH").Refresh
    ActiveWorkbook.Connections("Query - UBCP").Refresh
    ActiveWorkbook.Connections("Query - UEPS").Refresh
    ActiveWorkbook.Connections("Query - ULTA").Refresh
    ActiveWorkbook.Connections("Query - UMC").Refresh
    ActiveWorkbook.Connections("Query - URBN").Refresh
    ActiveWorkbook.Connections("Query - VC").Refresh
    ActiveWorkbook.Connections("Query - VCR").Refresh
    ActiveWorkbook.Connections("Query - VHC").Refresh
End Sub

 

 

(Seperate Refreshes)

Sub Refresh2()
'
' Refresh2 Macro
'

'
    ActiveWorkbook.Connections("Query - TRI DIV/YIELD").Refresh
    ActiveWorkbook.Connections("Query - TXRH DIV/YIELD").Refresh
    ActiveWorkbook.Connections("Query - UBCP DIV/YIELD").Refresh
    ActiveWorkbook.Connections("Query - UMC DIV/YIELD").Refresh
    ActiveWorkbook.Connections("Query - VCR DIV/YIELD").Refresh
    ActiveWorkbook.Connections("Query - VHC DIV/YIELD").Refresh
    ActiveWorkbook.Connections("Query - VSH DIV/YIELD").Refresh
    ActiveWorkbook.Connections("Query - WIT DIV/YIELD").Refresh
    ActiveWorkbook.Connections("Query - WSTG DIV/YIELD").Refresh
    ActiveWorkbook.Connections("Query - YUM DIV/YIELD").Refresh
    ActiveWorkbook.Connections("Query - WTBDY DIV/YIELD").Refresh
    ActiveWorkbook.Connections("Query - SILC HIS").Refresh
    ActiveWorkbook.Connections("Query - SIMO HIS").Refresh
    ActiveWorkbook.Connections("Query - SISI HIS").Refresh
    ActiveWorkbook.Connections("Query - SLAB HIS").Refresh
    ActiveWorkbook.Connections("Query - SMCI HIS").Refresh
    ActiveWorkbook.Connections("Query - SMGZY HIS").Refresh
    ActiveWorkbook.Connections("Query - SMTC HIS").Refresh
    ActiveWorkbook.Connections("Query - SNTG HIS").Refresh
    ActiveWorkbook.Connections("Query - SOFO HIS").Refresh
    ActiveWorkbook.Connections("Query - SOMLY HIS").Refresh
    ActiveWorkbook.Connections("Query - SPKKY HIS").Refresh
    ActiveWorkbook.Connections("Query - SRAD HIS").Refresh
    ActiveWorkbook.Connections("Query - SRI HIS").Refresh
    ActiveWorkbook.Connections("Query - SRT HIS").Refresh
End Sub

 

 

Ryzen 5900X

64 Gigs 3200 Memory

X570 Meg Ace 

1080ti

1TB Samsung EVO M.2

[kirashi]

2 hours ago, Mortaliton said:

Does anyone have any idea why my sheets use so much data when I have no operations being processed?

Most likely because Excel is built on a codebase from 20+ years ago that contains amazing functionality alongside swathes of intermittent memory leak issues. Don't get me wrong; it's great that Excel is capable of doing a ton of number crunching through a relatively easy to learn and accessible interface, but that's also what makes it terrible because people then use Excel for things it's not supposed to be used for.

 

Based on how many sheets and API calls your situation appears to involve, I'd recommend looking into using something like Microsoft Power BI or some other hosted / server-based solution rather than attempting to troubleshoot an intermittent Excel memory leak, especially if data integrity is critical to the situation.

 

https://www.amite.co.uk/blogs/excel-hell-why-to-stop-using-spreadsheets-for-everything

http://www.eusprig.org/horror-stories.htm

[Mortaliton]

First off, Thank you for your response. I have been working with Power Bi Desktop but I am still learning. I cannot get it to import the data in the way I want on the scale I want. I will continue working with it and possibly learn how to utilize a database if it works for what I need it to. I do run a server so the end result is all my files are hosted on that machine with the main file opening on my daily use Desktop. I would like to load the data back into excel as individual tables ideally. Importing my sheet links all API calls to teh spreadsheet itself. I will manually have to make a test file with 300 calls to the server and continue testing.

 

Although, through even more research and far more wasted time for failed results. I think I found a solution. I will list my VBA and what I have done to find this resolution. Just to re iterate I have on my multiple sheets between 250-350 API calls roughly and I will be adding, although I will make more sheets as well if this does not scale well. Currently I can refresh all Get Data/Web/.json queries in 15 minutes for 350 queries. My computer ranges between 25-40 Gigs of RAM. Although it does not release the RAM when it is done so its perpetually staying there. If I refresh again it stays as well and does not increase in usage. My use will be closing each file and going to the next so that will release all RAM on application exit as well.

 

CPU usage on the otherhand goes back to normal on Macro complete, I idle at 1% when finished instead of the 45%+.

 

1 - Separate all my queries into folders that contain 25 queries each

2 - Use VBA to refresh all data, I will post below as well.

 

Quick note, Data in numerical vs table format will glitch out on refresh all, keeping numerical data in its own separate folder and using VBA to refresh all data is a work around for me.

 

3 - I leave my data queries as basic as possible. I use to convert them into a table in power query. Now I leave them as they come in from source. Numerical data or List Data from a .json. Manually going to Data & Connections and using "Load To" from there, even though its still loading to a table does not obliterate my memory. This is a extra step to load onto a sheet but it is working.

4 - I have formulas in the options menu on manual ( Might not be necessary )

5 - Fast Data load is on

 

Main VBA Refresh

 

Sub Refresh()
     Refresh1
     Refresh2
     Refresh3
     Refresh4
     Refresh5
     Refresh6
     Refresh7
     Refresh8
     Refresh9
     Refresh10
     Refresh11
     Refresh12
     Refresh13
     Refresh14
     DoEvents
End Sub

 

 

Individual referenced Refresh Macro

 

Sub Refresh1()
'
' Refresh1 Macro
'

'
    ActiveWorkbook.Connections("Query - JAKK").Refresh
    ActiveWorkbook.Connections("Query - J").Refresh
    ActiveWorkbook.Connections("Query - IRT").Refresh
    ActiveWorkbook.Connections("Query - IRS").Refresh
    ActiveWorkbook.Connections("Query - IREN").Refresh
    ActiveWorkbook.Connections("Query - IRDM").Refresh
    ActiveWorkbook.Connections("Query - IPWR").Refresh
    ActiveWorkbook.Connections("Query - IPGP").Refresh
    ActiveWorkbook.Connections("Query - IPG").Refresh
    ActiveWorkbook.Connections("Query - IPDN").Refresh
    ActiveWorkbook.Connections("Query - INTG").Refresh
    ActiveWorkbook.Connections("Query - INFY").Refresh
    ActiveWorkbook.Connections("Query - INFN").Refresh
    ActiveWorkbook.Connections("Query - IMTE").Refresh
    ActiveWorkbook.Connections("Query - IMOS").Refresh
    ActiveWorkbook.Connections("Query - IHT").Refresh
    ActiveWorkbook.Connections("Query - IFBD").Refresh
    ActiveWorkbook.Connections("Query - IDBA").Refresh
    ActiveWorkbook.Connections("Query - ICLK").Refresh
    ActiveWorkbook.Connections("Query - HVT-A").Refresh
    ActiveWorkbook.Connections("Query - HVT").Refresh
    ActiveWorkbook.Connections("Query - HSII").Refresh
    ActiveWorkbook.Connections("Query - HOFT").Refresh
    ActiveWorkbook.Connections("Query - HOCPY").Refresh
    ActiveWorkbook.Connections("Query - HFBL").Refresh
End Sub