1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84
| Private Sub CommandButton1_Click()
Dim FSO As New FileSystemObject
Dim Fo As Folder
Dim Fi As File
Dim c As Range
Dim Fl As Worksheet, Fl2 As Worksheet
Dim Wb As Workbook
Dim strSearch(1) As String, NomFich As String
Set Fl = ThisWorkbook.Worksheets(1)
strSearch(0) = Fl.Range("A2").Value: strSearch(1) = Fl.Range("A3")
For Each c In Fl.Range("B2:B" & Fl.Range("B1").SpecialCells(xlCellTypeLastCell).Row).Cells
If Fl.Range("D" & c.Row) = "oui" Or Fl.Range("E" & c.Row) = "oui" Then
d = 0
UserForm1.Label1.Caption = ""
UserForm1.Label2.Caption = ""
UserForm1.Label3.Caption = "Les fichiers suivants vont être copiés vers :" & Chr(10) & Fl.Range("C" & c.Row).Characters(55, Fl.Range("C" & c.Row).Characters.Count).Caption
UserForm1.Label5.Caption = 0
With Application.FileSearch
.NewSearch
.LookIn = c.Value
.SearchSubFolders = True
.Filename = "*" & strSearch(0) & "*" & strSearch(1) & "*"
If .Execute > 0 Then
Copie_Impression:
If UserForm1.Label5.Caption = 1 Then
Application.ScreenUpdating = False
UserForm1.Label5.Caption = 2
For Each Fo In FSO.GetFolder(Fl.Range("C" & c.Row).Value).SubFolders
If Fo.Path = Fl.Range("C" & c.Row).Value & "\" & strSearch(0) & " " & Left(strSearch(1), 8) Then
d = 1
End If
Next Fo
If Not d = 1 Then
MkDir Fl.Range("C" & c.Row).Value & "\" & strSearch(0) & " " & Left(strSearch(1), 8)
End If
End If
For i = 1 To .FoundFiles.Count
If Fl.Range("D" & c.Row) = "oui" Then
If UserForm1.Label5.Caption = 0 Then
NomFich = StrReverse(Left(StrReverse(.FoundFiles(i)), InStr(StrReverse(.FoundFiles(i)), "\") - 1))
If i = 1 Then
UserForm1.Label1.Caption = NomFich
Else
UserForm1.Label1.Caption = UserForm1.Label1.Caption & Chr(10) & NomFich
End If
Else
Set Fi = FSO.GetFile(.FoundFiles(i))
Fi.Copy Fl.Range("C" & c.Row).Value & "\" & strSearch(0) & " " & Left(strSearch(1), 8) & "\" & Fi.Name
End If
End If
If Fl.Range("E" & c.Row) = "oui" Then
If UserForm1.Label5.Caption = 0 Then
If i = 1 Then
UserForm1.Label2.Caption = NomFich
Else
UserForm1.Label2.Caption = UserForm1.Label1.Caption & Chr(10) & NomFich
End If
Else
Set Fi = FSO.GetFile(.FoundFiles(i))
Set Wb = Workbooks.Open(Fi.Path)
For Each Fl2 In Wb.Worksheets
Fl2.PrintOut
Next Fl2
Wb.Close
End If
End If
Next i
If UserForm1.Label5.Caption = 0 Then UserForm1.Show
'--------------------------------------------------------------------------
'En cliquant sur Ok du UserForm1, on passe UserForm1.Label5.Caption à 1 et on ferme le UserForm1
'En cliquant sur Annuler, on ferme le UserForm1 sans changer UserForm1.Label5.Caption
'--------------------------------------------------------------------------
If UserForm1.Label5.Caption = 1 Then GoTo Copie_Impression
If UserForm1.Label5.Caption = 2 Then Application.ScreenUpdating = True
End If
End With
End If
Next c
End Sub |
Partager