Saving Excel-dataset after modifying

Accepted Solution Solved
Reply
Super Contributor
Posts: 355
Accepted Solution

Saving Excel-dataset after modifying

[ Edited ]

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?


Accepted Solutions
Solution
3 weeks ago
Super User
Super User
Posts: 9,599

Re: Saving Excel-dataset after modifying

Posted in reply to user24feb

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


All Replies
Solution
3 weeks ago
Super User
Super User
Posts: 9,599

Re: Saving Excel-dataset after modifying

Posted in reply to user24feb

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.

Super Contributor
Posts: 355

Re: Saving Excel-dataset after modifying

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

Super User
Super User
Posts: 9,599

Re: Saving Excel-dataset after modifying

Posted in reply to user24feb

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.

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

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