Refreshing a series of pivot tables

[wildhogs]

Hello

I am taking over the maintenance of a SAS reporting program that updates 21 existing excel workbook files using DDE.

Each Excel Workbook file contains:

a. 4 worksheets that have 4 charts each
b. 4 worksheets that contain one report each
c. 1 worksheet that contains 11 pivot tables
d. 1 worksheet that contains 4 pivot tables.

The existing program uses DDE to update an Excel Model file and then it saves it under the current months name.

Question

I would like to retrieve a list of all the pivot table names that exist in the excel file and then perform a refresh on each pivot table using DDE.

This would save me over 315 manual pivot table refreshes!

Does anyone out there know how to do this?


some code to retrieve the list of pivot tables...

put '[pivot.refresh("PivotTable5")]';

PS.

I would have liked to use the refresh on open, however, my clients do not have
access to the external files.

[Petersi]

I'm not sure if this helps, but I recently had to write a program to identify all the chart objects in a workbook. You can find out a lot about a workbook using the topics dde triplet:

filename xltopics dde'excel|system!topics' lrecl=32000;

data _null_;
length topic $ 1000;
infile xltopics pad dsd notab dlm='09'x;
input topic $ @@;
put topic=;
run;

A good reference is Excel Exposed: Using Dynamic Data Exchange to Extract Metadata from MS Excel Workbooks by Koen Vyverman. You can find it and more of his papers at

http://www.sas-consultant.com/professional/papers.html