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
|
...
Set ExcelApplication = CreateObject("Excel.Application")
' Rend Excel visible au travers de l'objet Application
ExcelApplication.Application.Visible = False
ExcelApplication.Workbooks.Open FileName:=sFichierXls
With ExcelApplication
' Insertion d'une ligne vide au début
' .Rows("1:1").Select
' .Selection.Insert
'Mise en forme des colonnes numériques
.Columns("I:I").NumberFormat = "0.00%"
.Columns("M:P").NumberFormat = "0.00"
'Insertion des 3 colonnes calculées
.Columns("Q:S").Select
.Selection.Insert Shift:=-4161
'Ajout des règles de calculs
.Range("Q2").FormulaR1C1 = "=IF(RC[-9]=0,0,RC[-4]/RC[-9])"
.Range("R2").FormulaR1C1 = "=IF((RC[-8]+RC[-6])=0,0,RC[-4]/(RC[-8]+RC[-6]))"
.Range("S2").FormulaR1C1 = "=IF((RC[-8]+RC[-7])=0,0,RC[-4]/(RC[-8]+RC[-7]))"
'Recherche de la dernière ligne
.ActiveSheet.cells(1, 1).Select
.Selection.End(-4121).Select
iDernierLigne = .ActiveCell.Row
'Recopie des formules de calcul sur toutes les lignes
.Range("Q2:S2").Select
.Selection.AutoFill Destination:=.Range(.cells(2, 17), .cells(iDernierLigne, 19))
.Range("Q1").Select
'Ajout des titres sur les colonnes calculées
.ActiveSheet.cells(1, 17) = "Average Value per Subscriber (k)"
.ActiveSheet.cells(1, 18) = "Average Value per Subscriber (Classic) (k)"
.ActiveSheet.cells(1, 19) = "Average Value per Subscriber (Share+) (k)"
.ActiveSheet.cells(1, 1).Select
'*****************************
'Mise en page globale
'*****************************
.Rows("1:1").Select
With .Selection
.HorizontalAlignment = -4108
.VerticalAlignment = -4108
.WrapText = True
End With
.cells.Select
.cells.EntireColumn.AutoFit
.Columns("I:T").ColumnWidth = 11
'Ajout des bordures
.Rows("1:1").Select
.Range(.Selection, .ActiveCell.SpecialCells(11)).Select
For i = 7 To 12
BordureTab ExcelApplication, i
Next i
With .ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = "Page &P"
.RightFooter = ""
.LeftMargin = ExcelApplication.Application.InchesToPoints(0.2)
.RightMargin = ExcelApplication.Application.InchesToPoints(0.2)
.TopMargin = ExcelApplication.Application.InchesToPoints(0.2)
.BottomMargin = ExcelApplication.Application.InchesToPoints(0.2)
.HeaderMargin = ExcelApplication.Application.InchesToPoints(0)
.FooterMargin = ExcelApplication.Application.InchesToPoints(0)
.PrintHeadings = False
.PrintGridlines = False
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = 2
.Draft = False
.PaperSize = 9
.Zoom = 50
End With
.ActiveSheet.cells(1, 1).Select
End With
... |
Partager