Excel-VBA - Daten aus Textdatei

Computer: Software & Programmierung Visual Basic
#1

Hallo,

mit VB6 kenne ich mich inzwischen etwas aus, nun muss ich aber Daten in eine Excel-Tabelle eintragen und finde den richtigen Weg nicht.

Wenn ich das für VB6 schreibe und die Daten statt in eine Excel-Tabelle in ein Flexgrid eintrage, sieht der Code dafür etwa so aus …

Private Sub Command1\_Click()
 Dim Monat As String, Jahr As String
 Dim ff As Integer, Feld() As String, Zl As String, n As Integer
 Monat = Trim(Text1.Text)
 Jahr = Trim(Text2.Text)
 CommonDialog1.Filter = "\*" + Jahr + "-" + Monat + ".txt|\*" + Jahr + "-" + Monat + ".txt|"
 CommonDialog1.ShowOpen
 ff = FreeFile
 n = 1
 Open CommonDialog1.FileName For Input As #1
 While Not EOF(1)
 Line Input #1, Zl
 Feld = Split(Zl, "|")
 For c = LBound(Feld) To UBound(Feld)
 MSFlexGrid1.TextMatrix(n, c) = Feld(c)
 Next
 n = n + 1
 Wend
 Close #1
End Sub

Aber wie kann ich das nach Excel-VBA umsetzen?
Von dem Code funktioniert fast nichts.
Lediglich ‚Cells(n, c) = Feld©‘ bekomme ich noch selbst hin.
Den ‚Commondialog‘ finde ich nicht, ‚Open‘ geht nicht, ‚Split‘ geht nicht …

Die Textdatei erzeuge ich übrigens auch selbst mit einem VB6-Programm. Eenn ich für Excel besser andere Trennzeichen verwenden sollte, ist das kein Problem. Die Zellen in der Exceltabelle, die da gefüttert werden soll liegen nicht wie hier im Beispiel direkt nebeneinander, da sind noch Zellen dazwischen, die Berechnungen aus den Daten enthalten. Nur falls es einfacher ist, ganze Zeilen einzufügen, denn dann dürfen die Zellen dazwischn nicht beschrieben werden.

Die Exceltabelle werde ich auch nicht selbst verwenden, der Umweg über diese Textdatei ist nötig.

Gruß Rainer

PS. Die Frage habe ich auch noch bei http://www.ActiveVb.de gestellt,
sobald das Problem gelöst ist, poste ich die Lösung hier.

#2

Hallo Rainer,

ist zwar egal da die Brettbeschreibung von denjenigen für die sie wichtig wäre sowieso nicht gelesen wird :smile:
Aber bei VBA-Anfragen wär schon schick das in der Brettbeschreibung stünde:

Bei VBA bitte immer Programmversion und Programmnae angeben.
Das VBA von Corel Draw,Outlook,Word,Excel unterscheidet sich dann doch sehr, deshalb der Programmname.
Warum die Programmversion ist auch klar.

Zu dem Split-Problem. Excel 97 baut auf VB5 auf, Excel 2000 auf VB6.
Und VB5 kennt keine „Split“ Funktion.

Ich hab mir da was gebastelt:

Function Split97(ByVal sStr As Variant, sdelim As String) As Variant
Dim Anz, Sat()
While InStr(sStr, sdelim) \> 0
 ReDim Preserve Sat(Anz)
 Sat(Anz) = Left(sStr, InStr(sStr, sdelim) - 1)
 sStr = Mid(sStr, Len(Sat(Anz)) + 1 + Len(sdelim))
 Anz = Anz + 1
Wend
If Len(sStr) \> 0 Then
 ReDim Preserve Sat(Anz)
 Sat(Anz) = sStr
End If
Split97 = Sat
End Function

Zum CoomonDialog1-problem. Das kann ich mit VB% und XL97 nicht nachstellen, Das Ding existiert und kann benutzt werden.
Ich habe mir eine Form in VB5 nachgestellt mit einem CB, einem Flexgrid und einem CommandDialog.

nach Korrektur von Split und der nichtdeklerierten variablen c, läüft der Code problemlos bis dahin wo ich eine Datei öffnen kann.
Kannst du mal bitte eine derartige Textdatei hochladen damit ich die öffnen kann um dann wohl in dem FlexGrid mal sehen zu können wie die Daten verteilt sind.

Mit den schon vorhandenen Berechnungszellen, soll das heißen , mal für Zeile 1 gesehen, in B1 und D1 stehen schon Formeln die erhalten bleiben sollen und du liest aus der Textdatei die erste Zeile ein, die so aussieht:
A1 Trenn B1 Trenn C1 Trenn D1 Trenn E1 Trenn F1
dann müßte das doch dann so gehen:

For c = LBound(Feld) To UBound(Feld)
 If Feld(c) "" Then MSFlexGrid1.TextMatrix(n, c) = Feld(c)
Next c

Das bedingt aber daß in der ausgelesenen Zeile B1 und D1 leer sind.

Ich muß weg und schaue erst heute Abend wieder rein.

Gruß
Reinhard

#3

Hallo Reinhard,

ist zwar egal da die Brettbeschreibung von denjenigen für die
sie wichtig wäre sowieso nicht gelesen wird :smile:
Aber bei VBA-Anfragen wär schon schick das in der
Brettbeschreibung stünde:

Bei VBA bitte immer Programmversion und Programmnae angeben.
Das VBA von Corel Draw,Outlook,Word,Excel unterscheidet sich
dann doch sehr, deshalb der Programmname.
Warum die Programmversion ist auch klar.

Zu dem Split-Problem. Excel 97 baut auf VB5 auf, Excel 2000
auf VB6.
Und VB5 kennt keine „Split“ Funktion.

Das Programm muss so sein, daß die Excel-Version egal ist, ich weiß nämlich nicht, was dort läuft, wo es eigesetzt werden muss. :smile:

Split nachzubilden ist mir dafür zu heftig, eventuell auch langsamer :smile:
Weil ich die Daten ja selbst schreibe, verzichte ich darauf und schreibe einfach nicht mehrere Daten in eine Zeile, dann nuss ich sie auch nicht zerlegen.

Zum CoomonDialog1-problem. Das kann ich mit VB% und XL97 nicht
nachstellen, Das Ding existiert und kann benutzt werden.
Ich habe mir eine Form in VB5 nachgestellt mit einem CB, einem
Flexgrid und einem CommandDialog.

??? Da gab es doch in VBA mal einen eigenen Filedialog, ich weiß noch, daß ich so etwas mal bei Dir gesehen habe. War das mit Application.xxx? Ich hab’s leider vergessen und finde es nicht mehr.
Um ein OCX zu verwenden müsste ich ja wohl auch eine Form haben, oder? Die benötige ich hier vermutlich gar nicht.

Kannst du mal bitte eine derartige Textdatei hochladen damit
ich die öffnen kann um dann wohl in dem FlexGrid mal sehen zu
können wie die Daten verteilt sind.

Das ist erstens nicht nötig, es sind immer nur Gruppen von sechs Werten pro Zeile, die eingetragen werden müssen, es sind immer alle sechs vorhanden.
Zweitens, nein, ich müsste für den Zweck auch einfach zufällige Zahlen erfinden, das kannst du auch selbst. Die Originaldaten sind Personaldaten … Zu sensibel zum hoch laden.

Zum besseren Verständnis die Felder:

  • Stammnummer
  • Name, Vorname
  • Datum, Schicht
  • Verfahrene Zeit
  • Erarbeitete Zeit
  • Zeitgrad

Die Daten kommen aus Akkordberichten, die Excel-Tabelle errechnet den Stundenlohn.

Statt der Trennzeichen habe ich jetzt eine Schleife, scheint besser zu sein, der Code sieht jetzt so aus:

Private Sub Workbook\_Open()
Dim FSO
Dim oShell
Dim Datei
Dim Txt
Dim n As Integer, c As Integer

Dim po(6)

po(1) = 2
po(2) = 4
po(3) = 5
po(4) = 7
po(5) = 8
po(6) = 10
n = 4

Set FSO = CreateObject("Scripting.FileSystemObject")
Set Datei = FSO.OpenTextFile("C:\Test.txt", 1, True)
Do While Datei.AtEndOfStream True
 n = n + 1
 For c = 1 To 6
 Txt = Datei.Readline
 Cells(n, po(c)) = Txt
 Next
Loop
End Sub

Das geht schon mit VBA, ich habe einfach VBS-Code genommen. :smile:

So weit tut der Code, was er soll, nur der Pfad fest im Code passt noch nicht, die Datei mu8ss man auswählen können.

Gruß Rainer

#4

Danke, erledigt
Hallo Reinhard,

Erledigt!

Darauf muss man erst mal kommen, als Handle einfach Null zu übergeben, wenn man mit VBA arbeitet und kein Handle hat … :frowning:

So sieht es aus.

Private Declare Function GetOpenFileName Lib "comdlg32.dll" Alias "GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long

Private Type OPENFILENAME
 lStructSize As Long
 hwndOwner As Long
 hInstance As Long
 lpstrFilter As String
 lpstrCustomFilter As String
 nMaxCustFilter As Long
 nFilterIndex As Long
 lpstrFile As String
 nMaxFile As Long
 lpstrFileTitle As String
 nMaxFileTitle As Long
 lpstrInitialDir As String
 lpstrTitle As String
 flags As Long
 nFileOffset As Integer
 nFileExtension As Integer
 lpstrDefExt As String
 lCustData As Long
 lpfnHook As Long
 lpTemplateName As String
End Type

Private Sub Workbook\_Open()

Dim FSO
Dim oShell
Dim Datei
Dim Txt
Dim n As Integer, c As Integer
Dim po(6) As Integer
Dim Pfad As String


 Dim OFName As OPENFILENAME
 OFName.lStructSize = Len(OFName)
 OFName.hwndOwner = 0
 OFName.hInstance = 0
 OFName.lpstrFilter = "Text Files (\*.txt)" + Chr$(0) + "\*.txt" + Chr$(0)
 OFName.lpstrFile = Space$(254)
 OFName.nMaxFile = 255
 OFName.lpstrFileTitle = Space$(254)
 OFName.nMaxFileTitle = 255
 OFName.lpstrInitialDir = "C:\"
 OFName.lpstrTitle = "Datei wählen"
 OFName.flags = 0

 If GetOpenFileName(OFName) Then
 Pfad = Trim$(OFName.lpstrFile)

 po(1) = 2
 po(2) = 4
 po(3) = 5
 po(4) = 7
 po(5) = 8
 po(6) = 10
 n = 4

 Set FSO = CreateObject("Scripting.FileSystemObject")
 Set Datei = FSO.OpenTextFile(Pfad, 1, True)
 Do While Datei.AtEndOfStream True
 n = n + 1
 For c = 1 To 6
 Txt = Datei.Readline
 Cells(n, po(c)) = Txt
 Next
 Loop
 End If
End Sub

Gruß Rainer

#5

Hallo Rainer,

Das Programm muss so sein, daß die Excel-Version egal ist, ich
weiß nämlich nicht, was dort läuft, wo es eigesetzt werden
muss. :smile:

tja nun, dann muß man nur Funktionen benutzen die alle kennen.

Oder in einem Modul zuallerst sowas schreiben:

Option Explicit
#If VBA6 = 0 Then
 Const FM20\_GUID = "{C43ABEE0-5C8F-4D95-B2C1-05B898491C64}" 'XL97
#Else
 Const FM20\_GUID = "{0D452EE1-E08F-101A-852E-02608C4D0BB4}" 'XL2000 und höher
#End If

d.h. man setzt Konstanten und prüft die dann ab. Dies ist hier für Erkennung ob XL97 oder nicht. Wie und ob man derart irgendwie auch andere Versionen erkennen kann weiß ich nicht.

Alternativ die Version ermitteln mit

Application.version

das gibt einen String zurück, den man auswerten kann:
8.0 = XL 97
9.0 = XL 2000
10.0 = XL2002
usw.

Wie das mit Excel-Vba auf einem Mac aussieht weiß ich nicht. In der Hilfe von Vba steht oft bei Funktionen der Hinweis daß diese Funktion bei Excel für mac anders benutzt werden muß oder anders funktionirt, andere Rükgabewerte und/oder parameter hat.

Split nachzubilden ist mir dafür zu heftig, eventuell auch
langsamer :smile:

? dann nimm doch meine „Nachbildung“.

??? Da gab es doch in VBA mal einen eigenen Filedialog, ich
weiß noch, daß ich so etwas mal bei Dir gesehen habe. War das
mit Application.xxx? Ich hab’s leider vergessen und finde es
nicht mehr.

Du meinst dieses:

Datei = Application.GetOpenFilename

Um sich CurDir nicht zu verbiegen kann man sich vorher CuDir merken, dann mit ChDrive und Chdr in ein gewünschtes Verzeichnis wechseln, dann startet Getopenfilename in diesem Verzeichneis, anschließend Curdir wieder zurücksetzen.

Um ein OCX zu verwenden müsste ich ja wohl auch eine Form
haben, oder? Die benötige ich hier vermutlich gar nicht.

Wie du magst, du kannst eine Userform in Excel benutzen oder halt nicht. Liegt kein Grund vor eine haben zu müssen.

Dim n As Integer, c As Integer

Benutze für Zeilen und Spalten in Excel grundsätzlich bitte immer Long

Erstens geht Integer nur bis 32.xxx , d.h bei Zeile 32.xxx hast du ein Problem mit Überlauf.

Zweitens, aus F2=Objektkatalog ( auch ne Baustelle von mir wo ich mal reinschauen sollte *gg*) geht klar heraus daß Cells(z,s) Werte vom Typ Long erwartet bzw. benutzt. D.h. Intergerwere bringen Zeitverlust da sie erst noch umgewandelt werden müssen.
Ist zwar für paar Zellen egal aber Excel 2007 hat 1.000.000 mal 16.xxx Zellen, da läppert sich das schon…

Zum Tunen noch eins, anfangs des Codes, üblicherweise unterhalb der Dims:

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

zum Ende des Codes einfügen:

Application.Calculate ’ weiß grad nicht ob diese Zeile nötig ist, glaub nicht.
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

So weit tut der Code, was er soll, nur der Pfad fest im Code
passt noch nicht, die Datei mu8ss man auswählen können.

Wie oben geschrieben, wechsle vorher das Verzeichnis.

Gruß
Reinhard

#6

Hallo Reinhard,

Das Programm muss so sein, daß die Excel-Version egal ist, ich
weiß nämlich nicht, was dort läuft, wo es eigesetzt werden
muss. :smile:

tja nun, dann muß man nur Funktionen benutzen die alle kennen.

Richtig! :smile: Genau so war es gemeint.

Wie das mit Excel-Vba auf einem Mac aussieht weiß ich nicht.

Kein Mac, so etwas gibt’s in der Firma nicht. Mal sehen, ob die noch NT4 haben.

? dann nimm doch meine „Nachbildung“.

Nicht mehr nötig, ich habe die Quelldaten umbebaut. :smile:

??? Da gab es doch in VBA mal einen eigenen Filedialog, ich
weiß noch, daß ich so etwas mal bei Dir gesehen habe. War das
mit Application.xxx? Ich hab’s leider vergessen und finde es
nicht mehr.

Du meinst dieses:

Datei = Application.GetOpenFilename

Genau! Das war’s, was mir nicht mehr eingefallen ist. :smile:

Um sich CurDir nicht zu verbiegen kann man sich vorher CuDir
merken, dann mit ChDrive und Chdr in ein gewünschtes
Verzeichnis wechseln, dann startet Getopenfilename in diesem
Verzeichneis, anschließend Curdir wieder zurücksetzen.

Ja, ich weiß. Ob das gebraucht wird weiß ich aber nicht.

Dim n As Integer, c As Integer

Benutze für Zeilen und Spalten in Excel grundsätzlich bitte
immer Long

OK ich baue es um.

Erstens geht Integer nur bis 32.xxx , d.h bei Zeile 32.xxx
hast du ein Problem mit Überlauf.

So viel wird auf ein A4-Blatt nicht passen. :smile:

Es sind sechs Felder und der Monat hat maximal 31 Tage. Größer kann das nicht werden. Die Datenmangen sind sehr überschaubar.

Zweitens, aus F2=Objektkatalog ( auch ne Baustelle von mir wo
ich mal reinschauen sollte *gg*) geht klar heraus daß
Cells(z,s) Werte vom Typ Long erwartet bzw. benutzt. D.h.
Intergerwere bringen Zeitverlust da sie erst noch umgewandelt
werden müssen.
Ist zwar für paar Zellen egal aber Excel 2007 hat 1.000.000
mal 16.xxx Zellen, da läppert sich das schon…

Na ja, bei mir werden es maximal 6 mal 31. :smile:
Tunen muss ich da nichts, die paar Daten werden ohnehin in Sekundenbruchteilen gelesen.

Gruß Rainer