DATA Step, Macro, Functions and more

Problem Running Excel VBA Macro through SAS

Reply
Frequent Contributor
Posts: 124

Problem Running Excel VBA Macro through SAS

I have following code which I have been running for couple months in my daily job. But since yesterday it stopped working. It opens the excel file and then it does not do anything (not running the VBA macro at all). Below is my code. Please let me know if any settings need to be changed...

 

Below is my code:

 

%let macDrv=c:\Temp\Reports;

%let macBook=Macro_Format_Scorecard.xlsm;

%let macName=FormatAllReports;

 

filename exl dde 'excel|system';

options noxwait noxsync;

x "'C:\Program Files\Microsoft Office\Office15\EXCEL.EXE' &macDrv.\&macBook";

data _NULL_;

x=sleep(5);

run;

 

data _null_;

file exl;

put '[RUN("'"&macBook"'!'"&macName"'",FALSE)]';

put '[ERROR(FALSE)]';

put '[SAVE()]';

put '[CLOSE()]';

run;

 

data _null_;

file exl;

put '[QUIT()]';

run;

Super User
Posts: 17,829

Re: Problem Running Excel VBA Macro through SAS

If it stopped working and worked before it's likely not the code. 

 

Verify that nothing else has changed, permissions, SAS settings, security settings, version of Excel, and path of file. 

Frequent Contributor
Posts: 124

Re: Problem Running Excel VBA Macro through SAS

Thanks Reeza.

 

If I run VBA macro, then it works. It formats the Report. But when I let SAS run the Same Madcro, it does not work..  

Super User
Posts: 17,829

Re: Problem Running Excel VBA Macro through SAS

SAS called macro fine previously with this code and doesn't now? Is that correct? 

 

If so, something else is the issue. 

Figure out what changed between last run and this and test each change. 

Frequent Contributor
Posts: 124

Re: Problem Running Excel VBA Macro through SAS

Yes,  SAS code worked perfectly until yesterday..  

Super User
Posts: 10,500

Re: Problem Running Excel VBA Macro through SAS

[ Edited ]

I have found at least one other application, Cisco Jabber, that if running interferes with dde calls and have anecdotal evidence there may be others. In the case of Jabber it is not sufficient to stop running the program but you have to use task manager to kill the process.

 

So you may check with your IT folks about any new or updated program installed since the last time you successfully ran the SAS program.

Ask a Question
Discussion stats
  • 5 replies
  • 252 views
  • 3 likes
  • 3 in conversation