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.