Note: The other languages of the website are Google-translated. Back to English
Se connecter  \/ 
x
or
x
INSCRIPTION  \/ 
x

or

Comment importer plusieurs fichiers texte d'un dossier dans une seule feuille de calcul?

Pour les cas, ici vous avez un dossier avec plusieurs fichiers texte, ce que vous voulez faire est d'importer ces fichiers texte dans une seule feuille de calcul comme ci-dessous la capture d'écran. Au lieu de copier les fichiers texte un par un, existe-t-il des astuces pour importer rapidement les fichiers texte d'un dossier dans une feuille?

Importez plusieurs fichiers texte d'un dossier dans une seule feuille avec VBA

Importer un fichier texte dans la cellule active avec Kutools for Excel bonne idée3


Voici un code VBA qui peut vous aider à importer tous les fichiers texte d'un dossier spécifique dans une nouvelle feuille.

1. Activez un classeur dans lequel vous souhaitez importer des fichiers texte et appuyez sur Alt + F11 clés pour activer Microsoft Visual Basic pour applications fenêtre.

2. Cliquez insérer > Module, copiez et collez ci-dessous le code VBA dans le Module fenêtre.

VBA: importer plusieurs fichiers texte d'un dossier vers une feuille

Sub Test()
'UpdatebyExtendoffice6/7/2016
    Dim xWb As Workbook
    Dim xToBook As Workbook
    Dim xStrPath As String
    Dim xFileDialog As FileDialog
    Dim xFile As String
    Dim xFiles As New Collection
    Dim I As Long
    Set xFileDialog = Application.FileDialog(msoFileDialogFolderPicker)
    xFileDialog.AllowMultiSelect = False
    xFileDialog.Title = "Select a folder [Kutools for Excel]"
    If xFileDialog.Show = -1 Then
        xStrPath = xFileDialog.SelectedItems(1)
    End If
    If xStrPath = "" Then Exit Sub
    If Right(xStrPath, 1) <> "\" Then xStrPath = xStrPath & "\"
    xFile = Dir(xStrPath & "*.txt")
    If xFile = "" Then
        MsgBox "No files found", vbInformation, "Kutools for Excel"
        Exit Sub
    End If
    Do While xFile <> ""
        xFiles.Add xFile, xFile
        xFile = Dir()
    Loop
    Set xToBook = ThisWorkbook
    If xFiles.Count > 0 Then
        For I = 1 To xFiles.Count
            Set xWb = Workbooks.Open(xStrPath & xFiles.Item(I))
            xWb.Worksheets(1).Copy after:=xToBook.Sheets(xToBook.Sheets.Count)
            On Error Resume Next
            ActiveSheet.Name = xWb.Name
            On Error GoTo 0
            xWb.Close False
        Next
    End If
End Sub

3. presse F5 pour afficher une boîte de dialogue et sélectionnez un dossier contenant les fichiers texte à importer. Voir la capture d'écran:
doc importer des fichiers texte à partir d'un dossier 1

4. Cliquez OK. Ensuite, les fichiers texte ont été importés dans le classeur actif en tant que nouvelle feuille séparément.
doc importer des fichiers texte à partir d'un dossier 2


Si vous souhaitez importer un fichier texte dans une cellule ou une plage spécifique, vous pouvez appliquer Kutools pour Excel's Insérer un fichier au curseur utilitaire.

Kutools pour Excel, avec plus de 300 fonctions pratiques, rend vos travaux plus faciles. 

Après installation gratuite Kutools for Excel, procédez comme suit:

1. Sélectionnez une cellule dans laquelle vous souhaitez importer le fichier texte, puis cliquez sur Kutools Plus > Import / Export > Insérer un fichier au curseur. Voir la capture d'écran:
doc importer des fichiers texte à partir d'un dossier 3

2. Ensuite, une boîte de dialogue apparaît, cliquez sur DECOUVREZ pour afficher le Sélectionner un fichier à insérer dans la boîte de dialogue de position du curseur de cellule, sélectionnez ensuite Fichiers texte dans la liste déroulante, puis choisissez le fichier texte que vous souhaitez importer. Voir la capture d'écran:
doc importer des fichiers texte à partir d'un dossier 4

3. Cliquez Open > Ok, et le fichier texte spécifié a été inséré à la position du curseur, voir capture d'écran:
doc importer des fichiers texte à partir d'un dossier 5


Les meilleurs outils de productivité de bureau

Kutools for Excel résout la plupart de vos problèmes et augmente votre productivité de 80%

  • Réutilisation: Insérer rapidement formules complexes, graphiques et tout ce que vous avez utilisé auparavant; Crypter les cellules avec mot de passe; Créer une liste de diffusion et envoyer des e-mails ...
  • Barre Super Formula (modifiez facilement plusieurs lignes de texte et de formule); Disposition de lecture (lire et modifier facilement un grand nombre de cellules); Coller dans la plage filtrée...
  • Fusionner les cellules / lignes / colonnes sans perdre de données; Contenu des cellules divisées; Combiner des lignes / colonnes en double... Empêcher les cellules en double; Comparer les gammes...
  • Sélectionnez Dupliquer ou Unique Lignes; Sélectionnez les lignes vides (toutes les cellules sont vides); Super Find et Fuzzy Find dans de nombreux classeurs; Sélection aléatoire ...
  • Copie exacte Plusieurs cellules sans changer la référence de formule; Créer automatiquement des références à plusieurs feuilles; Insérer des puces, Cases à cocher et plus encore ...
  • Extrait du texte, Ajouter du texte, Supprimer par position, Supprimer l'espace; Créer et imprimer des sous-totaux de pagination; Conversion entre le contenu et les commentaires des cellules...
  • Super filtre (enregistrer et appliquer des schémas de filtrage à d'autres feuilles); Tri avancé par mois / semaine / jour, fréquence et plus; Filtre spécial par gras, italique ...
  • Combiner des classeurs et des feuilles de travail; Fusionner les tableaux en fonction des colonnes clés; Diviser les données en plusieurs feuilles; Conversion par lots xls, xlsx et PDF...
  • Plus de 300 fonctionnalités puissantes. Prend en charge Office / Excel 2007-2019 et 365. Prend en charge toutes les langues. Déploiement facile dans votre entreprise ou organisation. Essai gratuit de 30 jours. Garantie de remboursement de 60 jours.
onglet kte 201905

Office Tab apporte une interface à onglets à Office et simplifie considérablement votre travail

  • Activer l'édition et la lecture par onglets dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
  • Ouvrez et créez plusieurs documents dans de nouveaux onglets de la même fenêtre, plutôt que dans de nouvelles fenêtres.
  • Augmente votre productivité de 50% et réduit des centaines de clics de souris chaque jour!
bas de cabine
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Tom · 2 months ago
    The code works but imports each text file to a new tab in the workbook.  Any idea where in the code this could be changed to import the new text file on the same worksheet below the data from the last text file?
  • To post as a guest, your comment is unpublished.
    sadiashahbaz57 · 5 months ago
    i need you help i dont have any idea vba excel i want to import multiple text file like 13000. the text file name same as the cell for example (c1=112 so the text file name is also 112) mean the text file 112 is import the c112.
  • To post as a guest, your comment is unpublished.
    sadiashahbaz57 · 5 months ago
    0

    i need you help i dont have any idea vba excel i want to import multiple text file like 13000. the text file name same as the cell for example (c1=112 so the text file name is also 112) mean the text file 112 is import the c112.
  • To post as a guest, your comment is unpublished.
    Martinho · 1 years ago
    Hi, my code runs but only imports the first file. It says there was a method error for copy. The debugger highlights the following line of code. Any ideas?


    xWb.Worksheets(1).Copy after:=xToBook.Sheets(xToBook.Sheets.Count)
    • To post as a guest, your comment is unpublished.
      Lars · 10 months ago
      Hey Martinho,
      I had the same Problem and solved it by changing this line:
      Set xToBook = ThisWorkbook
      to
      Set xToBook = ActiveWorkbook
      Maybe this helps.


  • To post as a guest, your comment is unpublished.
    Heles · 1 years ago
    thanks a lot
    did the job on office 2007 excel
  • To post as a guest, your comment is unpublished.
    Siri_2 · 1 years ago
    is there any chance for taking sheet names only certain part from txt file names?

    as per above code the entire sheet name has been taking.
  • To post as a guest, your comment is unpublished.
    302del · 2 years ago
    Hello, how do you modify this code to insert *.txt files in order: 1,2,3,4,5,6,7,8,9,10,11, etc. Currently code inserts files as follows:1,10,11,12,13,14,15,16,17,18,19,2,20,21, etc. Thanks!
  • To post as a guest, your comment is unpublished.
    pooja · 2 years ago
    hi i want to prevent removing preceding zero's in excel.

    i have tried below code but it is not working


    Sub Test()
    Dim xWb As Workbook
    Dim xToBook As Workbook
    Dim xStrPath As String
    Dim xFileDialog As FileDialog
    Dim xFile As String
    Dim xFiles As New Collection
    Dim I As Long
    Dim j As Long
    Set xFileDialog = Application.FileDialog(msoFileDialogFolderPicker)
    xFileDialog.AllowMultiSelect = False
    xFileDialog.Title = "Select a folder"
    If xFileDialog.Show = -1 Then
    xStrPath = xFileDialog.SelectedItems(1)
    End If
    If xStrPath = "" Then Exit Sub
    If Right(xStrPath, 1) <> "\" Then xStrPath = xStrPath & "\"
    xFile = Dir(xStrPath & "*.txt")
    If xFile = "" Then
    MsgBox "No files found", vbInformation, "Kutools for Excel"
    Exit Sub
    End If
    Do While xFile <> ""
    xFiles.Add xFile, xFile
    xFile = Dir()
    Loop
    Set xToBook = ThisWorkbook
    If xFiles.Count > 0 Then
    For I = 1 To xFiles.Count
    Set xWb = Workbooks.Open(xStrPath & xFiles.Item(I))
    ActiveSheet.Cells.NumberFormat = "@" 'This is to make excel in text format before pasting the text file data
    xWb.Worksheets(1).Copy After:=xToBook.Sheets(xToBook.Sheets.Count)
    On Error Resume Next
    ActiveSheet.Name = xWb.Name
    On Error GoTo 0
    xWb.Close False
    Next
    End If
    End Sub
    • To post as a guest, your comment is unpublished.
      Sunny · 2 years ago
      Pooja, you can try the Remove Leading Zeros function of Kutools for Excel to remove all leading zeros from selection after importing.
      • To post as a guest, your comment is unpublished.
        pooja · 2 years ago
        but I don't want to remove. I want to prevent from removing preceding zero's.
        • To post as a guest, your comment is unpublished.
          Sunny · 2 years ago
          If you want to keep the leading zeros, you can format them as text format by Cell Format.
  • To post as a guest, your comment is unpublished.
    Harsh · 2 years ago
    How would you delete the sheets in vba code if you dont want duplicates on re-executing the module?
    • To post as a guest, your comment is unpublished.
      Sunny · 2 years ago
      Sorry, Harsh, just be carefull to avoid repeatly importing.
  • To post as a guest, your comment is unpublished.
    John · 3 years ago
    Hi, my code runs but only imports the first file. It says there was a method error for copy. The debugger highlights the following line of code. Any ideas?


    xWb.Worksheets(1).Copy after:=xToBook.Sheets(xToBook.Sheets.Count)
    • To post as a guest, your comment is unpublished.
      katie · 2 years ago
      I have the same problem, any solutions found?
      • To post as a guest, your comment is unpublished.
        Madeline · 5 months ago
        Hey katie, 
        I know that your comment is pretty old, but I faced the same problem and fixed it this way:
        The module has to be inserted in a subfolder of the active .xlsx project. I made the mistake of copying the code into a subfolder of my PERSONAL.XLSB where I usually store my macros and it does with my other macros, but not with this one.
  • To post as a guest, your comment is unpublished.
    albeer.mayez@gmail.com · 3 years ago
    The Code is very helpful, it is the only code that i found which gets txt files in bulk the fix that i need on it is also what Joyce and Davinder are after.
    It is to extract the .txt files and paste them all under each other in a specific column lets say column 'N'.

    Also, need to know if it will be possible to add an "if condition" for the .txt files imported to be as follow.
    if the .txt files start with letter 'A' then to be pasted on 'sheet 1' starting with cell 'N2'
    and if the .txt files start with letter 'B' then paste on 'Sheet 2' starting with cell 'N2'
    else MsgBox to be "Unrecognised .txt file purpose".

    thank you in advance
    • To post as a guest, your comment is unpublished.
      Sunny · 3 years ago
      Sorry, my hands are tied
    • To post as a guest, your comment is unpublished.
      albeer.mayez@gmail.com · 3 years ago
      I have this code worked for me but still, I need to change some in it.

      *I want it to paste on the same sheet without opening a new sheet then copy it as it takes longer time.

      *need to insert a conditional if for txt files imported to be pasted on sheet 1 if it starts with letter A and imported to Sheet 2 if it starts with letter B


      Sub testcopy3()
      Dim xWb As Workbook
      Dim xToBook As Workbook
      Dim xStrPath As String
      Dim xFileDialog As FileDialog
      Dim xFile As String
      Dim xFiles As New Collection
      Dim i As Long
      Dim LastRow As Long
      Dim Rng As Range
      Set xFileDialog = Application.FileDialog(msoFileDialogFolderPicker)
      xFileDialog.AllowMultiSelect = False
      xFileDialog.Title = "Select a folder [Kutools for Excel]"
      If xFileDialog.Show = -1 Then
      xStrPath = xFileDialog.SelectedItems(1)
      End If
      If xStrPath = "" Then Exit Sub
      If Right(xStrPath, 1) <> "\" Then xStrPath = xStrPath & "\"
      xFile = Dir(xStrPath & "*.txt")
      If xFile = "" Then
      MsgBox "No files found", vbInformation, "Kutools for Excel"
      Exit Sub
      End If
      Do While xFile <> ""
      xFiles.Add xFile, xFile
      xFile = Dir()
      Loop
      Range("N2").Select
      Set xToBook = ThisWorkbook
      If xFiles.Count > 0 Then
      For i = 1 To xFiles.Count
      Set xWb = Workbooks.Open(xStrPath & xFiles.Item(i))
      xWb.Activate
      'Selecting and copying the txt data
      Range(Selection, Selection.End(xlDown)).Select
      Selection.Copy
      xToBook.Activate
      ActiveSheet.Paste
      Selection.End(xlDown).Offset(1).Select
      On Error Resume Next
      On Error GoTo 0
      xWb.Close False
      Next
      End If
      End Sub
  • To post as a guest, your comment is unpublished.
    Joyce · 3 years ago
    When I run the module as given, it adds each .txt file as a new sheet, not as a new line to the existing sheet. Is there a way to achieve that as the output instead of new sheets for each .txt file?
    • To post as a guest, your comment is unpublished.
      Sunny · 3 years ago
      Do you mean to combine all text file to one sheet?
      • To post as a guest, your comment is unpublished.
        Davinder · 3 years ago
        Yes this is what I want as well.
  • To post as a guest, your comment is unpublished.
    DF Max · 3 years ago
    how to do if my Txt file contain delimited using comma?
    • To post as a guest, your comment is unpublished.
      Sunny · 3 years ago
      You can use Find and Replace fuctuon to replace the comma with space first, and the apply one of above method to convert it to Excel file.
      • To post as a guest, your comment is unpublished.
        Robin · 3 years ago
        Isn't there a way to change this in the code? I'd have to do this with 130 files
        • To post as a guest, your comment is unpublished.
          Igor · 2 years ago
          Same question
  • To post as a guest, your comment is unpublished.
    P B Rama Murty · 4 years ago
    Sub Test()
    'UpdatebyExtendoffice6/7/2016
    Dim xWb As Workbook
    Dim xToBook As Workbook
    Dim xStrPath As String
    Dim xFileDialog As FileDialog
    Dim xFile As String
    Dim xFiles As New Collection
    Dim I As Long
    Set xFileDialog = Application.FileDialog(msoFileDialogFolderPicker)
    xFileDialog.AllowMultiSelect = False
    xFileDialog.Title = "Select a folder [Kutools for Excel]"
    If xFileDialog.Show = -1 Then
    xStrPath = xFileDialog.SelectedItems(1)
    End If
    If xStrPath = "" Then Exit Sub
    If Right(xStrPath, 1) <> "\" Then xStrPath = xStrPath & "\"
    xFile = Dir(xStrPath & "*.txt")
    If xFile = "" Then
    MsgBox "No files found", vbInformation, "Kutools for Excel"
    Exit Sub
    End If
    Do While xFile <> ""
    xFiles.Add xFile, xFile
    xFile = Dir()
    Loop
    Set xToBook = ThisWorkbook
    If xFiles.Count > 0 Then
    For I = 1 To xFiles.Count
    Set xWb = Workbooks.Open(xStrPath & xFiles.Item(I))
    xWb.Worksheets(1).Copy after:=xToBook.Sheets(xToBook.Sheets.Count)
    On Error Resume Next
    ActiveSheet.Name = xWb.Name
    On Error GoTo 0
    xWb.Close False
    Next
    End If
    End Sub

    this code is helping but I want

    tab, semi colon, space true how to do this please help me
    • To post as a guest, your comment is unpublished.
      Sunny · 4 years ago
      Do you want to keep the space(delimiters) after converting the text files to sheets?
      • To post as a guest, your comment is unpublished.
        farzaneh · 4 years ago
        that is my problem too, this code is true. but after convert text files to excel, it doesn't keep the delimiters.
        • To post as a guest, your comment is unpublished.
          Sunny · 4 years ago
          Could you upload the text file and the result you want for me?
          • To post as a guest, your comment is unpublished.
            Des · 3 years ago
            I have the same problem. The txt files are all in separate sheets and the code ignores the space between the two columns
            • To post as a guest, your comment is unpublished.
              Sunny · 3 years ago
              Hello, Des and P B Rama Murty, the below code can split data into columns based on space or tab while importing text file to sheets. You can have a try.

              Sub ImportTextToExcel()
              'UpdatebyExtendoffice20180911
              Dim xWb As Workbook
              Dim xToBook As Workbook
              Dim xStrPath As String
              Dim xFileDialog As FileDialog
              Dim xFile As String
              Dim xFiles As New Collection
              Dim I As Long
              Dim xIntRow As Long
              Dim xFNum, xFArr As Long
              Dim xStrValue As String
              Dim xRg As Range
              Dim xArr
              Set xFileDialog = Application.FileDialog(msoFileDialogFolderPicker)
              xFileDialog.AllowMultiSelect = False
              xFileDialog.Title = "Select a folder [Kutools for Excel]"
              If xFileDialog.Show = -1 Then
              xStrPath = xFileDialog.SelectedItems(1)
              End If
              If xStrPath = "" Then Exit Sub
              If Right(xStrPath, 1) <> "\" Then xStrPath = xStrPath & "\"
              xFile = Dir(xStrPath & "*.txt")
              If xFile = "" Then
              MsgBox "No files found", vbInformation, "Kutools for Excel"
              Exit Sub
              End If
              Do While xFile <> ""
              xFiles.Add xFile, xFile
              xFile = Dir()
              Loop
              Set xToBook = ThisWorkbook
              On Error Resume Next
              Application.ScreenUpdating = False
              If xFiles.Count > 0 Then

              For I = 1 To xFiles.Count
              Set xWb = Workbooks.Open(xStrPath & xFiles.Item(I))
              xWb.Worksheets(1).Copy after:=xToBook.Sheets(xToBook.Sheets.Count)

              ActiveSheet.Name = xWb.Name

              xWb.Close False
              xIntRow = ActiveCell.CurrentRegion.Rows.Count
              For xFNum = 1 To xIntRow
              Set xRg = ActiveSheet.Range("A" & xFNum)
              xArr = Split(xRg.Text, " ")
              If UBound(xArr) > 0 Then
              For xFArr = 0 To UBound(xArr)
              If xArr(xFArr) <> "" Then
              xRg.Value = xArr(xFArr)
              Set xRg = xRg.Offset(ColumnOffset:=1)
              End If
              Next
              End If
              Next
              Next
              End If
              Application.ScreenUpdating = True
              End Sub
              • To post as a guest, your comment is unpublished.
                jayant · 5 months ago
                What changes need to be done if I need tot data into columns based on comma?
              • To post as a guest, your comment is unpublished.
                Jayant · 5 months ago
                What changes needed if want to split data into columns based on comma