topic Problem Running Excel VBA Macro through SAS in SAS Programming

Problem Running Excel VBA Macro through SAS

pp2014 — Wed, 08 Jun 2016 18:38:45 GMT

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;

Re: Problem Running Excel VBA Macro through SAS

Reeza — Wed, 08 Jun 2016 18:56:26 GMT

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.

Re: Problem Running Excel VBA Macro through SAS

pp2014 — Wed, 08 Jun 2016 19:00:06 GMT

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..

Re: Problem Running Excel VBA Macro through SAS

ballardw — Thu, 09 Jun 2016 14:34:14 GMT

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.

Re: Problem Running Excel VBA Macro through SAS

Reeza — Wed, 08 Jun 2016 21:07:07 GMT

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.

Re: Problem Running Excel VBA Macro through SAS

pp2014 — Wed, 08 Jun 2016 23:00:14 GMT

Yes, SAS code worked perfectly until yesterday..