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 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170
| Option Explicit
Public Continuer As Boolean
Public Symptome As Boolean
Public Section As Boolean
Public Mdate As Boolean
Public FichierAOuvrir As Variant
Public Wb As Workbook
Public MonFichier As String
Public MonRepertoire As String
Sub OuvrirFichierExcelALOuverture()
Continuer = False
With UserForm2
.Show
End With
If Continuer = False Then Exit Sub
OuvertureFichiers MonRepertoire, MonFichier
End Sub
Sub OuvertureFichiers(RepertoireFichier, NomFichier)
If Continuer = True And Symptome = True Then
For Each Wb In Workbooks
Select Case Wb.Name
Case NomFichier
Wb.Activate
Continuer = False
Exit For
End Select
Next Wb
If Continuer = True Then Workbooks.Open Filename:=RepertoireFichier & "\" & NomFichier
Workbooks(NomFichier).Sheets(1).Copy Before:=Workbooks("essai.xlsm").Sheets(1)
Sheets(1).Activate
Cells.Style = "Normal"
Columns("J:J").Insert Shift:=xlToRight
Call formule
Worksheets("Sheet1").Name = "Feuil1"
Workbooks(NomFichier).Close False
Call Convertion
Call nombre
Sheets("ISY").Activate
ActiveSheet.Unprotect ""
Sheets("Feuil1").Range("Q3:Q1000" & _
Range("A65535").End(xlUp).Row).Name = "ISY"
Sheets("Feuil1").Range("J3:J1000" & _
Range("A65535").End(xlUp).Row).Name = "Difference"
Sheets("Feuil1").Range("F3:F1000" & _
Range("A65535").End(xlUp).Row).Name = "Date"
Sheets("Feuil1").Range("M3").Name = "Nom"
Sheets("Feuil1").Range("Q1:Q1000").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Range("A3"), Unique:=True
Sheets("ISY").Range("A4:A45").Select
Call MEF
Call graph
Sheets("ISY").Activate
ActiveSheet.Protect ""
End If
If Continuer = True And Mdate = True Then
For Each Wb In Workbooks
Select Case Wb.Name
Case NomFichier
Wb.Activate
Continuer = False
Exit For
End Select
Next Wb
If Continuer = True Then Workbooks.Open Filename:=RepertoireFichier & "\" & NomFichier
Workbooks(NomFichier).Sheets(1).Copy Before:=Workbooks("essai.xlsm").Sheets(1)
Sheets(1).Activate
Worksheets("Sheet1").Name = "Feuil1"
Workbooks(NomFichier).Close False
Sheets("GraphMdate").Activate
Sheets("Feuil1").Range("J3:J3000" & _
Range("A65535").End(xlUp).Row).Name = "difference"
Sheets("Feuil1").Range("J3:J3000").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Range("A2"), Unique:=True
Range("A2:A1000").Select
Selection.TextToColumns Destination:=Range("A2"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Call Trier
End If
End Sub
Sub formule()
Dim nbLigne As Integer, nbColonne As Integer, i As Integer
nbLigne = Cells.SpecialCells(xlCellTypeLastCell).Row
nbColonne = Range("J3").End(xlToLeft).Column
For i = 3 To nbLigne
If Not Range("H" & i).Value = "" Then Range("J" & i).Formula = "=(I" & i & "-h" & i & ")"
Next
End Sub
Sub Convertion()
Dim i As Integer
For i = 0 To 3
CONVERTIR Worksheets("Feuil1").Columns("H:H").Offset(, i)
Next i
End Sub
Private Sub CONVERTIR(ByVal Plage As Range)
Plage.TextToColumns Destination:=Plage.Cells(1, 1), DataType:=xlDelimited, FieldInfo:=Array(1, 5)
End Sub
Sub graph()
Sheets("GraphSy").Activate
Sheets("Feuil1").Range("Q1:Q1000").AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=Range("A1"), Unique:=True
End Sub
Sub nombre()
Range("F3:F1000").Select
Selection.TextToColumns Destination:=Range("F3"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
End Sub
Sub Trier()
ActiveWorkbook.Worksheets("GraphMdate").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("GraphMdate").Sort.SortFields.Add Key:=Range("A2"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("GraphMdate").Sort
.SetRange Range("A2:A1000")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub
Sub MEF()
With ActiveCell
.Borders.Weight = xlThin
With .Font
.Bold = False
.Size = 8
.Italic = True
.Name = "Arial"
End With
End With
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 9148836
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End Sub |
Partager