title: How to open a very large .dat file using excel vba
body: <p>I have some data files (.dat) that are very large (exceed the 1,048,000 rows Excel allows).  I can't quite figure out what the problem is with the attempted macros (originally written for text files with "," delimiter, not .dat files with tab delimiter).  The macro works, however it causes the data to be compiled into one column (ex. supposed to be 5 columns, now 1 column with all the numbers as a long text string).  Is there a better way to open a very large .dat file, split it up and import it into separate worksheets while keeping the data in separate columns using the tab delimiter?</p>
  
  <pre><code>Sub ImportBigFile()
       Dim N As Long
       Dim Lim As Long
       Dim SS() As String
       Dim S As String
       Dim R As Long
       Dim C As Long
       Dim WS As Worksheet
       Dim FNum As Integer
       Dim FName As String
  
       FName = "C:\Folder 1\Folder 2\File.dat"
       FNum = FreeFile
  
       With ActiveWorkbook.Worksheets
           Set WS = .Add(after:=.Item(.Count))
       End With
  
       Lim = WS.Rows.Count
       Open FName For Input Access Read As #FNum
       R = 0
       Do Until EOF(FNum)
           R = R + 1
           Line Input #FNum, S
           SS = Split(S, "\t", -1)
           For C = LBound(SS) To UBound(SS)
               WS.Cells(R, C + 1).Value = SS(C)
           Next C
           If R = Lim Then
               With ActiveWorkbook.Worksheets
                   Set WS = .Add(after:=.Item(.Count))
               End With
               R = 0
           End If
       Loop
   End Sub
  </code></pre>