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 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116
| Option Explicit
Public Const Duree = 10 'secondes
Sub maj()
UserForm1.Show
Application.OnTime Now + TimeValue("0:0:01"), "MiseAJour"
calcul
End Sub
Sub calcul()
Application.ScreenUpdating = False
Dim fso As New FileSystemObject
Dim fich As file
Dim Rep As Folder
Dim Wk As Variant
Dim TabFich() As String
Dim CL1 As Workbook, CL2 As Workbook
Dim FL1 As Worksheet, FL2 As Worksheet
Dim Premlig As Long, Derlig As Long
Dim Nom As String
Dim i As Integer
Dim dmae As String
Set CL1 = ThisWorkbook
Set FL1 = CL1.Worksheets("Sommaire")
Set Rep = fso.GetFolder(Application.ThisWorkbook.Path & "\Thèmes")
FL1.Unprotect dmae
FL1.Range("6:70").Locked = False
FL1.Range("A74:K65536").Locked = False
FL1.Rows("74:65536").Delete
For Each fich In Rep.Files
Wk = Rep & "\" & fich.Name
If fso.GetExtensionName(Wk) = "xls" Then
CL1.Save
Premlig = FL1.Range("A65535").End(xlUp).Row + 1
Set CL2 = Workbooks.Open(Wk)
Set FL2 = CL2.Worksheets("sommaire")
Nom = Left(fich.Name, Len(fich.Name) - 4)
If Nom = "Air" Or Nom = "Bruit" Or Nom = "Divers" Or Nom = "Hygiène - Sécurité" Or _
Nom = "ICPE" Or Nom = "Sites et Sols Pollués" Or Nom = "Substances Radioactives" Then
If Not FL2.Range("A15").Value = "" Then
FL2.Range("A15:" & FL2.Range("A1"). _
SpecialCells(xlCellTypeLastCell).Address).Copy _
Destination:=FL1.Range("B" & FL1.Range("B1"). _
SpecialCells(xlCellTypeLastCell).Row + 1)
Derlig = FL1.Range("A1").SpecialCells(xlCellTypeLastCell).Row
For i = Premlig To Derlig
FL1.Cells(i, 1).Value = Left(fich.Name, Len(fich.Name) - 4)
Next
End If
Else
If Not FL2.Range("A17").Value = "" Then
FL2.Range("A17:" & FL2.Range("A1"). _
SpecialCells(xlCellTypeLastCell).Address).Copy _
Destination:=FL1.Range("B" & FL1.Range("B1"). _
SpecialCells(xlCellTypeLastCell).Row + 1)
Derlig = FL1.Range("A1").SpecialCells(xlCellTypeLastCell).Row
For i = Premlig To Derlig
FL1.Cells(i, 1).Value = Left(fich.Name, Len(fich.Name) - 4)
Next
End If
End If
CL2.Save
CL2.Close
End If
Next
If Not FL1.Range("A74").Value = "" Then
With FL1.Range("A74:" & "K" & FL1.Range("A1").SpecialCells(xlCellTypeLastCell).Row)
.VerticalAlignment = xlVAlignCenter
'.HorizontalAlignment = xlHAlignCenter
.Borders(xlEdgeLeft).LineStyle = xlContinuous
.Borders(xlEdgeTop).LineStyle = xlContinuous
.Borders(xlEdgeBottom).LineStyle = xlContinuous
.Borders(xlEdgeRight).LineStyle = xlContinuous
.Borders(xlInsideVertical).LineStyle = xlContinuous
If FL1.Range("a1").SpecialCells(xlCellTypeLastCell).Row - 73 > 1 Then
.Borders(xlInsideHorizontal).LineStyle = xlContinuous
End If
End With
FL1.Range("A74:" & "A" & FL1.Range("A1").SpecialCells(xlCellTypeLastCell).Row).HorizontalAlignment = xlHAlignCenter
End If
FL1.Range("A74:K65536").Locked = True
FL1.Protect dmae, UserInterfaceOnly:=True, AllowFiltering:=True
Set Rep = Nothing
Set FL1 = Nothing
Set CL1 = Nothing
Set FL2 = Nothing
Set CL2 = Nothing
Application.ScreenUpdating = True
End Sub
Sub Demarrer()
Application.OnTime Now + TimeValue("0:0:01"), "MiseAJour"
End Sub
Sub MiseAJour()
If UserForm1.ProgressBar1.Value = Duree Then
Unload UserForm1
Exit Sub
Else
UserForm1.ProgressBar1.Value = UserForm1.ProgressBar1.Value + 1
End If
Call Demarrer
End Sub |
Partager