Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1016to1020
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Kontoumsätze einlesen

Kontoumsätze einlesen
17.10.2008 16:34:15
Mark
Hallo,
ich möchte die Kontoumsätze, die ich bei der Bank als csv herunterladen kann, in einer Tabelle zusammenfassen.
Ich such dazu ein Modul, welches alle *.csv eines Ordners öffnet, die Inhalte einliest und am Ende anhängt, und die doppelten Datensätze löscht.
Gibt es sowas schon halbwegs fertig als Beispiel herunterzuladen?
Grüße
Mark

16
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Kontoumsätze einlesen
18.10.2008 12:01:00
Tino
Hallo,
habe mal ein Beispiel aufgebaut und an meinen Bankdaten getestet.
Deine Bankdaten bzw. den Aufbau kenne ich nicht, daher kann es sein, dass Du einige Anpassungen vornehmen musst.
Kommentare stehen im Code.
Modul Modul1
Option Explicit 
Dim TempTabelle As Worksheet 
 
Sub BankDatenLesen() 
Dim strFile As String 
Const strPfad As String = "C:\Kontoumsätze\" 'Pfad 
With Application 
 .ScreenUpdating = False 
 .DisplayAlerts = False 
    'Erstelle TempTabelle 
    Set TempTabelle = Sheets.Add 
    'kopiere vorhandene Daten in Temptabelle 
    'damit bleibt auch die Überschrift erhalten 
    Sheets("BankDaten").UsedRange.Copy TempTabelle.Range("A1") 
     strFile = Dir$(strPfad & "*.csv") 
      
    Do Until strFile = "" 'Schleife bis keine Datei mehr vorhanden 
       
     txt_ReadLine (strPfad & strFile) 'Pfad und Dateiname 
      
     strFile = Dir$ 'nächste Datei suchen 
    Loop 
    Call Aufräumen 
  'Lösche Temptabelle 
  TempTabelle.Delete 
  .DisplayAlerts = True 
  .ScreenUpdating = True 
End With 
 
Set TempTabelle = Nothing 
End Sub 
 
'Sub zum lesen der Bankdaten ohne die Überschrift. 
Public Sub txt_ReadLine(ByVal sFilename As String) 
  Dim F As Integer 
  Dim sLine As String 
  Dim sTemp As Variant 
  Dim lRow As Long 
 
  lRow = 0 
  ' Existiert die Datei ? 
  If Dir$(sFilename) <> "" Then 
   
    ' Datei zum Lesen öffnen 
    F = FreeFile 
    Open sFilename For Input As #F 
 
    While Not EOF(F) 
      lRow = lRow + 1 
      Line Input #F, sLine 
       If lRow > 1 Then 
        sLine = Replace(sLine, """", "") 
        sTemp = Split(sLine, ";") 
        With TempTabelle 
        .Range(.Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0), .Cells(.Rows.Count, "A").End(xlUp).Offset(1, Ubound(sTemp))) _
        = Split(sLine, ";") 
        End With 
       End If 
    Wend 
    Close #F 
  End If 
End Sub 
Sub Aufräumen() 
    Dim Zellen As Range 
    Sheets("Bankdaten").Cells.Clear 
    With TempTabelle 
     'mit Spezialfilter doppelte ausfiltern 
     .Range("A1", .Cells.SpecialCells(xlCellTypeLastCell)).AdvancedFilter Action:=xlFilterInPlace, Unique:=True 
     'Sichtbare Zellen bestimmen 
    Set Zellen = .UsedRange.SpecialCells(xlCellTypeVisible) 
    End With 
    'Sichtbare Zellen in Tabelle Bankdaten kopieren 
    Zellen.Copy 
    Sheets("Bankdaten").Range("A1").PasteSpecial 
Set Zellen = Nothing 
End Sub 
 


Gruß Tino

www.VBA-Excel.de


Anzeige
Kontoumsätze einlesen
18.10.2008 14:22:00
Mark
Hallo Tino,
erstmal vielen Dank, dass du dir soviel Mühe gemacht hast und etwas "neu" entworfen hast.
Ich hatte mir schon aus den Beispielen ein paar Brocken heraus gesucht, um alle *.CSV eines Ordners zu öffnen und um Tabellen zu vergleichen und die Differenzen zu übernehmen.
Letzteres sieht bei dir viel effizienter aus. Ich hätte da nun ganz aufwendige Schleifen gebastelt, durch die ich dann irgendwann selbst nicht mehr durchgeblickt hätte :-)
Heute Abend, oder Morgen werd ich dein Beispiel testen.
Ich werde berichten...
Kontoumsätze einlesen
19.10.2008 15:31:00
Mark
Hallo Tino,
ich habe dein Modul mal mit 2 *.csv-Dateien angetestet, aber das funktioniert leider nicht auf Anhieb.
Ich habe mal den Debugger angeworfen und es soweit verfolgt, wie ich es verstehe.
In der Prozedur txt_Readline wird die Variable "lrow" am Anfang immer auf "1" gesetzt, sodass die Zerlegung der Datensätze in die Zellen abhängig von den Semikola nicht statt findet. Danach wird die Prozedur direkt beendet, sodass keine Daten übertragen worden sind. Die Prozedur "aufräumen" erzeugt dann einen Laufzeitfehler, weil der AdvancedFilter mindestens 2 Zeilen braucht, aber keine findet. Aber das ist wohl nur ein Folgefehler.
Meine csv-Dateien enthalten keine Zeilenumbrüche, die Notepad anzeigen würde. Die Daten sind durch Semikola getrennt.
In Notepad werden Rechtecke angezeigt, die die Zeilenenden anzeigen dürften.
Wenn ich so ein csv-Datei einfach unter Excel öffne, werden die Daten "richtig" nach Zeilen und Spalten getrennt angezeigt.
Könnte es sein, dass die Abfrage "If lrow > 1 then" für meine csv-Dateien überflüssig ist?
Grüße
Mark
Anzeige
AW: Kontoumsätze einlesen
19.10.2008 16:16:00
Tino
Hallo,
kannst Du mal eine solche Datei hochladen?
Die Bankdaten kannst du ja überschreiben, wichtig ist dass dieses Format erhalten bleibt.
Gruß Tino
Kontoumsätze einlesen
19.10.2008 17:36:00
Mark
Hallo Tino,
wie ich so eine Datei editieren kann, ohne die wesentlichen Daten zu verändern, weiß ich leider nicht. Notepad, Wordpad und Edit zeigen mir allesamt etwas anderes an. Mit Wordpad scheint es aber zu klappen. Ich hänge zwei Dateien an.
Das Problem habe ich aber inzwischen gelöst.
Da Excel das Format beim Öffnen aber selbständig konvertiert, habe ich mir eine Schleife geschrieben, die alle *.csv öffnet und als Kopie unter *.txt abspeichert.
Ich hoffe, dass ich den Code hier richtig posten kann:
sFile = Dir(sPath & "*.csv")
Do While sFile <> ""
Workbooks.Open Filename:=sPath & sFile
ActiveWorkbook.SaveCopyAs Filename:=sPath & sFile & ".txt"
ActiveWindow.Close
sFile = Dir()
Loop
Diese konvertierten *.txt-Dateien kann ich nun mit deinem Programm öffnen.
Jetzt habe ich aber andere Probleme, bei denen ich noch nicht weitergekommen bin. Dein Programm liest mir zwar den Inhalt der ersten Datei komplett ein, aber nichts der anderen Dateien.
Wenn ich das Programm dann nochmal starte, wird nur noch genau eine Zeile angefügt, die dann aber doppelt ist. Was da abgeht, habe ich aber noch nicht verstanden.
Bei der Datei "Auszug1.csv" wurde das Programm mit einem Laufzeitfehler 1004 abgebrochen. Das scheint nochmal ein besonderes Problem zu sein.
Ich guck gleich mal, ob ich diese Datei hochgeladen bekomme.
Ich hoffe, dass das klappt:
https://www.herber.de/bbs/user/56120.zip
Aber das Grundgerüst steht hier schon mal. Das ist für mich viel Wert.
Grüße
Mark
Anzeige
AW: Kontoumsätze einlesen
19.10.2008 18:28:38
Tino
Hallo,
die DIR Funktion versagt bei deinen Dateien, warum weis ich auch nicht,
habe eine andere Methode gewählt.
Nur ich weis nicht wie Du dies Umsätzen willst, beide Dateien sind unterschiedlich aufgebaut.
Bei meiner Bank sehen alle gleich aus, von Überschrift angefangen bis zum letzten Buchungseintrag.
https://www.herber.de/bbs/user/56121.xls
Gruß Tino
AW: Kontoumsätze einlesen
19.10.2008 19:16:00
Mark
Hallo Tino,
wenn ich dein letztes Modul auf meine *.csv loslasse, dann werden ebenfalls keine Daten übernommen. In "txt_Readline" ist
If lRow > 1 And sLine <> "" Then
immer false.
Aber gut, damit halte ich mich erstmal nicht auf. Das bekomme ich ja geregelt, indem ich die Dateien vorher einmal öffne und als Kopie speichere. Dabei biegt Excel das automatisch gerade.
Um die richtigen Daten in der Tabelle zu haben, würde ich von deiner letzten Lösung ausgehend einfach nachsehen, ob die Datensätze in den Spalten A und B ein korrektes Datum enthalten. Wenn nicht, dann würde ich die Zeilen löschen.
Das ist vielleicht nicht besonders elegant, aber das müsste ja funktionieren.
Wenn du da einen Tipp hättest, wie ich das am besten mache, wäre das super.
Ich würde im Prinzip so vorgehen.
1. Letzte benutzte Zeile finden (hab noch keine Ahnung, wie das geht)
2. Schleife von 1. bis zur letzen benutzen Zeile starten
3. Testen, ob in Spalte A und B ein korrektes Datum steht
(hab auch noch keine Ahnung, wie das geht)
4. Wenn in beiden Spalten kein korrektes Datum vorhanden, dann Zeile löschen
Evtl. muss ich das alles zweimal durchlaufen lassen, weil dadurch, dass ich eine Zeile lösche, könnte die nächste übersprungen werden.
Dann müsste ich eigentlich am Ziel sein.
Grüße
Mark
Anzeige
AW: Kontoumsätze einlesen
19.10.2008 20:01:13
Tino
Hallo,
ok. noch ein Versuch.
https://www.herber.de/bbs/user/56122.xls
zu Zeilen löschen:
Zeilen löscht man immer von unten nach oben, damit es keine Konflikte gibt. ;-)
Gruß Tino
AW: Kontoumsätze einlesen
19.10.2008 20:59:00
Mark
Guter Tipp mit der Richtung des Zeilenlöschens.
Ich habe das inzwischen mit einer For-Next-Schleife gemacht und darin einen Zähler von 0 an mitlaufen lassen, der die gelöschten Zeilen zählt. Die aktuelle Zeile ist dann immer der Wert der For-Variable minus der Zählervariable. Das klappt.
Meine ersten Versuche, die For-Variable in der Schleife zu manipulieren, haben dazu geführt, dass ich das Ende der Schleife nie erreicht habe :-)
Gut, das läuft jetzt.
Mir ist nun aber aufgefallen, dass die Inhalte der CSV-Dateien noch stärker durcheinander sind, als es mir bisher aufgefallen ist. Die Beträge stehen nämlich in den Spalten E bis I verstreut. Das ist echt ärgerlich.
Zum Glück steht in der letzten Spalte immer "EUR". Ich muss mir also die Zelle mit diesem String raussuchen und ggf. mit den beiden Zellen davor bis zur Spalte I nach hinten schieben.
Ansonsten scheine ich das Ziel fast erreicht zu haben :-)
Deinen letzten Versuch schau ich mir morgen an. Jetzt muss ich mich für Morgen entspannen gehen :-)
Nochmal vielen Dank für deine Hilfe. Ohne diese hätte ich wesentlich länger an der Sache gesessen.
Grüße
Mark
Anzeige
AW: Kontoumsätze einlesen
20.10.2008 09:45:00
Mark
So, nun hab ich mich total verrannt.
Ich hab bisher gedacht, dass wenn ich alle *.csv mit Excel öffne und dann abspeichere, ich keine Probleme mehr habe, aber dem ist nicht so.
Wenn ich eine Datei manuell öffne und abspeichere klappt das zwar, aber wenn ich diesen Vorgang mit dem Makro-Rekorder aufzeichne und dann mit VBA ablaufen lasse, dann wandelt mir das Makro anschließend alle Kommata in Semikola um.
Also so komm ich auch nicht weiter.
Dann habe ich deinen letzten Versuch ausprobiert, aber es bleibt beim Alten.
Die Anweisung Line Input # liest nicht eine einzelne Zeile, sondern den gesamten Inhalt der Dateien ein und packt mir das dann alles in eine Zeile.
In der Hilfe steht, dass die Anweisung bis zu einem CR oder CR LF liest.
Ich habe mal die *.CSV mit einem Hexeditor geöffnet. Im Original steht immer nur ein 0A. Wenn ich die Datei manuell mit Excel geöffnet und wieder abgespeichert habe, dann steht dort ein 0D, 0A.
Für mich sieht das im Moment so aus, dass die Zeilentrennung hier mit einem LF gemacht wird und dass die Anweisung Line Input # damit nicht klar kommt.
Und gerade sehe ich, dass auch Wordpad beim Abspeichern aus einem LF ein CR LF macht. Deshalb konntest du das bei dir nicht reproduzieren. Ich habe jetzt eine Datei mit dem Hexeditor so geändert, wie sie im Original ist.
https://www.herber.de/bbs/user/56129.zip
Wenn du dir beim Debuggen die Werte von "sline" ansiehst, dann werden dort nicht darstellbare Zeichen angezeigt. Zumindest ist das hier so, wenn ich den Mauscursor über "sline" halte. In der Watchlist sieht das wieder anders aus.
Gibt es vielleicht irgendwelche Schalter, die man setzen kann, damit sich die Anweisung anders verhält und auch auf einzelne LF reagiert?
Oder könnte man mit VBA die LF durch CR LF ersetzen?
Oder gibt es externe Tools, die das per Kommandozeile erledigen können?
Das wird ja echt ungeahnt kompliziert.
Grüße
Mark
Anzeige
AW: Kontoumsätze einlesen
20.10.2008 22:28:05
Mark
Hallo Tino,
erstmal nochmal herzlichen Dank für deine Mühe.
Das Einlesen klappt nun mit meinen Dateien. Ich glaube, ich konnte sogar halbwegs nachvollziehen, was du da gemacht hast, nachdem ich das mit Einzelschritten habe durchlaufen lassen :-)
Beim Aufräumen gibt es hier aber einen Laufzeitfehler bei der Anweisung:
'.Columns("A:A").TextToColumns Destination:=.Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=True, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 4), TrailingMinusNumbers:=True
~f~
Wenn ich das richtig verstehe würde mit der Anweisung nochmal der Inhalt der Zellen auf Semikola untersucht werden und bei Vorhandensein auf mehrere Spalten aufgeteilt. Semikola sollten dort aber wohl nicht mehr vorhanden sein, weil die schon beim Einlesen als Trennzeichen verwendet worden sind.
Ich habe die beiden Anweisungen erstmal ausgequotet.
Die beiden folgenden Anweisungen löschen mir dann die gesamte temporäre Tabelle.
~f~
'Zellen.FormulaR1C1 = "=IF(OR(NOT(ISNUMBER(RC1)),NOT(ISNUMBER(RC2))),TRUE,"""")"
'Zellen.SpecialCells(xlCellTypeFormulas, 4).EntireRow.Delete
~f~
Kann es sein, dass du das Datum der ersten beiden Spalten in seine Bestandteile zerlegen und auf je drei Spalten verteilen wolltest, um dann prüfen zu können, ob alle drei Spalten Zahlen enthalten?
Naja, ich habe die beiden Zeilen dann auch ausgequotet.
Jetzt habe ich im Ergebnis schon mal alle nicht doppelten Zeilen. Das ist schon was.
Ich hatte mir gestern selbst noch einen Filter geschrieben, der prüft, ob die Werte in A und B je ein Datum enthalten. Wenn nicht, habe ich die Zeilen gelöscht. Das hat mit ISDATE() ganz gut geklappt.
Diesen Filter werde ich mir morgen dran hängen und dann müsste ich eigentlich am Ziel sein.
Ich werde berichten.
... wenn ich wieder frisch bin :-)
Ach ja, mit dem Windows/DOS-Befehl ~f~ "more InDatei >OutDatei" bekomme ich die LF in CRLF konvertiert.
Kann man eine externe Programm aus Excel heraus starten?
Ich bin mir allerdings nicht sicher, welche Variante dann kompatibler wäre.
Grüße
Mark
Anzeige
AW: Kontoumsätze einlesen
21.10.2008 08:07:05
Tino
Hallo,
ich arbeite mit Office 2007,
wahrscheinlich macht diese Funktion (in diesem Aufbau), Probleme bei Dir.
Bin aber mit meinem Latein noch nicht am Ende. ;-)
https://www.herber.de/bbs/user/56158.zip
Gruß Tino
AW: Kontoumsätze einlesen
24.10.2008 13:34:41
Mark
Hallo Tino,
erstmal sorry, dass ich mich so lange nicht gemeldet habe, aber hier ging es drunter und drüber.
Dein letztes Modul bringt einen Laufzeitfehler 1004 in Sub Aufräumen bei TempR=TempVar~f~
Wenn ich im Debugger die Maus über TempVar halte, dann wird dort angezeigt: "Index außerhalb des gültigen Bereichs".
Im Kopf steht ~f~Dim TempVar()
Müsste dort nicht der Datentyp angegeben sein?
Wäre es nicht vielleicht einfacher, mit einer eigens angelegten Schleife alle Zeilen daraufhin zu prüfen, ob der Wert der ersten Spalte mit einer anderen Zeile überein stimmt? Wenn das der Fall ist, prüft man, ob die anderen Werte der Zeilen übereinstimmen und kickt diese ggf. raus.
Solche selbstgebastelten Schleifen sind zwar meistens viel langsamer, aber da weiß man, oder besser ich, dann wenigstens, was abgeht :-)
Grüße
Mark
Anzeige
AW: Kontoumsätze einlesen
24.10.2008 17:42:03
Tino
Hallo,
Müsste dort nicht der Datentyp angegeben sein?
Da ich kein Datentyp angegeben habe, ist dieser automatisch vom Typ Variant, diesen benötige ich auch, da in Deiner Tabelle unterschiedliche Daten vorkommen können, String, Date usw.
In diesen Zeilen wird die Area erstellt.
TempR ist der benutzte Bereich und dieser wird in einer Area gespeichert.
Set TempR = .Range("A1", .Cells(Rows.Count, 5).End(xlUp))
TempVar = TempR
Kann den Fehler bei mir nicht nachvollziehen?
Gruß Tino
AW: Kontoumsätze einlesen
27.10.2008 20:36:22
Mark
Hallo Tino,
ich habe mich heute mal durchgebissen und ausgehend von deiner vorletzten Version die Sache so zurecht gehuddelt, dass es das macht, was ich möchte.
Schön ist das nicht geworden und ich hab auch einige Sachen demontiert, die du vorgesehen hast, aber es läuft jetzt.
Ich bin jetzt aber auch ziemlich mürbe, weil ich über etliche Sachen gestolpert bin, die mir schon vor Jahren das Leben schwer gemacht haben und die ich immer noch nicht verstehe.
Z.B. funktioniert ein .range("a1:f6")., aber ein .range(cells(1,1),cells(6,6)). wird angemeckert.
Das ist nur eine Sache von vielen, die das hier extrem in die Länge gezogen haben.
Aber gut, ich bin praktisch am Ziel. Jetzt kommen nur noch die zeitraubenden Feinheiten :-) und dann kann ich das für alle Konten in der Familie kopieren.
Nochmal vielen Dank für deine Hilfe.
Grüße
Mark
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige