Pivot Table groups Refresh

I have written a plugin for Excel 2003 using exposed APIs in VB.NET and have
following porblems. Please help me out.

The major function of the plugin is to fetch data from a web serivce and
draw Pivot Table, charts for the representation of the data. The problem is
that when I refresh the already drawn pivot table for a changed CSV file the
data area of the pivot table is updated for the newly populated data in the
CSV but the groups made for the fields in rows or columns sections are not
refreshed. These groups can be seen by using the drop downs for the row and
column fields in the pivot table. I want to find a way to refresh these drop
downs on the refresh of the pivot table.

I am using Execl.PivotTable's refresh method to trigger the refresh
operation after retrieving the new data and creating a new CSV file.

Please help me out in this regard.

Waiting for your reply.

Usman Qadri

Hi,

Have you tried refreshing the PivotCache?

like this:

dim sODBCConnStr as string
sODBCConnStr = "DBQ=" & sPath & ";DefaultDir=" & sPath _
& ";Driver={Microsoft Text Driver (*.txt; *.csv)};DriverId=27;" _
& "Extensions=asc,csv,tab,txt;FIL=text;MaxBufferSize=2048;MaxScanRows=25;" _
& "PageTimeout=5;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;"
ActiveWorkbook.PivotCaches(1).Connection = sODBCConnStr
ActiveWorkbook.PivotCaches(1).Refresh

that may help for a completely different data file.

HTH

Philip

"Usman Qadri" wrote:

> I have written a plugin for Excel 2003 using exposed APIs in VB.NET and have
> following porblems. Please help me out.
>
> The major function of the plugin is to fetch data from a web serivce and
> draw Pivot Table, charts for the representation of the data. The problem is
> that when I refresh the already drawn pivot table for a changed CSV file the
> data area of the pivot table is updated for the newly populated data in the
> CSV but the groups made for the fields in rows or columns sections are not
> refreshed. These groups can be seen by using the drop downs for the row and
> column fields in the pivot table. I want to find a way to refresh these drop
> downs on the refresh of the pivot table.
>
> I am using Execl.PivotTable's refresh method to trigger the refresh
> operation after retrieving the new data and creating a new CSV file.
>
> Please help me out in this regard.
>
> Waiting for your reply.
>
> Usman Qadri
>

You can set the MissingItemsLimit property for the pivot cache, as shown
here:

http://www.contextures.com/xlPivot04.html#Clear2002

Usman Qadri wrote:
> I have written a plugin for Excel 2003 using exposed APIs in VB.NET and have
> following porblems. Please help me out.
>
> The major function of the plugin is to fetch data from a web serivce and
> draw Pivot Table, charts for the representation of the data. The problem is
> that when I refresh the already drawn pivot table for a changed CSV file the
> data area of the pivot table is updated for the newly populated data in the
> CSV but the groups made for the fields in rows or columns sections are not
> refreshed. These groups can be seen by using the drop downs for the row and
> column fields in the pivot table. I want to find a way to refresh these drop
> downs on the refresh of the pivot table.
>
> I am using Execl.PivotTable's refresh method to trigger the refresh
> operation after retrieving the new data and creating a new CSV file.
>
> Please help me out in this regard.
>
> Waiting for your reply.
>
> Usman Qadri
>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html