Memory leak caused by Power Query'd .xlsx file?

Question

I'm having an issue with a memory leak being caused by a particular file interacting with this simple block of code. The macro simply opens all .xlsx files in a particular directly, copies the sheets into my active workbook, then closes them. I've run this on a number of different .xlsx as well as .csv files with no issues whatsoever. However, when I run it on the pertinent data that needs to be processed, it results in my excel memory usage spiking to 4GB and then steadily climbing (clearly a leak of some sort).

Option Explicit

Sub Combine_Zoho_Gusto()

Dim Path As String
Dim Filename As String
Dim Sheet As Worksheet

Path = "C:\Users\XXXX\Desktop\Payroll Analysis" & "\"
Filename = Dir(Path & "*.xlsx")
Do While Filename <> ""
Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
    For Each Sheet In ActiveWorkbook.Sheets
        Sheet.Copy After:=ThisWorkbook.Sheets(1)
    Next Sheet
Workbooks(Filename).Close
Filename = Dir()
Loop

End Sub

The .xlsx file I'm trying to get this to run on is one that is using Power Query, which points to a folder which contains a simple .csv file. The Power Query simply filters and organizes this data into two columns of data with headers which it then displays on its sole Sheet1.

Has anyone encountered an issue like this, attempting to import sheets from a Power Query'd file, causing memory issues? I'm Quite new to VBA, so I'm not sure how else to diagnose this. The particular command that causes the memory to spike is:

Sheet.Copy After:=ThisWorkbook.Sheets(1)

Any and all help would be appreciated, thanks!

Update: I've been doing a bit more exploring of my data in the power query and I've noticed I even encounter this issue when trying to manually copy-paste the data from the power query to another excel file. If I mouse over Values it has no issue displaying the preview of the raw data, but as soon as I mouse over any of the other functions, it immediately spikes my memory to nearly 8GB usage.

[Copy-Paste Dialoge][1] [1]: https://i.stack.imgur.com/mjZLX.png

Cloax · Answer

I believe I figured out the issue, although I'm not sure about the details: It appears that, after running my macro dozens of times while testing my code, I was importing the same power query and all of it's connections to my workbook over and over again. I would delete the sheet I imported each time, but not the queries/connections. I'm not exactly sure how these connections work, but I presume they were all looking at and checking the destination folder for updates. I checked and realized I had nearly 60 of these queries/connections in my workbook that would presumably get triggered every time I copy-pasted the power query sheet into my workbook. Simply deleting all of these excess queries/connections resolved my issue.

If anyone knows the specifics about this process maybe clarify more in a response so future users who possibly encounter this issue will be able to resolve it.

Memory leak caused by Power Query'd .xlsx file?

1 Answers