Access97 : Interface to Excel pivot tables 
Author Message
 Access97 : Interface to Excel pivot tables

I have used the Pivot Table Wizard to create an embedded Excel pivot table
in an Access form.

How do I programmatically modify the Access query being used by Excel for
the pivot?

I cannot find any properties of the embedded pivot object which appear to be
relevant and I cannot find any reference to the data source from within the
pivot in Excel.

If the query which is interfaceing the two is not exposed, how would I
create a new embedded pivot table on the fly (as an alternative)?

Best Regards

Neil
--------------------------------------------------------
  Neil Sargent
  Smart IT

--------------------------------------------------------



Fri, 17 May 2002 03:00:00 GMT  
 Access97 : Interface to Excel pivot tables
Look at the PivotTable's SourceData property which will return an array with
all the relevant info to  you.

' ****** Code Start ********
Dim objPvtTbl As PivotTable
Dim varSrc As Variant
Dim i As Integer

    Set objPvtTbl = ActiveWorkbook.ActiveSheet.PivotTables(1)
    varSrc = objPvtTbl.SourceData
    For i = LBound(varSrc) To UBound(varSrc)
        Debug.Print varSrc(i)
    Next
' ******* Code End ********

 -- Dev


Quote:
> I have used the Pivot Table Wizard to create an embedded Excel pivot table
> in an Access form.

> How do I programmatically modify the Access query being used by Excel for
> the pivot?

> I cannot find any properties of the embedded pivot object which appear to
be
> relevant and I cannot find any reference to the data source from within
the
> pivot in Excel.

> If the query which is interfaceing the two is not exposed, how would I
> create a new embedded pivot table on the fly (as an alternative)?

> Best Regards

> Neil
> --------------------------------------------------------
>   Neil Sargent
>   Smart IT

> --------------------------------------------------------



Fri, 17 May 2002 03:00:00 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. exporting to excel pivot table

2. Refreshing an Excel pivot table, programatically, from Access

3. Add median to Excel pivot tables?????

4. VBA and Excel Pivot table

5. EXCEL 2000: Pivot table programming

6. Calculated Member in Excel Pivot Table

7. From Table To Excel - From Excel To Table

8. Pivot Table

9. Creating XL pivot tables in Access module

10. problem with pivot table

11. Dynamically refreshed pivot tables from Access queries

12. Pivot table : limit display / limiter l'affichage

 

 
Powered by phpBB® Forum Software