G
Guest
Dear Group,
I'm planning a extensive sheet with multiple recordsources. Consolidating
(pivottable-wizard) is no option. I found one solution with odbc. Something
like:
With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
.Connection =
Array("ODBC;DSN=Excel-bestanden;DBQ=C:\Testing.xls;DefaultDir=C:\;DriverId=790;MaxBufferSize=2048;PageTimeout=5;")
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM dbBlad1 UNION SELECT * FROM dbBlad2")
.CreatePivotTable TableDestination:="[Map2]Blad2!R3C1",
TableName:="Draaitabel3", DefaultVersion:=xlPivotTableVersion10
End With
ActiveSheet.PivotTables("Draaitabel3").AddFields RowFields:="GB",
ColumnFields:="Bron"
ActiveSheet.PivotTables("Draaitabel3").PivotFields("Bedrag").Orientation =
xlDataField
My question: can't xl not be fooled with some union of ranges instead of
the odbc-solution.
I'm planning a extensive sheet with multiple recordsources. Consolidating
(pivottable-wizard) is no option. I found one solution with odbc. Something
like:
With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
.Connection =
Array("ODBC;DSN=Excel-bestanden;DBQ=C:\Testing.xls;DefaultDir=C:\;DriverId=790;MaxBufferSize=2048;PageTimeout=5;")
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM dbBlad1 UNION SELECT * FROM dbBlad2")
.CreatePivotTable TableDestination:="[Map2]Blad2!R3C1",
TableName:="Draaitabel3", DefaultVersion:=xlPivotTableVersion10
End With
ActiveSheet.PivotTables("Draaitabel3").AddFields RowFields:="GB",
ColumnFields:="Bron"
ActiveSheet.PivotTables("Draaitabel3").PivotFields("Bedrag").Orientation =
xlDataField
My question: can't xl not be fooled with some union of ranges instead of
the odbc-solution.