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

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?

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisHemedinger
Community Manager

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.

 

Learn from the Experts! Check out the huge catalog of free sessions in the Ask the Expert webinar series.

View solution in original post

2 REPLIES 2
ChrisHemedinger
Community Manager

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.

 

Learn from the Experts! Check out the huge catalog of free sessions in the Ask the Expert webinar series.
Yemi_Mktg
Calcite | Level 5

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

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

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
  • 2 replies
  • 2324 views
  • 3 likes
  • 2 in conversation