Import File Memory Leak using QueryTables

[rbateman]

Problem: I have a Workbook containing two worksheets "X" and "FILES". The worksheet "FILES" contains the name of each file in a directory on my desktop "C:\Users\Owner\Desktop\DATA-X" in cells (A1:A1000) (There are 1000 files in the folder "DATA-X". The worksheet "X" is a blank worksheet.

The following code loops 1000 times, each time importing the "k"th file from the folder "DATA-X" and storing it in the worksheet "X". After each loop, the worksheet "X" gets replaced with the data in the next file from the folder "DATA-X". As the program is running, it can be seen in the task manager that memory is accumulating in the Excel program. In fact, it cannot finish because it will use up all memory by then (I get a message "OUT OF MEMORY"). Why is this memory leak occuring and how can I fix this? Any help would be greatful.

​

[COLOR=#0000ff]Sub[/COLOR] MemoryTest() 
     
    [COLOR=#0000ff]Dim[/COLOR] qt [COLOR=#0000ff]As[/COLOR] QueryTable 
    [COLOR=#0000ff]Dim[/COLOR] WSh [COLOR=#0000ff]As[/COLOR] Worksheet 
    Sheets("X").Select 
     
    [COLOR=#0000ff]For[/COLOR] k = 1 [COLOR=#0000ff]To[/COLOR] 1000 
         
        Cells.Select 
        Selection.Delete Shift:=xlUp 
         
        Range("A1").Select 
        [COLOR=#0000ff]With[/COLOR] ActiveSheet.QueryTables.Add(Connection:= _ 
            "TEXT;" & "C:\Users\Owner\Desktop\DATA-X" & "\" & Sheets("FILES").Cells(k, 1).Value _ 
            , Destination:=Range("$A$1")) 
            .Name = Sheets("FILES").Cells(k, 1) 
            .FieldNames = [COLOR=#0000ff]True[/COLOR] 
            .RowNumbers = [COLOR=#0000ff]False[/COLOR] 
            .FillAdjacentFormulas = [COLOR=#0000ff]False[/COLOR] 
            .PreserveFormatting = [COLOR=#0000ff]True[/COLOR] 
            .RefreshOnFileOpen = [COLOR=#0000ff]False[/COLOR] 
            .RefreshStyle = xlInsertDeleteCells 
            .SavePassword = [COLOR=#0000ff]False[/COLOR] 
            .SaveData = [COLOR=#0000ff]True[/COLOR] 
            .AdjustColumnWidth = [COLOR=#0000ff]True[/COLOR] 
            .RefreshPeriod = 0 
            .TextFilePromptOnRefresh = [COLOR=#0000ff]False[/COLOR] 
            .TextFilePlatform = 437 
            .TextFileStartRow = 1 
            .TextFileParseType = xlDelimited 
            .TextFileTextQualifier = xlTextQualifierDoubleQuote 
            .TextFileConsecutiveDelimiter = [COLOR=#0000ff]False[/COLOR] 
            .TextFileTabDelimiter = [COLOR=#0000ff]False[/COLOR] 
            .TextFileSemicolonDelimiter = [COLOR=#0000ff]False[/COLOR] 
            .TextFileCommaDelimiter = [COLOR=#0000ff]False[/COLOR] 
            .TextFileSpaceDelimiter = [COLOR=#0000ff]False[/COLOR] 
            .TextFileColumnDataTypes = Array(1) 
            .TextFileTrailingMinusNumbers = [COLOR=#0000ff]True[/COLOR] 
            .Refresh BackgroundQuery:=[COLOR=#0000ff]False[/COLOR] 
        [COLOR=#0000ff]End With[/COLOR] 
        Range("A1").Select 
        Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _ 
        FieldInfo:=Array(0, 1), TrailingMinusNumbers:=[COLOR=#0000ff]True[/COLOR] 
         
        [COLOR=#0000ff]For Each[/COLOR] WSh [COLOR=#0000ff]In[/COLOR] ThisWorkbook.Worksheets 
            [COLOR=#0000ff]For Each[/COLOR] qt [COLOR=#0000ff]In[/COLOR] WSh.QueryTables 
                qt.ResultRange.ClearContents 
                qt.Delete 
            [COLOR=#0000ff]Next[/COLOR] qt 
        [COLOR=#0000ff]Next[/COLOR] WSh 
         
        [COLOR=#0000ff]Do Until[/COLOR] ActiveWorkbook.Connections.Count = 0 
            ActiveWorkbook.Connections(ActiveWorkbook.Connections.Count).Delete 
        [COLOR=#0000ff]Loop[/COLOR] 
         
    [COLOR=#0000ff]Next[/COLOR] k 
     
[COLOR=#0000ff]End Sub[/COLOR]

 

[Chilli71]

I don't have a solution yet, but maybe I can narrow it down.
The method .add with the parameter connection appears to create a bunch of data.
But when you use the .delete method for the connection only the top layer information is cleared but the rest not. You will see that if you try to delete all data (excel 365) and excel asks if you want to delete the connections despite that you had deleted all connections already.
It is pretty clearly a bug an not a desired behavior.
Memory leak is one problem. My problem is that apparently excel wants to constantly check or refresh the connections. And you can't switch it off by by setting the formula refresh to manual. So once the numbers of created and deleted connections exceeds a couple of thousands excel gets completely unresponsive. So unresponsive that you can't even copy the data into an empty sheet of a workbook that does not have the problematic connection settings remains.
Btw. at excel 365 you can see the connections under Data->Queries and connections , if you switch the window panel that appears from queries to connections.
Funny thing is that if you manually delete the connection Excel appears to delete (almost) all data. At least it does not ask anymore if you want to delete the connection if you delete the data.
So since 15 years or more nobody at Microsoft did bother to reveal the internal model or to let us know which steps are necessary to cleanup the connection related data. And all proposals I found did not work.
Life of a PowerUser is rough