VBA-Programmierung in Microsoft Excel

Import einer Textdatei mit mehr als 256 Spalten

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

    

Beiträge aus dem Excel-Forum zu den Themen Extern und TextImport