SAS Office Analytics, SAS Add-In for Microsoft Office, and other integrations

Change Excel pivot table properties through SAS

Reply
N/A
Posts: 1

Change Excel pivot table properties through SAS

Hello, everyone!

I am working on a project trying to control the Excel pivot table through SAS. Basically I wrote SAS code to update the data in Excel worksheet, then refresh the linked pivot tables.

DDE is the main method I use. with the X4ML code like PUT '[workbook.protect(FALSE,FALSE,"'"&psw"'")]'; I am able to open Excel file in SAS, protect or unprotect the workbook, update data, etc.

Here are the questions: DDE or X4ML are very old and limited and I have to search for some better methods to

1. Change the data source of the pivot table. For example the original data source is r1c1:r5c4. After updating there are two more rows to be included and the source should be r1c1:r7c4.

1.png

2. Modify protection attributes. When protecting the Exce worksheet, I'd like the user could use pivot table. In other words, check the option in the picture below.

2.png

The requirement is, all of these should be done in SAS. I know it is easy to write a piece of macro in Excel but that's not allowed. I believe every function in Excel has a corresponding command that could be used in SAS.

Anyone have any idea about these? Thank you!


Grand Advisor
Posts: 17,396

Re: Change Excel pivot table properties through SAS

Change the datasource to be the full worksheet and select out blanks in your pivot table instead.

SAS Super FREQ
Posts: 8,720

Re: Change Excel pivot table properties through SAS

Hi:

  When you state your believe that "every function in Excel has a corresponding command that could be used in SAS", I am not sure that this is true. I would recommend that you work with Tech Support on your question. In addition, at the beginning of your post, you say that "DDE or X4ML are very old and limited and I have to search for some better methods to 1)... 2)...."  Here's an interesting paper that uses VB.NET technology to interact between SAS and Excel (http://support.sas.com/resources/papers/proceedings10/191-2010.pdf).

cynthia

Esteemed Advisor
Esteemed Advisor
Posts: 7,232

Re: Change Excel pivot table properties through SAS

Not saying you should, but if you use xlsx you could manipulate the XML directly.  The XLSX is a zip file, if you rename it and then open it there are various folders/XML docs.  At a rough guess I think \xl\pivotTables\pivotTable1.xml is probably the one to edit (of course this is assuming the file already has  pivot table and it is called pivotTable1).  You could read up on the Microsoft open document format if your interested in reading/writing directly though its a bit of a chore.

Ask a Question
Discussion stats
  • 3 replies
  • 877 views
  • 0 likes
  • 4 in conversation