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
| Public Sub Go()
'Déclaration des variables
Dim appExcel As Excel.Application 'Application Excel
Dim wbExcel As Excel.Workbook 'Classeur Excel
Dim wsExcel As Excel.Worksheet 'Feuille Excel
'Ouverture de l'application
Set appExcel = CreateObject("Excel.Application")
'Ouverture d'un fichier Excel
Workbooks.OpenText FileName:=App.Path & "\rq.txt", Origin:=xlWindows, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlNone, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
'
Set wbExcel = appExcel.ActiveWorkbook
Set wsExcel = wbExcel.ActiveSheet
ConstruitTCD
'Rendre excel visible
appExcel.Visible = True
'appExcel.Quit
'Désallocation mémoire
Set wsExcel = Nothing
Set wbExcel = Nothing
Set appExcel = Nothing
End Sub
---
Sub ConstruitTCD()
Range("A1").CurrentRegion.Select
ActiveWorkbook.Names.Add "BD", RefersToR1C1:=Selection
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"BD").CreatePivotTable TableDestination:="", TableName:= _
"TCD"
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("TCD").AddFields RowFields:="A" _
, ColumnFields:="B"
ActiveSheet.PivotTables("TCD").PivotFields("C"). _
Orientation = xlDataField
End Sub |
Partager