Gruppe
Extern
Problem
Eine Textdatei mit über 256 Spalten soll so importiert werden, dass die über das jeweilige Blatt hinausgehenden Spalten in Folgeblätter eingetragen werden.
StandardModule: Modul1
Sub SplitImport()
Dim wks As Worksheet
Dim arr() As Variant
Dim lRow As Long
Dim iCol As Integer
Dim sFile As String, sSeparator As String, sTxt As String
Application.ScreenUpdating = False
Set wks = ActiveSheet
sFile = "c:\temp\test.txt"
If Dir(sFile) = "" Then
Beep
MsgBox "Die Textdatei wurde nicht gefunden!"
Exit Sub
End If
sSeparator = vbTab
Close
Open sFile For Input As #1
Do Until EOF(1)
Line Input #1, sTxt
Set wks = Worksheets(1)
lRow = lRow + 1
iCol = 1
Do While InStr(sTxt, sSeparator)
If iCol > 256 Then
If wks.Index = Worksheets.Count Then
Worksheets.Add after:=Worksheets(Worksheets.Count)
Set wks = ActiveSheet
Else
Set wks = Worksheets(wks.Index + 1)
End If
iCol = 1
End If
wks.Cells(lRow, iCol).Value = Left(sTxt, InStr(sTxt, sSeparator) - 1)
sTxt = Right(sTxt, Len(sTxt) - InStr(sTxt, sSeparator))
iCol = iCol + 1
Loop
wks.Cells(lRow, iCol).Value = sTxt
Loop
Close
Application.ScreenUpdating = True
End Sub