Alternative to DDE for Accessing Excel (Office 365)

[Yemi_Mktg]

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?

[ChrisHemedinger · Accepted solution]

If you want to do this work on your desktop and not in the cloud, then I suggest using a PowerShell script (which you can launch from SAS) to automate Microsoft Excel and accomplish the task. There are lots of examples on the web of using PowerShell to automate Excel.  The Excel automation interface is well supported by Microsoft, whereas DDE works only for certain behind-the-scenes legacy operations and is often blocked by security updates/settings.

If you need to somehow make it work from a SAS program, but in the cloud independent of your desktop, you'll need to use Microsoft Graph APIs. I have an article and video for that here: How to use SAS to access Microsoft 365  However, I don't know whether Excel macro operations would be something you could trigger that way.

[Yemi_Mktg]

Thank you!  I wish I'd posted this sooner.  I'd tried faffing around with Command Prompt to no avail, but Powershell worked!