Need help with SAS & DDE

[SASHunter]

I have a program that I am testing where it opens EXCEL and then assigns a macro variable to an Excel program that has a macro in it.  The program runs and opens Excel, but it errors and does not open the program.  I've tried different approaches of using the SLEEP command and also placing XSYNC in different parts.  It still errors with the following ERROR: File is in use, EXCEL|SYSTEM.

Can anyone help with this?

Thanks in advance,

Nancy

Here is the program that I have.

/*******************************************************************************************/

    options noxwait;

        %global filnme mypath newdrive;

        %let filnme = %sysget(SAS_EXECFILENAME);

        %let xpath  = %sysget(SAS_EXECFILEPATH);

        %let mypath = M:\ADC-P\CPSPB;

        /* Determine the current drive and directory that is being used. */

        %let drvlen  = %index(&mypath,\CPSPB);

        %let newdrive= %substr(&mypath,1,&drvlen - 1)\CPSPB\;

        /*  start Excel */

        *     %include "&mypath\opnexcel.sas";

    filename cmdexcel DDE 'EXCEL|SYSTEM';

    /**********************************************************************/

    /* Data step to start Excel if it is not already open                 */

    /**********************************************************************/

    data _null_;

     fid = fopen('cmdexcel','S');            /* Check if Excel is open    */

     if fid le 0 then do;                    /* Open via Windows registry */

        rc = system("Start Excel");          /* DOS command to open Excel */

        start = datetime();                  /* Note start time           */

        stop = start + 5;                    /* Max time to try opening   */

        do while (fid le 0);                 /* Loop while Excel opens    */

           fid = fopen('cmdexcel','S');      /* Check if Excel is open    */

           time = datetime();                /* Reset current time        */

           if time ge stop then fid = time;  /* Set FID to terminate loop */

        end;                                 /* do while (fid le 0)       */

     end;                                    /* Open via Windows registry */

     rc = fclose(fid);                       /* Close fileopen on Excel   */

    run;

    *create macro variables with the Excel file names;

    data _null_;

        call symput('macrfile',"'"||

                '[open("'||"&newdrive"||'Macros\Chdaily_test_.xlsm")]'||"';");

    run;

    /* Make it wait some while Excel opens */

    data _null_;

        rc=sleep(20);

    run;

    /**********************************************/

    /*  OPEN THE EXCEL FILE CONTAINING THE MACRO. */

    /**********************************************/

    data _null_;

        file cmdexcel;

        put &macrfile;  *open the file containing the macro;

    run;

/*******************************************************************************************/

This is the error I get at the end.  So Excel opens but it does not open up the

program that contains the macros.

646

647      /* Make it wait some while Excel opens */

648      data _null_;

649          rc=sleep(20);

650      run;

NOTE: DATA statement used (Total process time):

      real time           20.01 seconds

      cpu time            0.00 seconds

651

652      /**********************************************/

653      /*  OPEN THE EXCEL FILE CONTAINING THE MACRO. */

654      /**********************************************/

655      data _null_;

656          file cmdexcel;

SYMBOLGEN:  Macro variable MACRFILE resolves to

            '[open("M:\ADC-P\CPSPB\Macros\Chdaily_test_.xlsm")]';

657          put &macrfile;  *open the file containing the macro;

658      run;

ERROR: File is in use, EXCEL|SYSTEM.

NOTE: The SAS System stopped processing this step because of errors.

NOTE: DATA statement used (Total process time):

      real time           0.00 seconds

      cpu time            0.00 seconds

[Reeza]

Does the program work if you replace the &macrfile with the actual file path not the macro variable?

ie

data _null_;

file cmdexcel;

put  '[open("M:\ADC-P\CPSPB\Macros\Chdaily_test_.xlsm")]';

run;

I typically use these options when using DDE:

option noxwait noxsync mprint;

the mprint option is really useful to help tell where the code is going wrong.

Try posting the log (only) with the mprint option on as well.

[SASHunter]

Hi,

I changed to top X System Option to

options noxwait noxsync ;

It worked correctly three times, then it failed.  See log below.

It seems to me to be a timing issue on opening Excel.  That is why

I put in the SLEEP data step.

Nancy

____________________________________________________

NOTE: Copyright (c) 2002-2008 by SAS Institute Inc., Cary, NC, USA.

NOTE: SAS (r) Proprietary Software 9.2 (TS2M3)

      Licensed to CENSUS BUREAU FOUNDATION WNDW, Site 70047657.

NOTE: This session is executing on the XP_PRO  platform.

NOTE: SAS initialization used:

      real time           2.79 seconds

      cpu time            1.60 seconds

1            OPTIONS SYMBOLGEN MPRINT;

2            options noxwait noxsync ;

3

4            %global filnme mypath newdrive;

5

6            %MACRO fdate(fmt);

7                %global fdate;

8                data _null_;

9                    call symputx("fdate",put("&sysdate"d,&fmt));

10               run;

11           %MEND fdate;

12           %fdate(worddate.)  /* Execute the macro */

MPRINT(FDATE):   data _null_;

SYMBOLGEN:  Macro variable SYSDATE resolves to 24JUN11

SYMBOLGEN:  Macro variable FMT resolves to worddate.

MPRINT(FDATE):   call symputx("fdate",put("24JUN11"d,worddate.));

MPRINT(FDATE):   run;

NOTE: DATA statement used (Total process time):

      real time           0.04 seconds

      cpu time            0.01 seconds

SYMBOLGEN:  Macro variable FDATE resolves to June 24, 2011

13           %put ***  This program ran on:  &fdate;

***  This program ran on:  June 24, 2011

14

15

16           %let filnme = %sysget(SAS_EXECFILENAME);

17           %let xpath  = %sysget(SAS_EXECFILEPATH);

18           %let mypath = M:\ADC-P\CPSPB;

19

20           /* Determine the current drive and directory that is being used. */

21           %let drvlen  = %index(&mypath,\CPSPB);

SYMBOLGEN:  Macro variable MYPATH resolves to M:\ADC-P\CPSPB

22           %let newdrive= %substr(&mypath,1,&drvlen - 1)\CPSPB\;

SYMBOLGEN:  Macro variable MYPATH resolves to M:\ADC-P\CPSPB

SYMBOLGEN:  Macro variable DRVLEN resolves to 9

23

24

25           /*  start Excel */

26           *   %include "&mypath\opnexcel.sas";

27       filename cmdexcel DDE 'EXCEL|SYSTEM';

28       /**********************************************************************/

29       /* Data step to start Excel if it is not already open                 */

30       /**********************************************************************/

31

32       data _null_;

33        fid = fopen('cmdexcel','S');            /* Check if Excel is open    */

34        if fid le 0 then do;                    /* Open via Windows registry */

35           rc = system("Start Excel");          /* DOS command to open Excel */

36           start = datetime();                  /* Note start time           */

37           stop = start + 5;                    /* Max time to try opening   */

38           do while (fid le 0);                 /* Loop while Excel opens    */

39              fid = fopen('cmdexcel','S');      /* Check if Excel is open    */

40              time = datetime();                /* Reset current time        */

41              if time ge stop then fid = time;  /* Set FID to terminate loop */

42           end;                                 /* do while (fid le 0)       */

43        end;                                    /* Open via Windows registry */

44        rc = fclose(fid);                       /* Close fileopen on Excel   */

45       run;

NOTE: DATA statement used (Total process time):

      real time           5.15 seconds

      cpu time            0.23 seconds

46

47

48       *create macro variables with the Excel file names;

49       data _null_;

50           call symput('macrfile',"'"||

51                   '[open("'||"&newdrive"||'Macros\Chdaily_test_.xlsm")]'||"';");

SYMBOLGEN:  Macro variable NEWDRIVE resolves to M:\ADC-P\CPSPB\

52       run;

NOTE: DATA statement used (Total process time):

      real time           0.00 seconds

      cpu time            0.00 seconds

53

54       /* Make it wait some while Excel opens */

55       data _null_;

56           rc=sleep(20);

57       run;

NOTE: DATA statement used (Total process time):

      real time           20.03 seconds

      cpu time            0.03 seconds

58

59       /**********************************************/

60       /*  OPEN THE EXCEL FILE CONTAINING THE MACRO. */

61       /**********************************************/

62       data _null_;

63           file cmdexcel;

SYMBOLGEN:  Macro variable MACRFILE resolves to

            '[open("M:\ADC-P\CPSPB\Macros\Chdaily_test_.xlsm")]';

64           put &macrfile;  *open the file containing the macro;

65       run;

ERROR: File is in use, EXCEL|SYSTEM.

NOTE: The SAS System stopped processing this step because of errors.

NOTE: DATA statement used (Total process time):

      real time           0.00 seconds

      cpu time            0.00 seconds

[Reeza · Accepted solution]

Rather than your initial Data _null_ step where you open the system via registry try opening by X command?

You say it runs 3 times and then fails...are you looping the macro and thats where it fails or on the third try of the same open. 

You do have to make sure Excel has enough time to process the commands SAS sends to it and closes down other wise some funny things can happen. I'm also including a sample of what my close/quit looks like...

x ' "C:\Program Files\Microsoft Office\Office11\excel.exe" ';

filename cmdexcel;

data _null_;

     x=sleep(5);

run;

data _null_;

file cmdexcel;

put &macrfile:

run;

.... other sas code

data _null_;

file cmdexcel;

put '[save.as("new file name")]';

put '[close()]';

x=sleep(3);

put '[quit()]';

run;

HTH,

Reeza

[SASHunter]

Reeza,

Thanks!  By changing the way EXCEL opens, it seems to work just fine now.  It's funny, I've had this program for years and it always baffled me why one time Excel would open with everything running fine and other times it would open Excel but not run the macro. I always got the same error. 

ERROR:  File is in use, EXCEL|SYSTEM.   I would just close SAS and EXCEL, then reopen SAS and run it again.  It would work the second time.

This seems to have fixed the problem.  This program has to run next week also, so I'll post if it was successful at the end of the week.

Thanks much.

Nancy