title: VBA load large data from text file to Excel
body: <p>I have a problem loading data to Excel from a text file.
  The text file has about 230,000 lines and each line has exactly 130 characters. My goal is to get, from each line, specific data for what I'm trying to load the file into Excel and then retrieve the data I need. </p>
  
  <p>First, I used the following code, but it takes about 7 minutes.</p>
  
  <pre><code>Sub leerTXT()
  Dim strArchivo As String 'ruta del archivo
  Dim intResultado As Integer 'resultado del dialogo
  
  
  '---------- RUTA DEL ARCHIVO ----------
  'Abrir dialog y preparar
  Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = False
  intResultado = Application.FileDialog(msoFileDialogOpen).Show
  
  'sólo si hay resultado positivo abrir fichero y leer
  If intResultado &lt;&gt; 0 Then
      strArchivo = Application.FileDialog(msoFileDialogOpen).SelectedItems(1)
      On Error GoTo lblError:
  
  Dim strFila As String 'filas que se van a leer
  Dim iFila As Long  'numero de fila en la que se esta en el fichero
  Dim jFila As Long 'numero de fila en el excel
  'posicionarse en la fila 1
  iFila = 1
  jFila = 1
  
      'abrir el archivo
      Open strArchivo For Input As #1
      'loop mientras no se ha llegado al final del archivo
      Do Until EOF(1)
  
          If iFila Mod 70 &gt; 17 Then
              'leer la fila actual
              Line Input #1, strFila
  
              'leer la linea y copiar a celda
              Cells(jFila, 1) = strFila
  
              jFila = jFila + 1
          End If
  
          'incrementar iFila en uno y pasar a la siguiente fila
          iFila = iFila + 1
  
      'loopear mientras condicion
      Loop
  
  End If
  
  'cerrar el archivo
  Close #1
  
  lblError:
  If Err.Number &lt;&gt; 0 Then
  MsgBox (Err.Number)
  MsgBox (Err.Description)
  Err.Clear
  'cerrar el archivo
  Close #1
  End If
  
  End Sub
  </code></pre>
  
  <p>But as I said, that takes too long, so I searched for other ways and I saw something like the following:</p>
  
  <pre><code>Dim FSO As Object, MyFile As Object
  Dim FileName As String, Arr As Variant
  Dim f As Long
  
  FileName = textfilename
  Set FSO = CreateObject("Scripting.FileSystemObject")
  Set MyFile = FSO.OpenTextFile(FileName, 1)
  Arr = Split(MyFile.ReadAll, vbNewLine) ' Arr is zero-based array
  
  Dim BigGuy(0 To UBound(Arr, 1), 1 To 1) As Long
  
  Dim I As Long
  For I = 0 To UBound(Arr)
      BigGuy(I, 1) = I
  Next
  'For test
  'Fill column A from this Array Arr
     'UBound(Arr) + 1 Application.Transpose(
  Range("A1:A500") = BigGuy
  </code></pre>
  
  <p>The clue is to use array and paste range at it seems to be faster, but as the file is too long to transpose, I need to transpose it manually, but <code>UBound(Arr)</code> doesn't work and I don't know how to import the data.</p>
  
  <p>And by the way, is there any way of just retrieving specific characters from the file (in an exact position), as every line has exactly the same length?</p>