BookmarkSubscribeRSS Feed
peterluo
Calcite | Level 5

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!


3 REPLIES 3
Reeza
Super User

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

Cynthia_sas
SAS Super FREQ

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

Discussion stats
  • 3 replies
  • 1756 views
  • 0 likes
  • 4 in conversation