AW: import aus txt file
24.10.2006 11:46:58
fcs
Hallo Peter,
folgendes Makro sollte das Problem lösen.
Gruß
Franz
Sub txtimportmehrals256Spalten()
Dim wks() As Worksheet, Zeilentext, Zeile As Long, Spalte As Integer
Dim wb As Workbook, I As Long, J As Integer
Dim Dateiname As String, ff As Integer, Text As String, Spalten As Integer
Zeile = 1
Dateiname = Application.GetOpenFilename("Text Files (*.txt), *.txt", , "Bitte Textdatei auswählen")
If Dateiname = "" Then Exit Sub
ff = FreeFile()
Open Dateiname For Input As #ff
Do Until EOF(ff)
Line Input #ff, Text
'Delimiter in der nächsten Zeile ggf. anpassen
Zeilentext = Split(Expression:=Text, Delimiter:=";")
'Neue Datei anlegen und ggf. Blattzahl anpassen
If Zeile = 1 Then
Spalten = UBound(Zeilentext) + 1
Set wb = Workbooks.Add
Do Until wb.Worksheets.Count >= Spalten / 256
wb.Worksheets.Add After:=wb.Worksheets(wb.Worksheets.Count)
Loop
ReDim wks(1 To Int(Application.WorksheetFunction.RoundUp(Spalten / 256, 0)))
For I = 1 To UBound(wks)
Set wks(I) = wb.Worksheets(I)
Next I
End If
' Daten in Spalten eintragen
For I = 1 To UBound(wks)
For J = 1 To 256
wks(I).Cells(Zeile, J).Value = Zeilentext(Spalte)
Spalte = Spalte + 1
If Spalte = Spalten Then GoTo nexteSatz
Next J
Next I
nexteSatz:
Zeile = Zeile + 1
Spalte = 0
Loop
Close #ff
End Sub