Excel CSV import via VBA - out of memory error

Hello,


since beginning of December, I'm experiencing problems with VBA based combined HTML/CSV import to an Excel 2016 workbook. The code imports one text-only HTML file and six CSV files from the internet by querytables per loop. The overall size of these files is less than 30 kB. This used to return "runtime error '7': not enough memory." after 50+x loops, which was not perfect for me, but acceptable. Since exactly Dec 4th, the script cannot even complete ONE loop.


This problem seems to have been described many times before, but none of the workarounds I found on Microsoft or other sites helped. To describe the adjustments I already tried:


environment:

- running the script on Excel 2016 64 and 32 bit versions on Windows 10

- running it on different workstations, with 2 (Win 10), 8 and 32 GB RAM (both Win 7)

- installing the Large Adress Aware Update, which by notification of the Office updater already seems to be part of Excel 2016

- adjusting/enlarging the parameter SharedSection in the registry ("HKEY_LOCAL_MACHINE\System\CurrentControlSet\Control\Session Manager\SubSystems")


code:

- clearing the clipboard after every import (Application.CutCopyMode = False) - that means, after every single imported file, 7 times per loop

- saving the file after every import

- trying to reduce the size of the worksheet that the files were originally imported to, by saving them to an array and clearing the worksheet afterwards

- running the script from outside the workbook to be able to close and reopen it after every import, hoping to clear the memory by this.

- all of the above combined.


Apart from the points mentioned above and of course "installing the latest update" or "running the troubleshoot manager", I would really appreciate every idea and promise to try it and give feedback what works. The original code that worked until Dec 3rd can be found below.


Thank you and best regards!


Sub Import_Master()
Dim Link_P As String
Dim Link_IS As String
Dim Link_BS As String
Dim Link_CF As String
Dim Link_KR As String
Dim Link_MC As String
Dim Stocks_no As Single
Dim i, j, k, m, n As Single

Stocks_no = Sheets("Ticker").Cells(1, 1).Value
j = Worksheets("Evaluation").Cells(1, 1).Value
k = (j - 1) * 50
Worksheets("CRD").Cells.ClearContents
n = Application.WorksheetFunction.Min(50, Stocks_no - k)

For i = 1 To n

m = k + i

Link_P = Sheets("Ticker").Cells(m + 1, 2).Value
Link_IS = Sheets("Ticker").Cells(m + 1, 3).Value
Link_BS = Sheets("Ticker").Cells(m + 1, 4).Value
Link_CF = Sheets("Ticker").Cells(m + 1, 5).Value
Link_KR = Sheets("Ticker").Cells(m + 1, 6).Value
Link_MC = Sheets("Ticker").Cells(m + 1, 7).Value
Link_CP = Sheets("Ticker").Cells(m + 1, 8).Value

With Worksheets("CRD").QueryTables.Add(Connection:= _
Link_P, Destination:=Worksheets("CRD").Cells(1, 12 * i - 11))
.Name = "ISU_2"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = True
.WebDisableRedirections = True
.Refresh BackgroundQuery:=False
End With

With Worksheets("CRD").QueryTables.Add(Connection:= _
Link_IS _
, Destination:=Worksheets("CRD").Cells(20, 12 * i - 11))
.Name = "IS_XETRAI"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1)
.TextFileDecimalSeparator = "."
.TextFileThousandsSeparator = ","
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

With Worksheets("CRD").QueryTables.Add(Connection:= _
Link_BS _
, Destination:=Worksheets("CRD").Cells(70, 12 * i - 11))
.Name = "BS_XETRAI"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1)
.TextFileDecimalSeparator = "."
.TextFileThousandsSeparator = ","
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

With Worksheets("CRD").QueryTables.Add(Connection:= _
Link_CF _
, Destination:=Worksheets("CRD").Cells(130, 12 * i - 11))
.Name = "CF_XETRAI"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1)
.TextFileDecimalSeparator = "."
.TextFileThousandsSeparator = ","
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

With Worksheets("CRD").QueryTables.Add(Connection:= _
Link_KR _
, Destination:=Worksheets("CRD").Cells(180, 12 * i - 11))
.Name = "KR_XETRDAI"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1)
.TextFileDecimalSeparator = "."
.TextFileThousandsSeparator = ","
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

With Worksheets("CRD").QueryTables.Add(Connection:= _
Link_MC _
, Destination:=Worksheets("CRD").Cells(300, 12 * i - 11))
.Name = "MC_XETRDAI"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1)
.TextFileDecimalSeparator = "."
.TextFileThousandsSeparator = ","
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With

Next

With Worksheets("CRD")
For i = .QueryTables.Count To 1 Step -1
.QueryTables(i).Delete
Next
End With

End Sub