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.
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.
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!