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 85 86 87 88 89 90 91 92 93 94
| Public Sub cmdRecupere_Click()
Dim strWB As String, strFile As String
Application.ScreenUpdating = False
Application.EnableEvents = False
' Name of this workbook
strWB = ThisWorkbook.Name
' Recuperation of the 1st workbook of the directory
strFile = Dir("D:\testlist\CMV42" & "\*.html")
' Loop between the 1st and last workbook
Do While strFile <> ""
chemin = "D:\testlist\CMV42" & "\" & strFile
Set Objet = CreateObject("Scripting.FileSystemObject")
Set Fichier = Objet.GetFile(chemin)
' If the name of the workbook doesnt exists in column C
If Fichier.DateLastModified >= UserGuide!I1 And Fichier.DateLastModified <= UserGuide!J1 Then
ElseIf strFile <> strWB And Worksheets("Calcul2").Columns("C").Find(strFile, LookIn:=xlValues, LookAt:=xlWhole) Is Nothing Then
' Open Workbook
Workbooks.Open "D:\testlist\CMV42" & "\" & strFile
' Datas copy
Workbooks(strFile).Worksheets(1).Range("A11:C28").Copy
With Workbooks(strWB).Worksheets("Calcul2")
.Range("A2").Insert xlDown 'insertion en ligne 2
.Range("c2:c19").ClearContents 'on ne garde que les données A2:B17
.Range("C3") = strFile
.Range("C2") = Fichier.DateLastModified
End With
' Close Workbook
Workbooks(strFile).Close
End If
' Next Workbook
strFile = Dir
Loop
' Next Directory
' Recuperation of the 1st workbook of the directory
strFile = Dir("D:\testlist\CMV01" & "\*.html")
' Loop between the 1st and last workbook
Do While strFile <> ""
chemin = "D:\testlist\CMV01" & "\" & strFile
Set Objet = CreateObject("Scripting.FileSystemObject")
Set Fichier = Objet.GetFile(chemin)
' If the name of the workbook doesnt exists in column C
If Fichier.DateLastModified >= UserGuide!I1 And Fichier.DateLastModified <= UserGuide!J1 Then
ElseIf strFile <> strWB And Worksheets("Calcul2").Columns("C").Find(strFile, LookIn:=xlValues, LookAt:=xlWhole) Is Nothing Then
' Open Workbook
Workbooks.Open "D:\testlist\CMV01" & "\" & strFile
' Datas copy
Workbooks(strFile).Worksheets(1).Range("A11:C28").Copy
With Workbooks(strWB).Worksheets("Calcul2")
.Range("A2").Insert xlDown 'insertion en ligne 2
.Range("c2:c19").ClearContents 'on ne garde que les données A2:B17
.Range("C3") = strFile
.Range("C2") = Fichier.DateLastModified
End With
' Close Workbook
Workbooks(strFile).Close
End If
' Next Workbook
strFile = Dir
Loop
Application.EnableEvents = True
Application.ScreenUpdating = True
MsgBox "Le traitement des fichiers est terminé.", vbInformation, "Traitement..."
End Sub |
Partager