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
| Sub leaReader(ByRef exportArray() As Variant)
Dim i!, j!, k!, l!, m!, S!, Chemin$, Code$, fName$, namSh$, Sh As Object, ch As Object, n As Object
Dim tabCopy() As String
Dim rw As Object
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False
For j = 1 To UBound(exportArray, 1)
If (exportArray(j, 1) = "Recap") * 1 + (exportArray(j, 1) = "P&L Summary") * 1 + (exportArray(j, 1) = "P&L Detail") * 1 + (exportArray(j, 1) = "Budget") * 1 < 0 Then
exportArray(j, 3) = 3
ElseIf (exportArray(j, 1) = "Version") * 1 + (exportArray(j, 1) = "FTE") * 1 + (exportArray(j, 1) = "Space") * 1 + (exportArray(j, 1) = "CapEx") * 1 + (exportArray(j, 1) = "SUC") * 1 + (exportArray(j, 1) = "ABC") * 1 < 0 Then
exportArray(j, 3) = 2
Else
exportArray(j, 3) = 1
End If
Next j
Set wbExport = Workbooks.Add
'Nombre d'onglets vierge livrés avec wbExport
m = wbExport.Sheets.Count
'Initialiser la liste des onglets à copier
l = 0
For j = 1 To UBound(exportArray, 1)
If exportArray(j, 1) <> "" Then
l = l + 1
ReDim Preserve tabCopy(1 To l)
tabCopy(l) = exportArray(j, 1)
End If
Next
'Copier les onglets
wbNEW.Activate
Sheets(tabCopy).Copy after:=wbExport.Sheets(wbExport.Sheets.Count)
Application.CutCopyMode = False
'Enregistrer wbExport
Do
fName = Application.GetSaveAsFilename("ExportLEA", "Excel(*.xls),*.xls")
Loop Until fName <> ""
wbExport.SaveAs fileName:=fName
'Réinitialiser la variable wbExport (peut être pas nécessaire)
Set wbExport = Nothing
Set wbExport = ActiveWorkbook
'Suppression des MFC, des fusions de cellule et des graphiques
For Each Sh In wbExport.Sheets
Sh.Activate
ActiveSheet.UsedRange.Select
Selection.FormatConditions.Delete
Selection.MergeCells = False
For Each n In ActiveSheet.Names
n.Delete
Next n
For Each ch In ActiveSheet.ChartObjects
ch.Activate
ActiveChart.ChartArea.Select
ActiveWindow.Visible = False
Selection.Delete
Next ch
Next Sh
'noms
i = 0
For Each n In ActiveWorkbook.Names
n.Delete
Next n
For j = 1 To UBound(tabCopy)
namSh = exportArray(j, 1)
If exportArray(j, 3) > 0 Then
If exportArray(j, 3) < 3 Then
wbExport.Worksheets(namSh).Activate
With ActiveSheet
.UsedRange.Select
Selection.Value = Selection.Value
.Tab.ColorIndex = 55
If exportArray(j, 3) = 1 Then
.Tab.ColorIndex = 49
End If
End With
Else
wbExport.Worksheets(namSh).Tab.ColorIndex = 6
End If
End If
Next j
For S = 1 To m
Sheets(1).Delete
Next S
'Ajout du code couleur
Code = ""
Code = Code & "Private Sub Workbook_Open()" & vbCrLf
Code = Code & "ActiveWorkbook.Colors(49) = RGB(196, 207, 230)" & vbCrLf
Code = Code & "ActiveWorkbook.Colors(11) = RGB(158, 176, 214)" & vbCrLf
Code = Code & "ActiveWorkbook.Colors(55) = RGB(45, 110, 176)" & vbCrLf
Code = Code & "ActiveWorkbook.Colors(5) = RGB(45, 110, 176)" & vbCrLf
Code = Code & "ActiveWorkbook.Colors(47) = RGB(221, 221, 221)" & vbCrLf
Code = Code & "ActiveWorkbook.worksheets(1).activate" & vbCrLf
Code = Code & "End Sub"
wbExport.VBProject.VBComponents("Thisworkbook").CodeModule.InsertLines 1, Code
With wbExport.CustomDocumentProperties
.Add Name:="Source", LinkToContent:=False, Type:=msoPropertyTypeString, Value:=wbNEW.FullName
.Add Name:=wbGAM.Sheets(csWSFORMS).Cells(362, wbGAM.Sheets(csWSOPTIONS).Cells(1, 2).Value).Value, LinkToContent:=False, Type:=msoPropertyTypeString, Value:=InputBox(wbGAM.Sheets(csWSFORMS).Cells(360 _
, wbGAM.Sheets(csWSOPTIONS).Cells(1, 2).Value).Value, wbGAM.Sheets(csWSFORMS).Cells(362, wbGAM.Sheets(csWSOPTIONS).Cells(1, 2).Value).Value, Environ("USERNAME"))
.Add Name:=wbGAM.Sheets(csWSFORMS).Cells(363, wbGAM.Sheets(csWSOPTIONS).Cells(1, 2).Value).Value, LinkToContent:=False, Type:=msoPropertyTypeString, Value:=InputBox(wbGAM.Sheets(csWSFORMS).Cells(361 _
, wbGAM.Sheets(csWSOPTIONS).Cells(1, 2).Value).Value, wbGAM.Sheets(csWSFORMS).Cells(363, wbGAM.Sheets(csWSOPTIONS).Cells(1, 2).Value).Value, "")
.Add Name:=wbGAM.Sheets(csWSFORMS).Cells(364, wbGAM.Sheets(csWSOPTIONS).Cells(1, 2).Value).Value, LinkToContent:=False, Type:=msoPropertyTypeDate, Value:=Date
End With
Application.DisplayAlerts = True
Application.ScreenUpdating = True
wbExport.Save
wbExport.Close
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub |
Partager