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
| Public ListeFe As Worksheet
Public ComparaisonFe As Worksheet
Public Const Liste_NumLigDeb As Integer = 2
Public Const ListeBases_NumLigDeb As Integer = 3
Public Const ListeBases_NumColCodeProjet As Integer = 1
Public Const ListeBases_NumColLast As Integer = 26
Public Const Appli_NumLigMax As Long = 200000
Sub Upload()
' Cette procédure a pour but de concaténer les Listes
Application.Calculation = xlManual
Set ListeFe = ThisWorkbook.Worksheets("Liste")
Set ComparaisonFe = ThisWorkbook.Worksheets("Comparaison")
Repertoire = ThisWorkbook.Path
Nom_Fichier = ThisWorkbook.Name
Liste_NumLig = Liste_NumLigDeb
j = 3
Application.EnableEvents = False
Application.DisplayAlerts = False
' Suppression des lignes du Liste
Range(ListeFe.Cells(Liste_NumLigDeb, 1), ListeFe.Cells(Appli_NumLigMax, ListeBases_NumColLast)).ClearContents
' balayage de tous les fichiers du répertoire
FichS = Dir(Repertoire & "\*.xls*")
While ((FichS <> "") And (Left(FichS, 1) <> "~"))
j = j + 1
'ComparaisonFe.Cells(j, 1).Value = FichS
If (FichS = Nom_Fichier) Then
FichS = Dir
Else
FichS_nom_complet = Repertoire & "\" & FichS
Workbooks.Open FichS_nom_complet
Dim i As Integer
For i = 1 To Worksheets.Count
If (Worksheets(i).Name = "Synthese financiere") Then
ListeBases_NumLig = ListeBases_NumLigDeb
While (Worksheets(i).Cells(ListeBases_NumLig, ListeBases_NumColCodeProjet) <> "")
ListeBases_NumLig = ListeBases_NumLig + 1
Wend
Range(Worksheets(i).Cells(ListeBases_NumLigDeb, 1), Worksheets(i).Cells(ListeBases_NumLig, ListeBases_NumColLast)).Select
Selection.Copy
ListeFe.Activate
Range(ListeFe.Cells(Liste_NumLig, 1), ListeFe.Cells(Liste_NumLig, ListeBases_NumColLast)).Select
ActiveSheet.Paste
Liste_NumLig = Liste_NumLig + ListeBases_NumLig - ListeBases_NumLigDeb
End If
Next i
Workbooks(FichS).Close
FichS = Dir
End If
Wend
Application.EnableEvents = True
Application.DisplayAlerts = True
Application.Calculation = xlAutomatic
End Sub |
Partager