Excel PivotTables

[Lucas]

How can I translate this code to QM?

Sub Macro1()
'

'

'
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"DATA1!R1C1:R745C3").CreatePivotTable TableDestination:="", TableName _
:="Tabla dinámica3"
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("Tabla dinámica3").SmallGrid = False
ActiveSheet.PivotTables("Tabla dinámica3").AddFields RowFields:="T1", _
ColumnFields:="T2"
ActiveSheet.PivotTables("Tabla dinámica3").PivotFields("TOT"). _
Orientation = xlDataField
Charts.Add
ActiveChart.SetSourceData Source:=Sheets("Hoja7").Range("A3")
ActiveChart.Location Where:=xlLocationAsNewSheet
Sheets("Hoja7").Select
End Sub

[Gintaras]

ActiveWorkbook etc are called for Application object.

ExcelSheet es.Init
IDispatch a=es.ws.Application
a.ActiveWorkbook...
...

SourceType:=xlDatabase is not supported. Use @ for optional arguments that you don't use.

Join lines that end with _.

[Lucas]

Ok.

Thanks.

[Lucas]

a.ActiveWorkbook ----> doesn't exist

only exist:

_create
_getactive
_getcontrol
_getfile
_setevents
GetIDsOfNames
GetTypeInfo
GetTypeInfoCount
Invoke

[Gintaras]

Exists, but for IDispatch QM finds it only at run time.

Or use Excel.Application instead of IDispatch, but then conversion often is not so easy.

[Lucas]

Ok.

I will try it.

[Lucas]

I couldn't do it.

Can you show me an example with 3 fields (name, date, score)?

[Gintaras]

Recorded:

Code:

Copy      Help

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 2008.11.21 by G
'

'
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "Sheet1!R1C1:R6C3").CreatePivotTable TableDestination:="", TableName:= _
        "PivotTable2", DefaultVersion:=xlPivotTableVersion10
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    Exit Sub
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("name")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable2").PivotFields("date")
        .Orientation = xlColumnField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable2").AddDataField ActiveSheet.PivotTables( _
        "PivotTable2").PivotFields("score"), "Sum of score", xlSum
End Sub

Converted:
Macro

Code:

Copy      Help

;/exe 1
typelib Excel
ExcelSheet es.Init
Excel.Application a=es.ws.Application

a.ActiveWorkbook.PivotCaches.Add(xlDatabase, "Sheet1!R1C1:R6C3").CreatePivotTable("", "PivotTable2", @, xlPivotTableVersion10)
Excel.Worksheet ws=a.ActiveSheet
Range r=ws.Cells.Item(3, 1)
ws.PivotTableWizard(@ @ r)
r.Select
Excel.PivotTable t=ws.PivotTables("PivotTable2")
t.PivotFields("name").Orientation = xlRowField
t.PivotFields("name").Position = 1
t.PivotFields("date").Orientation = xlColumnField
t.PivotFields("date").Position = 1
t.AddDataField(t.PivotFields("score"), "Sum of score", xlSum)

[Lucas]

Thanks a lot.

It works for me with this code:

Code:

Copy      Help

typelib Excel
ExcelSheet es.Init
Excel.Application a=es.ws.Application

a.ActiveWorkbook.PivotCaches.Add(xlDatabase, "Hoja1!A:C").CreatePivotTable("", "PivotTable2", @, xlPivotTableVersion10)
Excel.Worksheet ws=a.ActiveSheet
Range r=ws.Cells.Item(3, 1)
ws.PivotTableWizard(@ @ r)
r.Select
Excel.PivotTable t=ws.PivotTables("PivotTable2")
t.AddFields("nombre" "fecha")
t.AddDataField(t.PivotFields("puntuacion"), "Suma de Puntuacion", xlSum)

How can I add a Chart?
in my macro:
Charts.Add
ActiveChart.Location Where:=xlLocationAsNewSheet

[Gintaras]

Macro

Code:

Copy      Help

;/exe 1
typelib Excel
ExcelSheet es.Init
Excel.Application a=es.ws.Application

a.Charts.Add
a.ActiveChart.Location(xlLocationAsNewSheet)

[Lucas]

Perfect!

Thanks again.