Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre questionSub test() Dim v_fichier As String Dim v_i As Byte v_i = 1 v_fichier = Dir("C:\Projet\RAF\RAF" & v_i & ".txt") While v_fichier <> "" Workbooks.OpenText Filename:= _ "C:\Projet\RAF" & v_fichier Workbooks.OpenText Filename:= _ "d:\Users\ys.EMEA\Desktop\NIR2.txt" 'Columns("A1:A1").Select Selection.NumberFormat = "0" Sheets.Add ActiveSheet.Name = "Resultat" For j = 2 To 15 valeur = Workbooks("NIR2.txt").Sheets("NIR2").Cells(j, 1) Workbooks(v_fichier).Activate Set celluletrouvee = Range("A:A").Find(valeur) If Not celluletrouvee Is Nothing Then ligne = celluletrouvee.Row i = 1 Do Workbooks("NIR2.txt").Sheets("Resultat").Cells(i, 1) = Workbooks(v_fichier).Sheets("RAF" & v_i).Cells(ligne, 1) i = i + 1 ligne = ligne + 1 Loop Until (Left(Workbooks(v_fichier).Sheets("RAF" & v_i).Cells(ligne, 1), 14)) = "S30.G01.00.001" End If Next v_i = v_i + 1 v_fichier = Dir("C:\Projet\RAF\RAF" & v_i & ".txt") Wend End MsgBox "fini" Exit Sub faute: Stop End Sub
Option Explicit Sub TraiterRepertoire(ByRef nomfic_NIR2 As String, ByRef repertoire_RAF As String) Dim FSO As New FileSystemObject Dim fldr_RAF As Folder Dim file_RAF As File Set fldr_RAF = FSO.GetFolder("C:\Projet\RAF") For Each file_RAF In fldr_RAF.Files If UCase(file_RAF.Name) Like "RAF*" Then TraiterFichierRAF nomfic_NIR2, file_RAF.Path Next End Sub Sub TraiterFichierRAF(ByRef nomfic_NIR2 As String, ByRef nomfic_RAF As String) Dim wb_RAF As Workbook Dim wb_NIR2 As Workbook Dim ws_RAF As Worksheet Dim ws_NIR2 As Worksheet Dim ws_resultat As Worksheet Dim cel_cherchee As Range Dim cel_trouvee As Range Dim cel_resultat As Range Workbooks.OpenText Filename:="d:\Users\ys.EMEA\Desktop\NIR2.txt" Set wb_NIR2 = ActiveWorkbook Set ws_NIR2 = wb_NIR2.Worksheets(1) ws_NIR2.Columns(1).NumberFormat = "0" Workbooks.OpenText Filename:="c:\Projet\RAF\RAF*" Set wb_RAF = ActiveWorkbook Set ws_RAF = wb_RAF.Worksheets(1) Set ws_resultat = wb_NIR2.Worksheets.Add ws_resultat.Name = "Resultat" Set cel_resultat = ws_resultat.Range("A1") For Each cel_cherchee In ws_NIR2.Range("A2:A7") Set cel_trouvee = ws_RAF.Columns(1).Find(cel_cherchee.Value) Do cel_resultat.Value = cel_trouvee.Value Set cel_resultat = cel_resultat.Offset(1) Set cel_trouvee = cel_trouvee.Offset(1) ligne = ligne + 1 Loop Until (Left(cel_trouvee.Text, 14)) = "S30.G01.00.001" End If Next End Sub Sub start() Dim nomfic_NIR2 As String Dim nomfic_RAF As String Dim repertoire_RAF As String Call TraiterRepertoire(nomfic_NIR2, repertoire_RAF) Call TraiterFichierRAF(nomfic_NIR2, nomfic_RAF) End Sub
Do cel_resultat.Value = cel_trouvee.Value Set cel_resultat = cel_resultat.Offset(1)