BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
user24feb
Barite | Level 11

Is it possible (and how) to automatically save and close Excel after opening a file (like in this example: https://communities.sas.com/t5/General-SAS-Programming/opening-an-excel-file-with-SAS/m-p/260934#M36...) using an x command?

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Why?  SAS generates output, what you do with that output afterwards is not a SAS issue.  I mean you can pass out system commands (well if you have the correct permissions) and open Excel, but then you need to send the commands through to Excel to do something.  The question would be why you would want to do this, I assume its something to do with updating Excel specific things like pivots or something?  If so then have the Excel file have some On Open VBA code which loads your data into it and then refreshes the workbook.  This is far simpler than any other method.  Another way is to have a VB batch file which send the appropriate commands out, there are hundreds of examples out there:

https://www.experts-exchange.com/questions/24163470/BAT-file-to-Open-Excel-Files-in-a-folder-Save-an...

This could be called from SAS.

 

But again, it all depends on use case scenario and best fit.

View solution in original post

3 REPLIES 3
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Why?  SAS generates output, what you do with that output afterwards is not a SAS issue.  I mean you can pass out system commands (well if you have the correct permissions) and open Excel, but then you need to send the commands through to Excel to do something.  The question would be why you would want to do this, I assume its something to do with updating Excel specific things like pivots or something?  If so then have the Excel file have some On Open VBA code which loads your data into it and then refreshes the workbook.  This is far simpler than any other method.  Another way is to have a VB batch file which send the appropriate commands out, there are hundreds of examples out there:

https://www.experts-exchange.com/questions/24163470/BAT-file-to-Open-Excel-Files-in-a-folder-Save-an...

This could be called from SAS.

 

But again, it all depends on use case scenario and best fit.

user24feb
Barite | Level 11

It is actually intended as a (makeshift auto-)update and I was hoping to avoid DDE etc.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Yes, then build your auto update process using Excel and VBA.  With that you can create an OnOpen function (note this does not have to be part of the actual SAS file, the VAB can be in a separate file) which opens the CSV file you exported from SAS, reads that in and processes it into your Excel file, then refresh all the objects within the Excel file and save it.  Its pretty simple, and you can kick it off from SAS just by asking the OS to open the working file - which would kick off its OneOpen function, load CSV, update other Excel file, refresh then save and shut down.

I would highly advise to avoid DDE.

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 538 views
  • 1 like
  • 2 in conversation