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
| Sub auto_open()
Dim book As Excel.Workbook
Dim excelfile As String
Dim Myrep(0 To 100) As String
Myrep(0) = "\\serveur1\partage\rep1\rep2\Biggest_Files.xls"
Myrep(1) = "\\serveur2\partage\rep1\rep2\Biggest_Files.xls"
While Myrep(i) <> ""
Set appxls = CreateObject("Excel.Sheet")
appxls.Application.Workbooks.Open Myrep(i)
Rows("1:4").Select
Range("A4").Activate
Selection.Delete Shift:=xlUp
Columns("G:G").Select
Selection.Delete Shift:=xlToLeft
Range("A1:F1").Select
Range("F1").Activate
Selection.Font.Bold = True
Range("H1").Select
ActiveCell.FormulaR1C1 = "TOTAL (Mo)"
With ActiveCell.Characters(Start:=1, Length:=10).Font
.Name = "Arial"
.FontStyle = "Gras"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("H3").Select
ActiveCell.FormulaR1C1 = "TOTAL (Go)"
Columns("A:F").Select
Range("F1").Activate
Selection.AutoFilter
Range("H3").Select
Selection.Font.Bold = True
Columns("A:F").Select
Range("F1").Activate
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("H1:I1,H3:I3").Select
Range("I3").Activate
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Columns("C:C").Select
Selection.Replace What:=" MB", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.TextToColumns Destination:=Range("C1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Selection.NumberFormat = "0.0"
Columns("D:D").Select
Selection.TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 4), TrailingMinusNumbers:=True
Selection.NumberFormat = "m/d/yyyy"
Columns("C:D").Select
Selection.Sort Key1:=Range("C2"), Order1:=xlDescending, Key2:=Range("D2") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal
Range("I1").Select
Selection.NumberFormat = "0"
Range("I3").Select
Selection.NumberFormat = "0.0"
Range("I1").Select
ActiveCell.FormulaR1C1 = "=SUM(C[-6])"
Range("I3").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-2]C/1000)"
Columns("A:A").ColumnWidth = 33.57
Columns("B:B").ColumnWidth = 33.71
Range("C1").Select
ActiveCell.FormulaR1C1 = "Size (Mo)"
Columns("D:D").Select
Columns("C:C").EntireColumn.AutoFit
Columns("D:D").EntireColumn.AutoFit
Columns("E:E").ColumnWidth = 22.14
Columns("F:F").EntireColumn.AutoFit
Columns("C:D").Select
Selection.Sort Key1:=Range("C2"), Order1:=xlDescending, Key2:=Range("D2") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal
Range("A1").Select
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=Myrep(i) _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
ActiveWorkbook.Close
Application.DisplayAlerts = True
i = i + 1
Wend
Excel.Application.Quit
Set appxls = Nothing
End Sub |
Partager