Vous pouvez utiliser et automatiser l'assistant 'Données' -> 'À partir du texte / CSV' d'Excel.

En utilisant l'enregistreur de macros, vous vous retrouvez avec ceci, ce qui devrait être un bon début:

ActiveWorkbook.Queries.Add Name:="MyFile", Formula:="let" & Chr(13) & "" & Chr(10) & "    Source = Table.FromColumns({Lines.FromBinary(File.Contents(""C:\Path\MyFile.txt""), null, null, 1252)})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    Source"
ActiveWorkbook.Worksheets.Add
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:="OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""MyFile"";Extended Properties=""""", Destination:=Range("$A$1")).QueryTable
    .CommandType = xlCmdSql
    .CommandText = Array("SELECT * FROM [MyFile]")
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .BackgroundQuery = True
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .PreserveColumnInfo = True
    .ListObject.DisplayName = "MyFile"
    .Refresh BackgroundQuery:=False
End With

Mathieu Guindon avait EXACTEMENT la solution que j'espérais. L'élimination de la transposition a résolu le problème avec les valeurs # N / A. Merci!

Modifier:

Le code boucle simplement les données matricielles une deuxième fois dans un tableau à deux dimensions, puis les publie dans la plage sans effet de transposition. C'est un peu plus lent que l'ancien (cela prend environ deux minutes de plus), mais c'est quand même assez rapide et produit les résultats que je souhaite. Le code est le suivant:

    lineValue = ts.ReadAll
DoEvents
arrLines() = Split(lineValue, vbCrLf)
Dim arrBetween() As Variant
ReDim arrBetween(UBound(arrLines), 0)

LoopLength = UBound(arrLines) - 1

For i = 0 To LoopLength
    arrBetween(i, 0) = arrLines(i)

    DoEvents

    If i Mod 2500 = 0 Or i = LoopLength Then
        Application.StatusBar = "Importing " & WorksheetName & " " & (i) & " ."
    End If
Next i

Dim Destination As Range
Set Destination = Worksheets(WorksheetName).Range("A2:A" & UBound(arrLines))

Destination.Value = arrBetween

Copier des fichiers texte dans Excel

Crédits à solution simple pour avoir suggéré (dans les commentaires) à ouvrez les fichiers texte avec Workbooks.Open.

Le code

Sub CopyTextFilesToExcel()

    ' Search Folder Path
    Const cStrPath As String _
            = "D:\Excel\MyDocuments\StackOverflow\"
    Const cStrExt As String = "*.txt"       ' File Extension
    Const cFolderPicker As Boolean = False  ' True to enable FolderPicker

    Dim wb As Workbook          ' Current File
    Dim strPath As String       ' Path of Search Folder (Incl. "\" at the end.)
    Dim strFileName As String   ' Current File Name

    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False
    End With

    On Error GoTo ProcedureExit

    ' Determine Search Path ("\" Issue)
    If cFolderPicker Then
        With Application.FileDialog(msoFileDialogFolderPicker)
            If .Show = False Then Exit Sub
            strPath = .SelectedItems(1) & "\"
        End With
      Else
        If Right(cStrPath, 1) <> "\" Then
            strPath = cStrPath & "\"
          Else
            strPath = cStrPath
        End If
    End If

    ' Determine first Current File Name.
    strFileName = Dir(strPath & cStrExt)

    With ThisWorkbook ' Target Workbook
        ' Loop through files in folder.
        Do While strFileName <> ""
            ' Create a reference to the Current File.
            Set wb = Workbooks.Open(cStrPath & strFileName)
            ' Copy first worksheet of Current File after the last sheet
            ' (.Sheets.Count) in Target Workbook.
            wb.Worksheets(1).Copy After:=.Worksheets(.Sheets.Count)
            ' Close Current File without saving changes (False).
            wb.Close False
            ' Find next File(name).
            strFileName = Dir()
        Loop
    End With

    MsgBox "All files copied!"

ProcedureExit:

  With Application
      .ScreenUpdating = True
      .DisplayAlerts = True
  End With

End Sub