Excel CSV import via VBA - out of memory error

Diskutiere Excel CSV import via VBA - out of memory error im Office Forum im Bereich Microsoft Community Fragen; Hello, since beginning of December, I'm experiencing problems with VBA based combined HTML/CSV import to an Excel 2016 workbook. The code imports...
M

MSCom

Erfahrener Benutzer
Threadstarter
Mitglied seit
20.09.2016
Beiträge
92.365
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_XETR:DAI"
.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_XETR:DAI"
.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_XETR:DAI"
.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
 
#
Schau dir mal diesen Ratgeber an. Dort wird jeder fündig!
Registrieren bzw. einloggen, um diese und auch andere Anzeigen zu deaktivieren
Thema:

Excel CSV import via VBA - out of memory error

Excel CSV import via VBA - out of memory error - Ähnliche Themen

  • Import von CSV (Excel file) in Outlook 2013 , danach in jedem Fenster ein Simikolon ?

    Import von CSV (Excel file) in Outlook 2013 , danach in jedem Fenster ein Simikolon ?: Hallo erste einmal, habe ca. 2000 Kontakte zu Importieren, dafür habe ich eine Excel Tabelle so formatiert das sie von Outlook importiert...
  • Import von CSV (Excel file) in Outlook 2013 , danach in jedem Fenster ein Simikolon ?

    Import von CSV (Excel file) in Outlook 2013 , danach in jedem Fenster ein Simikolon ?: Hallo erste einmal, habe ca. 2000 Kontakte zu Importieren, dafür habe ich eine Excel Tabelle so formatiert das sie von Outlook importiert...
  • csv Datei in Excel öffnen

    csv Datei in Excel öffnen: Mein Problem ist folgendes - ich habe eine Datei, die als CSV-Datei erstellt wurde/wird und die in Excel geöffnet werden soll. Die meisten Spalten...
  • Import von Kontakten in Outlook (aus Excel bzw. CSV) funktioniert nicht (mehr)

    Import von Kontakten in Outlook (aus Excel bzw. CSV) funktioniert nicht (mehr): Hallo, früher gab es beim Import von Kontakten in Outlook eine explizite Auswahl für EXCEL-Format. Das gibt es nicht mehr und alle...
  • Excel Datenformat für csv - import

    Excel Datenformat für csv - import: Es ist ein immer wiederkehrendes Thema und ich finde die Lösung nicht: Auf einer Internetseite kann man etwas eingeben, speichern ... Ich kann...
  • Ähnliche Themen

    Oben