I'm working on a report of quality control. I've got a table, with all the points of control inside, and each time, when I get a new test result, Excel should fill this table. My code already works, but slowly (I have 15000 lines in this table, and it takes me 4 minutes to fill it), so I wish I can accelerate a little bit.

Here is my general idea:

The results of one test is composted by nearly 3000 .txt files, and I have a list L which contains the names of all these files that I should open and import the data into Excel. I open the list L, and open the .txt file one by one, to import the results into a sheet "Brouillon" all at once, then fill the table with its help. I think it could be faster.

'to chose the list L
Private Sub Button_Parcourir_Click()

With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = False
.InitialFileName = "import_excel.txt"
.Show
listPath = .SelectedItems(1)
End With
TextBox1.Text = listPath
End Sub

Private Sub Button_Importer_Click()

'create a sheet named "Brouillon":
Sheets.Add After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = "Brouillon"
Range("A1").Select

'open the list L and import the data into "Brouillon":
list_de_Controle = "TEXT;" & listPath
Open listPath For Input As #1
Do While Not EOF(1)
    Line Input #1, nom_de_Fich
    mfile = Dir(nom_de_Fich & "*.*")

    If mfile <> "" Then
        Open nom_de_Fich For Input As #2
        Fich_dansList = Fich_dansList & nom_de_Fich & "|"
        Inserer_contenu
        Close #2
    End If
Loop
Close #1

'count how many lines and columns we have in "Data":
Worksheets(Array(1)).Select
Range("A3").Select
ActiveCell.End(xlDown).Select
ActiveCell.End(xlToRight).Select
ligne_Data = Selection.Row
ma_Colonne = Selection.Column + 1

'count how many lines and columns we have in "Brouillon":
Count_Brouillon
'put a flag to reduce the loop
marque_ligneBrouillon = 1

'for each line in "Data":
For i = 4071 To ligne_Data
    'find which file I should open (mon_objet):
    mon_objet = Sheets("Data").Cells(i, 15).Text        
    'begin to search in "Brouillon" (from the last time where it stopped):
    For j = marque_ligneBrouillon To ligne_Brouillon
        'check the file:
        If InStr(Sheets("Brouillon").Cells(j, 1).Text, mon_objet) <> 0 Then
            'put this file in list:
            opened_Fich = opened_Fich & Sheets("Brouillon").Cells(j, 1).Text & "|"                
            'check the zone:
            If InStr(Sheets("Brouillon").Cells(j, 2).Text, Sheets("Data").Cells(i, 18).Text) <> 0 _
            Or InStr(Sheets("Data").Cells(i, 18).Text, "/") <> 0 Then

                'in the correct line in "Brouillon" from the beginning till the end:
                For k = 2 To colonne_Brouillon

                    'check the control whose result I'm looking for:
                    If InStr(Sheets("Brouillon").Cells(j, k).Text, Sheets("Data").Cells(i, 20).Text) <> 0 Then

                        'if this cell contains the result:
                        '1. put the mark here
                        marque_ligneBrouillon = j
                        '2. put the result inside:
                        mon_Data = Sheets("Brouillon").Cells(j, k).Text
                        Sheets("Data").Cells(i, ma_Colonne).Value = mon_Data
                        Flag_j = True
                        Exit For
                    End If

                Next k

            End If

        End If
        If Flag_j Then Exit For

    Next j
    Flag_j = False

Next i

Dim nb_Unique As Long
'because one txt.file could be opened a several times, so I want to remove the duplicate lines:
BList = Split(opened_Fich, "|")
With Worksheets("Brouillon")
    .Range("A1").Resize(UBound(BList)).Value = Application.Transpose(BList)
End With
Sheets("Brouillon").Columns(1).RemoveDuplicates Columns:=Array(1), Header:=xlNo
nb_Unique = Sheets("Brouillon").Columns(1).SpecialCells(xlCellTypeConstants, xlTextValues).Count
Debug.Print nb_Unique

'then put list L after all these files:
AList = Split(Fich_dansList, "|")
With Worksheets("Brouillon")
    .Range("A1").End(xlDown).Offset(1).Resize(UBound(AList)).Value = Application.Transpose(AList)
End With
'then remove again all the duplicate parts:
Sheets("Brouillon").Columns(1).RemoveDuplicates Columns:=Array(1), Header:=xlNo
'so the ones that haven't been opened are listed after those have been opened:
nb_total = Sheets("Brouillon").Columns(1).SpecialCells(xlCellTypeConstants, xlTextValues).Count 
End Sub

Then I'll do the nearly the same thing to put all these haven't been opened files at the end of the table. Because they are the new tests points that have been just added into the test, there is no correct 'place' for them, and I have to complete and improve the table.

Here I've made a little bit change

I've tried to modified the name into English (I hope it's clear enough for you?), and add some functions to reduce the complicity of the whole Sub.

Private Sub WriteName(Optional ByVal file As String)

ActiveCell.Value = file
ActiveCell.Offset(0, 1).Select

End Sub

Private Sub ImportFile(Optional ByVal file As String)

Dim Onefile As Long
Open file For Input As #1

    Dim keyWord As String
    keyWord = "Zone"
    WriteName file
    
    Dim Content As String
    Do While Not EOF(1)
        Line Input #1, Content
        'if it's not a new zone
        If InStr(Content, keyWord) < 1 Then
            ActiveCell = Content
        'if it's a new zone
        Else
           'goto next row  <<===========Here's the problem
            ActiveCell.Offset(1, 0).Select
            ActiveCell.End(xlToLeft).Select
            WriteName
            ActiveCell = Content
        End If
        ActiveCell.Offset(0, 1).Select
    Loop
   'remove the last cell and goto next row  <<=============Problem
    ActiveCell.Offset(0, -1).Select
    ActiveCell.Clear
    ActiveCell.Offset(1, 0).Select
    ActiveCell.End(xlToLeft).Select

Close #1

End Sub

Public Sub Count_Brouillon()
ligne_Brouillon = Sheets("Brouillon").UsedRange.Rows.Count
colonne_Brouillon = Sheets("Brouillon").UsedRange.Columns.Count
End Sub

It's a little bit too long, so I've tried to add some comments to help you to read. If you've got some ideas to improve and accelerate the program, or something I've not good explained, please leave a comment.