Textdatei einlesen, nach Trennzeichen trennen und in eine bestimmte Spalte schreiben

[johann.s]

Hallo liebe Fangemeinde,
nach langem googeln und ausprobieren muss ich mich geschlagen geben. Habe mir ein Macro gegoogelt, etwas angepasst und es funktioniert auch fast wie gewünscht, aber nur fast.

Ich versuche gerade, einen Schnittstellenconverter zu basteln. Das soll wie folgt laufen: Excel-Datei wird geöffnet, der Benutzer wählt die Spalten die in der zu verarbeitenden Datei enthalten sind (gelöst mit 2 Userformen) und öffnet die zu verarbeitende Datei. Dabei sollen die Daten gemäß der Auswahl in die entsprechende Spalte in einem Tabellenblatt abgelegt werden.

Hier ist der Quellcode:

Code:

Private Sub BTN_OpenFile_Click()
Dim ff As Long
Dim sFile As String
Dim sLine As String
Dim arr() As String
Dim row As Long
Dim col As Long
Dim activCell As Range

ff = FreeFile
sFile = Application.GetOpenFilename("beliebige Datei,*.*")
'datei oeffnen
Open sFile For Input As #ff

'erste zelle markieren
Sheets("Voucher").Select
Set activCell = Worksheets("Voucher").Range("A1")

'Call activCell.Activate

While (Not EOF(ff))
Line Input #ff, sLine 'zeile einlesen
If row >= 1 Then
   arr = Split(sLine, ";") 'an semikolons aufspalten
  
   'relativ zur aktiven zelle den wert setzen
   For col = LBound(arr) To UBound(arr)
       'bestimmt, in welcher Spalte der Wert geschrieben wird
       For feld = 0 To UserForm1.ListBox2.ListCount - 1
           Select Case UserForm1.ListBox2.List(feld)
               Case "Date"
                   spalte = 1
               Case "Voucher-No."
                   spalte = 2
               Case "Invoice-No."
                   spalte = 3
               Case "General Account-No."
                   spalte = 4
               Case "General Account-No. Countermark"
                   spalte = 5
               Case "S/P-Account No."
                   spalte = 6
               Case "S/P-Account No. Countermark"
                   spalte = 7
               Case "Entry Description"
                   spalte = 8
               Case "Maturity Date"
                   spalte = 9
               Case "Quantity"
                   spalte = 10
               Case "Amount"
                   spalte = 11
               Case Else
                   spalte = 28
           End Select
       activCell.Offset(row + 6, spalte - 1).Value = arr(col)
       Next feld
   Next 'i
End If
row = row + 1
Wend

'schliessen
Close ff
End Sub

Das Makro schreibt zwar in die ausgewählten Spalten, allerdings immer den letzten ausgewählten Wert. Wenn man also z.B. die Spalten Date und Voucher-No. auswählt, werden beide Spalten mit Voucher-No. gefüllt. Wie bekommt man das Makro so hin, dass in den jeweiligen Spalten auch die richtigen Werte landen, und nicht nur der letzte?

Die Excel-Datei und eine kleine Datei zum Testen sind angefügt. Vielen Dank schon mal im Voraus für die Lösungsvorschläge.

Viele Grüße
Johann

[schauan]

Hallo Johann,

zwei Probleme Deiner Lösung liegen darin,

1. dass die Einträge in der Textdatei unabhängig von den Einträgen Deiner Listbox sind. Der user sucht sich irgendwelche Einträge in der Listbox aus. Ob die zu den Daten in der Datei passen, weiß er eventuell vorher gar nicht oder er wählt eine falsche Datei aus oder ...

2. dass in Deiner Schleifenkonstruktion ein Arrayeintrag aus arr in alle gewählten Spalten geschrieben wird.
Du nimmst im äußeren For einen "Index" oder Zähler von Arr, z.b. 0. Anschließend schleifst Du über die Einträge des Listenfeldes. Ist der z.B. Date, trägst Du den zum Index gehörenden Wert von arr(col), z.B. arr(0), dort ein. Anschließend holst Du Dir den nächsten Eintrag des Listenfeldes und trägst den gleichen Wert in die nächste Spalte ein. Der Zähler col ändert sich in der inneren Schleife ja nicht - also nimmst Du wieder z.B. arr(0).
Du müsstest also die Logik ändern.

[johann.s]

Hallo André

vielen Dank für die schnelle Antwort, bei beiden Punkten hast Du recht.

Punkt 1 wird sich später ändern, ich habe vor, dass durch Standardangaben später zu ersetzen. Man sollte dann z. B. nur auswählen "Rechnungen vom Lieferanten xxx", die dafür benötigten Felder und sonstige Einstellungen sollten dann automatisch geladen werden.

Beim Punkt 2 werde ich versuchen die Schleifen anders zu machen. Der Array wird gelesen und anschließend nach dem ersten Trennzeichen durchsucht. Der String zwischen dem Anfang und dem ersten Trennzeichen, bzw. anschließend zwischen den einzelnen Trennzeichen soll in die entsprechende Spalte geschrieben werden. Ich muss mich allerdings zum Thema "Array" erstmal einlesen, bin mal gespannt ob es klappt.

Viele Grüße
Johann

[schauan]

Hallo Johann,

das Array ist wohl die einfachere Sache in Deinem Projekt

So, wie Du es jetzt angehst, werden die Daten häufig nicht zu den Spalten passen. Wenn Du z.B. nur das Datum übernehmen willst, kommt dort auch immer der Eintrag aus der 4. "Spalte".

Wenn Deine Daten in der Reihenfolge immer gleich sind und zu den Spalten passen, könntest Du eine Listbox nehmen wo Du über Multiselect mehrere Einträge wählen kannst. Die Spalten- und Arrayzuordnung nimmst Du dann über den ListIndex bzw. die Selected-Eigenschaft. Wenn der 2. Eintrag und der 4. Eintrag gewählt wird, nimmst Du denn dann den 2. und 4. Arrayeintrag in die 2. und die 4. Spalte der Tabelle... Du brauchst dann also nur noch eine Schleife über die Einträge der Listbox.

[johann.s]

Hallo André,

vielen Dank noch mal für die Hilfe, das Problem habe ich mittlerweile durch vieles Rumprobieren gelöst. Die Listboxen habe ich komplett weggelassen, war mir dann doch etwas zu unsicher.

Die Zeile wird eingelesen und nach dem Trennzeichen durchsucht, die gefundene Trennzeichen werden in einen neuen Array geschrieben.
Ich muss dann pro Lieferanten bestimmen, wieviele Spalten in der Textdatei enthalten sind (für die Größe des neuen Arrays) und was sie beinhalten. Anschließend werden die einzelnen "Spalten" zwischen den Trennzeichen ausgelesen und in die entsprechende Spalte geschrieben.

Ist zwar eventuell nicht die feinste Lösung, aber es scheint zu funktionieren.

Viele Grüße
Johann