Updating Excel Workbooks without opening them to refresh with SAS EG?

[MrManSledge]

Hi all,

I'm migrating a lot of PC-SAS reporting over to SAS EG. Many of the programs relied heavily upon proc export to update many different sheets in an excel workbook.

I know proc export doesn't work with SAS EG (at least to local excel workbooks), but I am using to what I assume is the next best option, the SAS Excel add-on.

Only issue with this is that it seems that I have to open each workbook after I run my SAS EG projects and select the 'Manage Content' and then 'Update all.'

So my question is, 

Is there a better way to do this? Can I run a script or something else that will update all the datasets in the excel files without having to open each one?

Appreciate the time.

[SASKiwi]

I'm assuming here that you are using EG with a remote SAS server. EG using SAS on your PC can reference PC Excel workbooks just fine.

One solution would be to move your Excel workbooks to a common shared folder that is also accessible by your remote SAS server. I can't think of any easy way to do what you want, leaving the workbooks on your PC. 

[fifthand57th]

@MrManSledge 

You can schedule a refresh of your Excel workbooks using the SAS Add-in. A VBScript file is created that will execute in non-interactive mode when scheduled in Windows Task Scheduler.

Here's the doc:

https://go.documentation.sas.com/?cdcId=amodoccdc&cdcVersion=8.3&docsetId=amoug&docsetTarget=p1fqgkd...

A SAS Note I wrote with a list of tips for scheduling a refresh successfully: http://support.sas.com/kb/59947

Alternatively, you can enable the "Refresh on file open" option for individual SAS content in a workbook. Open Manage Content and right-click on the SAS content you want to refresh automatically when the Excel workbook opens. Choose Properties, then on the Execution tab you'll see the "Refresh on file open" option.