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
|
Sub openAndTransfomTheExtract(ByVal extractFileName As String, ByVal extractionDataSheetName As String, ByRef extractionWB As Workbook)
'Ouvre le fichier cible
extractFileName = Application.GetOpenFilename()
Set extractionWB = Workbooks.Open(Filename:=extractFileName)
'Convertir l'extraction au format attendu et supposé être immuable:
'Supprimer l'image, les 3+1 lignes, duppliquer la colonne "Création" en "Création2", créer Tableau1, affecter formule à "Création2"
'With Sheets(extractionDataSheetName)
With extractionWB.Worksheets(extractionDataSheetName)
'Step1: delete img
.Shapes.Range(Array("Picture 1")).Delete
'Step2: delete 3 first lines
.Rows("1:3").Delete Shift:=xlUp
'Step3: delete last line
'.Rows(.Cells(Columns(1).Cells.Count, 1).End(xlUp).Row).Clear
.Rows(.Cells(Columns(1).Cells.Count, 1).End(xlUp).Row).Delete
'Step4: Duplicate column E
'.Range("E:E").Select
''.Columns("E:E").Select
''Selection.Copy
'.Column("E:E").Insert
'.Range("E:E").Copy [E1] 'Shift:=xlToRight
'extractionWB.Worksheets.FillAcrossSheets Range("E:E"), xlFillWithAll
'.Columns("E:E").Copy .Columns("E:E")
''Selection.Insert Shift:=xlToRight
Application.CutCopyMode = False
'Step5: Rename Column F
.Range("F1").FormulaR1C1 = "Création2"
'Step6: create Tableau1
.ListObjects.Add(xlSrcRange, .UsedRange(), , xlYes).Name = _
"Tableau1"
'Step7: affecter la formule à la colonne "Création2"
.Range("F2").Select
ActiveCell.FormulaR1C1 = _
"=CONCATENATE(YEAR( [@Création]),""/"",IF(MONTH([@Création])<10,""0"",""""),MONTH([@Création]))"
End With
End Sub |
Partager