Changing SAS data source in excel 2010 pivot

[ccondike]

I have a set of pivots connected to a SAS data source and I'm looking to use the same set of pivots I built, but connect to a different SAS data source.

I spent ~20 hours building an excel report built of approximately 10 individual pivots, slicers, formulas, etc. I need this report replicated with the exact same fields but instead, connect to 7 different data sets in SAS. I'm hoping I don't have to spend another ~100 hours rebuilding all of this from scratch.

When I click on the current pivot table I have connected to "SAS data set X", I go to the SAS tab in excel --> SAS Data, I can browse for the new SAS data set, "data set Y" and locate it. Once I have "data set Y" selected, I'm unable to get it to replace the data source of the pivot table I clicked on.

The only option I have is to connect "data set Y" to a blank pivot, which would mean I would have to run through that entire 20 hour process again.

Does anyone know how to simply change the SAS data source in an existing pivot?

Thanks

Chris

[DomPazz]

I think you have to use VBA to change the source.  Open the SAS Add-In for Microsoft Office help (SAS Ribbon -> Help ->  Help for the SAS Add-In to Microsoft Office).  From there, look at the chapter named "Automating the SAS Add-In with Visual Basic Code".  There is documentation on the SASPivotTable class.

I have not tried this, but this is where I would start if I were in your shoes.

HTH!