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
|
Private Sub CreateExcelGraphOEE()
Dim xlApp As Excel.Application
Dim xlBook As Excel.Workbook
Dim xlSheet As Excel.Worksheet
Dim xlSheetstat As Excel.Worksheet
Dim iloop As Integer
Dim iColCounter As Integer
Dim sRefExcel As String
Dim sPivotTableSheetName As String
Dim ExcelDLL As CubeExportExcelDll.clsExportExcel
Dim rs As ADODB.Recordset
On Error GoTo CreateExcelGraphOEE_Error
Set ExcelDLL = New CubeExportExcelDll.clsExportExcel
sRefExcel = ""
Set xlApp = ExcelDLL.OpenExcel
Set mvarExcelApplication = xlApp
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Sheets.Add
xlSheet.Name = mvarDataSheetName
xlSheet.Activate
sRefExcel = CVCell(1, 1) 'A1 reference for Title
xlSheet.Range(sRefExcel) = mvarDataSheetName
xlSheet.Range(sRefExcel).Font.Bold = True
xlSheet.Range(sRefExcel).Font.Color = vbBlue
xlSheet.Activate
'Set ExcelDLL = New CubeExportExcelDll.clsExportExcel
ExcelDLL.LetRecordsetData mvarGraphRecordset
sRefExcel = CVCell(2, 1) 'A1 reference for Data
ExcelDLL.DirectExport xlBook.Name, xlSheet.Name, sRefExcel, True, True, False, True
xlApp.Range("F7").Select
xlApp.ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= "'" & mvarDataSheetName & "'!R2C1:R" & CStr(mvarGraphRecordset.RecordCount + 2) & "C" & CStr(mvarGraphRecordset.Fields.Count)).CreatePivotTable TableDestination:="", _ TableName:="PivotTable1"
xlApp.ActiveSheet.Name = mvarGraphName
--erreur ici--> xlApp.ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(mvarGraphRecordset.Fields.Count, 1)
xlApp.ActiveSheet.Cells(mvarGraphRecordset.Fields.Count, 1).Select
xlApp.ActiveSheet.PivotTables("PivotTable1").SmallGrid = False
... |
Partager