Excel zieht sich Hauptspeicher, gibt aber nicht mehr frei

[daPanic]

Hallöchen zusammen,

ich habe ein spannendes Problem welchem ich versuche Herr zu werden.
Ich habe eine Datei für meine Firma geschrieben in der ich zunächst eine CSV Datei importiere (ca. 2000 Zeilen).

Die CSV ist in etwa so aufgebaut:
TEAM A > Name A > Abwesend
TEAM A > Name B > Abwesend

Nur noch etwas mehr Optionen. Da mir z.B. Name A und Name B egal sind und ich nur auf das "TEAM" habe ich mir zunächst eine Routine gebaut die daraus eine Tabelle erstellt welche die Daten in der für mich guten Ansicht aufbereitet.
Also aus daraus wird bei mir "TEAM A" > Teilneher A, B, C und D > 2 Abwesend
Meine Tabelle hat dann "nur" ca. 700 Zeilen.

Soweit, sogut.
Jetzt werden täglich mehrere Änderungen durchgeführt und ich ziehe mir mehrfach täglich eine neue CSV.
Die CSV wird automatisch aktualisiert und ich möchte dann ein Update durchführen.

Ich gehe meine Ansicht von oben nach unten durch (Schleife mit 700 Durchäufen - SCHLEIFE A) und prüfe für jeden Eintrag in meiner Liste ob und welche Daten in der CSV enthalten sind (SCHLEIFE B)
Dafür gehe ich den CSV Import solange durch bis ich das erste Auffinden der relevanten Daten gefunden habe, mache ein Update und laufe mit dem nächsten Durchlauf von SCHLEIFE A weiter.

Spannend ist seit neustem, vermutlich das Upgrade auf O365, dass er sich ca. 2GB Hauptspeicher reinzieht.
Ich hätte gedacht, damit er alles durchlaufen hat (ca.  20 Sekunden Dauer) dass er dann alles wieder leer und den Speicher löscht. Macht er aber nciht.

Ich habe angefangen manche Variablen zu "nothingen" wie z.b. referenzierte Worksheets, aber ohne Erfolg.

Nun die Idee, dass ich eventuell meine Abfrage anders aufbauen muss um die Anzahl der Schleifendurchläufe zu minimieren.

Meine Ideen:
Ideen 1:
Ich kopiere das Datenblatt des CSV Imports in ein Puffersheet. Dieses gehe ich durch und durchsuche meine Daten und nach jedem Fund lösche ich die Zeile.
So wird die Anzahl der Zeilen von Durchlauf zu Durchlauf immer kleiner.

Idee 2: 
Ich generiere mir einen Unique Key aus den Daten die ich in der CSV Prüfe (Parameter 1, Parameter 2, Parameter 3 werden als Unique Key konkateniert).
Und dann prüfen ich lediglich immer nur ob die Daten in einem Feld statt in 3 oder 4 Feldern stimmig sind.

Was sagt ihr dazu?

[derHoepp]

Moin,

Was sagt ihr dazu?

42

Viele Grüße 
derHoepp

[volti]

Hallo,
da sich noch kaum jemand gemeldet hat, gehe ich davon aus, dass auch die anderen nicht so ganz schlau werden aus Deinem Ansinnen.

Zunächst einmal, 2.000 Zeilen, das ist nichts. Das und auch die Variablen im Code wie auch die Anzahl der Schleifendurchgänge haben m.E. so gut wie keinen Einfluss auf den Arbeitsspeicher.

PS: 'Nothingen': Gehört zum guten Programmierstil bei externen Objekten und Zeigern.
Mache ich oft aber auch nicht, denn soweit ich weiß werden die am Ende der Sub von Windows gelöscht. Dazu gibt es aber unterschiedliche Meinungen im Netz.

Ich würde die CSV-Datei einfach per VBA in ein Array laden und dann in einer Schleife aus diesem Array via .Match die Zieldatei aufbauen oder aktualisieren, falls alte Datensätze erhalten bleiben müssen.
Meine Einschätzung: 2 Sekunden.

Aber das hängt natürlich von den Daten, der Programmierung und den evtl. noch speziellen Anforderungen ab.

Du solltest mal zwei Musterdateien (Ziel-XLS und CSV) hier bereitstellen...

Gruß
Karl-Heinz

[ralf_b]

brauchst du die 2 GB Speicher für andere Sachen oder wo liegt das Problem?

[Cadmus]

Wäre es nicht einfacher die Daten per PowerQuery zu vergleichen und zusammenzuführen, statt 700 Durchläufe zu machen?

https://excelhero.de/power-query/power-q...ren-joins/

[Der Steuerfuzzi]

Hallo,

Nun die Idee, dass ich eventuell meine Abfrage anders aufbauen muss um die Anzahl der Schleifendurchläufe zu minimieren.

Ohne deinen Code zu kennen, wird es schwierig hier Empfehlungen zu geben oder Lösungen zu finden.

Ideen 1:
Ich kopiere das Datenblatt des CSV Imports in ein Puffersheet. Dieses gehe ich durch und durchsuche meine Daten und nach jedem Fund lösche ich die Zeile.

Zellen in einem Sheet abzuarbeiten und löschen von Zeilen ist sehr zeitaufwändig. Ich würde daher davon abraten, denn mE wird das Makro dadurch unheimlich träge.

Idee 2: 
Ich generiere mir einen Unique Key aus den Daten die ich in der CSV Prüfe (Parameter 1, Parameter 2, Parameter 3 werden als Unique Key konkateniert).
Und dann prüfen ich lediglich immer nur ob die Daten in einem Feld statt in 3 oder 4 Feldern stimmig sind.

Bahnhof ... keine Ahnung was Du damit genau meinst.

Ohne die Daten und Deinen Code zu kennen wird es mit der Hilfe ziemlich schwierig ...

Aber wenn ich Dich richtig verstanden habe, stehen in der csv immer alle Daten, so dass Du gar nicht "updaten" musst, oder?

Der Hinweis auf PowerQuery kam schon, wäre sicherlich eine Alternative. Und das Einlesen einer csv per VBA sollte aber auch problemlos möglich sein, auch mehrmals.

[volti]

Hallo,

hier mal ein Ansatz für eine VBA Lösung, so wie ich es verstanden habe.
Report wird hier immer wieder neu aufgebaut.

Code:

Sub Import()
  Dim sDateiname As String, sData As String
  Dim i As Long, iOutZl As Long, iGefunden As Long
  Dim sArr() As String, sArrDat() As String, sTln As String
  Dim WSh As Worksheet

  sDateiname = ThisWorkbook.Path & "\Testdaten.csv" ' <<< anpassen >>>
  Set WSh = ThisWorkbook.Sheets("Report")           ' <<< anpassen >>>

' Datei öffnen und einlesen
  i = FreeFile()
  Open sDateiname For Binary Access Read As i
  sData = Space$(LOF(i))
  Get i, , sData
  Close i

' Umlaute ersetzen  (Optional)
  sArr = Split(",'€,€,Ä,Ä,ä,ä,&Ounml;,Ö,ö,ö,Uuml;,Ü,ü,ü,ÿ,ÿ,ß,ß", ",")
  For i = 1 To UBound(sArr) Step 2
      sData = Replace(sData, sArr(i), sArr(i + 1))
  Next i
  
  iOutZl = 1
  sArr = Split(sData, vbCrLf)
  Application.ScreenUpdating = False
  WSh.Cells.ClearContents
  
  For i = 0 To UBound(sArr)
      sArrDat = Split(sArr(i), ";")
      On Error Resume Next
      iGefunden = iOutZl
      iGefunden = Application.WorksheetFunction.Match(sArrDat(0), WSh.Range("A:A"), 0)
      With WSh.Cells(iGefunden, "A")
          If iGefunden = iOutZl Then
             .Value = sArrDat(0)
             iOutZl = iOutZl + 1
          End If
          sTln = .Offset(, 1).Value
          .Offset(, 1).Value = sTln & IIf(sTln <> "", ", ", "") & sArrDat(1)
          .Offset(, 2).Value = .Offset(, 2).Value + IIf(sArrDat(2) = "ja", 1, 0)
      End With
  Next i
  
  Application.ScreenUpdating = True
  MsgBox "Fertig"
End Sub

_________
viele Grüße
Karl-Heinz

[schauan]

Hallöchen,

Excel gibt z.B. bei Kopieraktionen nicht den ganzen dazu benötigten Speicher frei sondern behält einen kleinen Rest im Hauptspeicher. Das summiert sich mit der Zeit. Ich hatte das z.B. beim Kopieren eines Zellbereiches A1:R62 mit anschließendem Einfügen in ein Chartobjekt zwecks Ausgabe als Grafik. Dabei habe ich über die Jahre festgestellt, dass fast mit jeder neuen Office-Version die Anzahl de Kopiervorgänge bis zum Crash geringer wurde. Unter 97/WNT waren das um die 500, unter 2010/W10 z.B. nur noch ca. 150