topic Updating Excel Workbooks without opening them to refresh with SAS EG? in Microsoft Integration with SAS

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

MrManSledge — Fri, 22 Jan 2021 19:04:21 GMT

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.

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

SASKiwi — Sat, 23 Jan 2021 02:22:43 GMT

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.

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

fifthand57th — Wed, 27 Jan 2021 20:43:12 GMT

@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=p1fqgkd7pbj5wpn1f54xlg5clrwc.htm&locale=en

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.