Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
pivottable mutiple recordsource
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:=xlExter nal) .Connection = Array("ODBC;DSN=Excel-bestanden;DBQ=C:\Testing.xls;DefaultDir=C:\;Driver Id=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. -- best regards, Leon |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
pivottable mutiple recordsource
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 ?B?c29qb2Nh?= wrote: 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:=xlExter nal) .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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
pivottable mutiple recordsource
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 "keepITcool" wrote: 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 ?B?c29qb2Nh?= wrote: 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:=xlExter nal) .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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
mutiple tests | Excel Discussion (Misc queries) | |||
Running mutiple excel sheets on mutiple computers | Excel Discussion (Misc queries) | |||
mutiple if formulas | Excel Worksheet Functions | |||
Creating a PivotTable w/o selecting data in an existing PivotTable | Excel Discussion (Misc queries) | |||
PivotTable - PivotTable Field name is not valid - error! | Excel Programming |