This is a tricky situation based on the way that the add-in handles data. The add-in doesn't want to insert data over another dataset since it can't have different tables pointing to the same cells. If you refreshed one of them, they would be writing over each other and the worksheet would turn into a mess. Likewise, if you try to clear out the previous table and insert the new one, Excel has lost its reference since it is specifically pointing to a location within the table.
Here are 2 suggestions:
1. We have an option for whether or not the data is opened into an Excel table or not:
If you turn this option off, then when your data is inserted into the worksheet it will simply be in cells. Now, you would lose the Excel functionality of the table object. But, if you then add formulas that point to cell ranges, those are no longer linked to a table. That way, if you remove the older table from the worksheet and replace it with a new one, the formulas will still be valid.
2. You could write a stored process that would essentially copy the data from the data source you want to see into a new data source that would be inserted into the document. When you want to change the data source, you could run the stored process, and then simply refresh the data to account for the new table. Here is a step by step description to make this more clear:
Write a stored process that simply does something like:
data SASDATA.FOREXCEL;
set &dataset;
run;
This could be a prompted stored process, where the user will be prompted for the &dataset macro when it runs. You could run this once so that your FOREXCEL data source is populated with the first table you want to use.
The time comes where you want to replace the data in FOREXCEL with another table. Simply run the stored process and it will prompt you for the data source.
Provide the new data source, and it will overwrite your FORDATA table with the new one. Now you can simply refresh the data table that you inserted into Excel, and it will update in place with the new columns/values. You can even continue to use a table for it since you're never removing the table from the worksheet.
Hopefully one of these suggestions will work for you, or lead you to another possible solution. Feel free to reach back out if you have questions on implementing these.
Tim Beese
... View more