Prior to the cloud-based versions of Office I was able to run code to set off an Excel macro to create some regular reports. I've scoured the SAS forums and whilst I appreciate that DDE is outdated, I can't seem to find alternative code that simply lets me run my VBA macro. My setup is as follows: - I run some lengthy code in Base SAS (V9.4, desktop not server based), at the end of which a number of flat files (.txt, tab delimited) are produced and exported to a static server drive. - The end of the code then should run the following (template_path is the path for the folder on the static server): filename EXCEL DDE 'EXCEL|SYSTEM';
data _null_;
file excel;
put "[open(""&template_path.\Report Template.xlsm"")]";
put '[RUN("Report_Macro")]';
run; - This will always successfully open Excel, but no longer opens the file or runs the VBA macro (the macro manipulates the flat files produced earlier in the SAS code). - I am running Windows 10 Pro for Workstations (64-bit OS) and Microsoft Office 365 ProPlus, Version 1902. I only wrote this code earlier this year and although it did work, its ability to work has deteriorated with what I assume are successive updates to Microsoft Office over the last couple of months (we were already on Office 365 when I wrote it, but I don't know which version). I've seen lots of posts on the SAS Add In for Microsoft Office (which I think we may very well have the licence for), but all of them discuss how to work with SAS files from Excel, whereas I want to control Excel from SAS. I have also seen articles on how to control Excel files that are in the cloud, but a) I'm using desktop Excel and my files are not in the cloud and b) I do not believe my IT department would grant me the necessary permissions to access the files with SAS were I to change the setup so that everything was in the cloud. So, in summary, is there a code alternative to DDE that works similarly?
... View more