pivottable mutiple recordsource

[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.

 

[keepITcool]

I have small addin that works by copying multitable data
to 1 hiddensheet


So as long as the data can fit on 1 sheet.. it will work.
(and you have all the options of a normal single list
pivottable iso the limited functionality of multiple range pivot.

http://members.chello.nl/keepitcool/download.html



keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[Guest]

Hi KIC, great solution. It will work I guess,the problem is that I have a
shortage on rows in the near future... Probably I have to turn to Access and
use a union there and than put the result data in a pvt.

Thanks again for your help.

gr. Leon

I have small addin that works by copying multitable data
to 1 hiddensheet


So as long as the data can fit on 1 sheet.. it will work.
(and you have all the options of a normal single list
pivottable iso the limited functionality of multiple range pivot.

http://members.chello.nl/keepitcool/download.html



keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

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:\;Drive
rId=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").Orientat
ion =
xlDataField

My question: can't xl not be fooled with some union of ranges instead
of the odbc-solution.