Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Excel PivotTables
#1
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
#2
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 _.
#3
Ok.

Thanks.
#4
a.ActiveWorkbook ----> doesn't exist

only exist:

_create
_getactive
_getcontrol
_getfile
_setevents
GetIDsOfNames
GetTypeInfo
GetTypeInfoCount
Invoke
#5
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.
#6
Ok.

I will try it.
#7
I couldn't do it.

Can you show me an example with 3 fields (name, date, score)?
#8
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)
#9
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
#10
Macro
Code:
Copy      Help
;/exe 1
typelib Excel
ExcelSheet es.Init
Excel.Application a=es.ws.Application

a.Charts.Add
a.ActiveChart.Location(xlLocationAsNewSheet)
#11
Perfect!

Thanks again.


Forum Jump:


Users browsing this thread: 1 Guest(s)