Updating Extrenal Excel Pivot Table in SAS

[Statdork]

I have gone through several searches, including the SAS community and cannot find a solution to this problem. I simply want to open an Excel workbook and refresh all the pivot tables within the workbook in a SAS Program. Below is the code that I have come up with, which opens the workbook, but just keeps it open giving me the following error:

ERROR: DDE session not ready.

FATAL: Unrecoverable I/O error detected in the execution of the DATA step program.

Aborted during the EXECUTION phase.

Code:

options noxwait noxsync;

data _null_;

     rc=system('start excel');

     rc=sleep(20);

run;

filename cmds dde 'excel|system';

data _null_;

     file cmds;

     put "[open(""&path\&f_name..xlsx"")]";

     put "[pivot.refresh()]";

     put "[save(""&path\&f_name..xlsx"")]";

     put "[quit()]";

run;

quit;

[Reeza]

Some suggestions/guesses based on the documentation below:

1. When you open the file you're not on an active cell, so nothing refreshes.

2. Try suggesting pivot tables names.

3. You usually get a pop up when opening an Excel file about refreshing links, that may be an issue.

4. Write or record VBA code in the workbook to refresh the tables and then use DDE to run the macros if you can't find workarounds.

From the DDE documentation:

Macro Sheets Only

Refreshes a PivotTable.

Syntax

PIVOT.REFRESH(name)

Name    is the name of the PivotTable the user would like to refresh with current data. If  name is omitted, Microsoft Excel will use the PivotTable containing the active cell.

Remarks

If the function is successful, it returns TRUE; otherwise, it returns the #VALUE! error value.

    If name is not a valid PivotTable name, then the #VALUE! error value is returned.

[Statdork]

Reeza, thank you for your reply.

I have tried using both the active cell and the pivot table name to no avail. I don't get any popup when using excel as you noted in 2 above and recording a VBA macro in Excel is not an option as I have to save the file as .xlsm and then cannot wrote a SAS dataset out to it.

This is the most recent code:

options noxwait noxsync;

data _null_;

     rc=system('start excel');

     rc=sleep(20);

run;

filename cmds dde 'excel|system';

data _null_;

     file cmds;

     put "[open(""&path\&f_name..xlsx"")]";

     put '[workbook.select("Pivot")]';

     put '[select("A1")]';

     put "[pivot.refresh()]";

     put "[save()]";

     put "[quit()]";

run;

quit

[Statdork]

I should note that I am referencing the following to a tab within the workbook called Pivot. Could this be the problem?

put '[workbook.select("Pivot")]';

[Reeza]

You can export data to an XLSM file.

If you want to attach a sample workbook where you have the issue I can take a further look into, but I don't feel like mocking up a scenario.

[Statdork]

I didn't mean it is physically impossible, I should have clarified. There are no business partners in the history of the world that want to open up an xlsm file. I know I could write the data to the .xlsm file and then make a copy of it saving it as .xlsx, but I am close to this solution. At this point, I have skipped trying to update the pivot table by name. I can open the .xlsx file and activate the sheet that I need; however, it will not select the range of cells that I want to refresh, any idea how to do that?

[Statdork]

If I manually open the .xlsx file and select a cell within the pivot table, then save my original code above refreshes it. Frustrating.

[Reeza · Accepted solution]

You need to put your reference in as RC not A4, you can't select a header row in a pivot (I think?) so try selecting the first cell.

   put '[select("R2C1")]';

[Statdork]

Ahh....row, column....that is a duh moment for me. I was thinking in terms of cell range rather than how DDE needs to reference the cell. Thanks Reeza, got it working I appreciate it! For anyone else that needs a solution like this, this is the code that works for me:

/* Tell SAS to open Excel */

options noxwait noxsync;

data _null_;

     rc=system('start excel');

     rc=sleep(10);

run;

/* Execute VBA code via SAS DDE - open file, select sheet with pivot, put cursor on pivot table and refresh pivot table */

filename cmds dde 'excel|system';

data _null_;

     file cmds;

     put "[open(""\\&path\&f_name..xlsx"")]"; 

     put '[workbook.activate("Pivot")]';

     put '[select("R1C1")]';

     put '[pivot.refresh()]';

     put "[save()]";

     put "[quit()]";

run;

quit;

[archana]

Does the above code work in Unix SAS?

[Reeza]

No, it won't