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 one text-only HTML ...


+ Antworten + Neues Thema erstellen
Ergebnis 1 bis 1 von 1
  1. #1

    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\Cont rol\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

  2. Schau dir mal diesen Ratgeber an. Dort wird jeder fündig!

    Registrieren bzw. einloggen, um diese und auch andere Anzeigen zu deaktivieren

Excel CSV import via VBA - out of memory error

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


  1. Error nach Update of Windows Creators - EXCUTED OF ATTEMPT NOEXCECUTIV MEMORY: Ich hab heute 3 Computer auf Windows 10 Creators upgedated. Seitdem stürzen die Rechner nach einer Weile immer mit der Meldung ATTEMPTED EXCUTE OF...



  2. Import von .csv-Datei nicht möglich - Angeblich unkorrektes Format: Hallo zusammen, ich bin vom Administrator der Windows Phone-Fraktion hierher verwiesen worden. Problem: Ich kann eine von EXcel 2010 erzeute...



  3. CSV-Import von Gmail: Geburtstage falsches Format: Hallo zusammen, ich habe meine Kontakte von GMail mit der entsprechenden Funktion als CSV exportiert. Beim Import zu Outlook.com werden die...



  4. CSV Übernahme in Excel: Seit Windows 10 und Office 365 wird beim Ausdruck der Bankdaten als CSV-Format zwar Excel geöffnet, das Blatt ist aber komplett leer?



  5. 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 das...