ExcelClose.sas

/****************************************************************************/
/*  Program Name  : ExcelClose                                              */
/*  Purpose       : Macro utility to close Excel application with option    */
/*                  to save.                                                */
/*                                                                          */
/*--------------------------------------------------------------------------*/
/*  Author        : Matt Trzcinski                                          */
/*  Last Update   : 2017/05/03                                              */
/*                                                                          */
/*--------------------------------------------------------------------------*/
/*  Input(s)      : None                                                    */
/*  Output(s)     : None                                                    */
/*  Usage         : Takes argument 'save' which must be either 'yes' or     */
/*                  'no'.  If file workbook already has name, then the file */
/*                  will be overwritten with that name.  If workbook has no */
/*                  name (i.e. has never been saved), then Excel will open  */
/*                  a 'save as' prompt.  If given a 'save as' prompt, the   */
/*                  user MUST save the file, otherwise a fatal error occurs.*/
/*                  A fatal error will also occur if no workbook is open and*/
/*                  save option is 'yes'. Owing to the implementation, it is*/
/*                  not possible to rename a pre-existing file or to        */
/*                  automatically name a new file.                          */
/*                                                                          */
/*--------------------------------------------------------------------------*/
/*  Example       : %CloseExcel(save=yes);                                  */
/*                  %CloseExcel(save=no);                                   */
/*                  %CloseExcel();                                          */
/*                                                                          */
/*--------------------------------------------------------------------------*/
/*  Note(s)       : Save cannot occur if cell is being edited. If the       */
/*                  process hangs, check that the cursor is not currently   */
/*                  inside of a cell.  Once the cell is no  longer being    */
/*                  edited, the process will resume.                        */
/*                                                                          */
/*  Developer     : At the end of this file are excerpts from the Microsoft */
/*                  Excel Macro Functions Help file detailing functions     */
/*                  useful to this program.                                 */
/*                                                                          */
/*                  Two known fatal errors can occur: Pressing close/cancel */
/*                  in 'save as' prompt or having no workbook open.  When   */
/*                  close/cancel is pressed, a FALSE value is returned by   */
/*                  Excel.  This may be what causes the error. The client   */
/*                  /server relationship is one directional. There does not */
/*                  seem to be a function which can handle no workbook being*/
/*                  open.  There seems to be no way to check if a workbook  */
/*                  is not open other than an error being produced.  When a */
/*                  fatal error is generated, it is unclear which case      */
/*                  triggered it. Having no workbook open causes an infinite*/
/*                  loop with this construction.  A counter has been set to */
/*                  terminate the loop after 5 iterations.  The user is     */
/*                  notified in the log of status.                          */
/*                                                                          */
/*                  Note that GOTO construction has been used to handle the */
/*                  fatal error scenario, no try/catch type construction    */
/*                  being available.                                        */
/*                                                                          */
/****************************************************************************/

%macro IsEmpty(macroVariable);
  %sysevalf(%superq(&macroVariable)=, boolean)
%mend;

%macro SetSystemOptions(opt1, opt2, opt3);
  options &opt1. &opt2. &opt3.;
%mend;

%macro ExcelClose(save) / minoperator mindelimiter = ',';
%if %IsEmpty(save) %then %put NOTE: [&SYSMACRONAME] Executing: ExcelClose(save=no);
%else                    %put NOTE: [&SYSMACRONAME] Executing: ExcelClose(save=&save);

  %let macroStart    = %sysfunc(datetime());
  %let originalNOTES = %sysfunc(getoption(notes));

  options nonotes;

********************************************************************

** Verify argument

********************************************************************;
  %if %IsEmpty(save) %then %let save = NO;

  %if not (%upcase(&save.) in (YES, NO)) %then %do;
    %put ERROR: [&SYSMACRONAME] Invalid argument.  Must be yes/no.;
    %SetSystemOptions(&originalNOTES.);
    %abort cancel;
    %end;

********************************************************************

** Verify Excel is open

********************************************************************;
    filename xlDDE dde 'excel|system';

    %let linkConnection = %sysfunc(fopen(xlDDE, S));

    %if (&linkConnection. <= 0) %then %do;
      %put ERROR: [&SYSMACRONAME] Excel not open. Failed to close Excel.;
      %SetSystemOptions(&originalNOTES.);
      %abort cancel;
      %end;

    %let closeReturnCode = %sysfunc(fclose(&linkConnection));

********************************************************************

** Attempt to close Excel

********************************************************************;
  %let errorOccurred  = 0;
  %let counter        = 0;

  %if %upcase(&save.) = YES %then %do;
      %label_Return_Here_On_Error: ;
      data _null_;
        file xlDDE;
        put '[CLOSE(TRUE, FALSE)]';
        put '[QUIT()]';
      run;
    %if &SYSERR. ne 0 %then %do;
      %put WARNING: [&SYSMACRONAME] Press SAVE at prompt or open a workbook. ;
      %let counter = %eval(&counter. + 1);
      %if (&counter. > 4) %then %do;
        %put ERROR: [&SYSMACRONAME] Fatal error unavoidable. ;
        %let errorOccurred = 1;
        %goto label_Fatal_Error;
        %end;
      %else %goto label_Return_Here_On_Error;
      %end;
    %end;
  %else %do;
  /*Shell command 'taskkill /F /IM EXCEL.EXE' not used as it

    may cause a 'not closed properly' type response upon

    Excel reopen.*/
    data _null_;
      file xlDDE;
      put '[ERROR(FALSE)]';
      put '[QUIT()]';
    run;
  %end;

  %label_Fatal_Error: ;

********************************************************************

** Housekeeping

********************************************************************;
  filename xlDDE clear;
  options notes;

  %if &errorOccurred. %then %let successMessage = Unable to close Excel.;
  %else %if %upcase(&save.) = YES %then %let successMessage = Successfully closed Excel. File saved.;
  %else %let successMessage = Successfully closed Excel. File not saved.;

  %put NOTE: [&SYSMACRONAME] &successMessage;
  %put NOTE: [&SYSMACRONAME] EXCELCLOSE macro used (Total process time):;

  %let duration = %sysfunc(putn(%sysevalf(%sysfunc(datetime())-&macroStart.), time12.3));
  %if %sysfunc(minute("&duration."t)) > 0 %then %do;
    %put NO%str(TE-)         real time            %substr(&duration., 3, 8);
    %end;
  %else %do;
    %put NO%str(TE-)         real time            %substr(&duration., 6, 5) seconds;
    %end;

  %SetSystemOptions(&originalNotes.);

%mend;

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

Excerpt from "MACROFUN.HLP" file:

  CLOSE



  Macro Sheets Only



  Closes the active window. In Microsoft Excel for

  Windows, CLOSE is equivalent to choosing the Close command from the

  Document Control menu. In Microsoft Excel for the Macintosh, CLOSE is

  equivalent to clicking the close box.



  Syntax



  CLOSE(save_logical, route_logical)





  Save_logical is a logical value that specifies whether to save the

  file before closing the window.



  Save_logical        Result

  ---------------------------------



  TRUE                Saves the file



  FALSE         Does not save the file



  Omitted       If you 've made changes to the file,

          displays a dialog box asking if you want to

          save the file





  Route_logical is a logical value that specifies whether to route the

  file after closing it. This argument is ignored if there is not a

  routing slip present.



  Route_logical      Result

  --------------------------------



  TRUE               Routes the file



  FALSE              Does not route the file



  Omitted            If you've specified recipients for routing,

        displays a dialog box asking if you want to save

        the file



  Remarks



  Users of Microsoft Excel versions earlier than 4.0 should note that if

  the macro sheet containing the function is the active sheet, CLOSE now

  closes the workbook.



  Note



  When you use the CLOSE function, Microsoft Excel does not run any

  Auto_Close macros before closing the workbook.

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

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

Excerpt from "MACROFUN.HLP" file:

  ERROR



  Macro Sheets



  Only Specifies what action to take if an error is encountered while a

  macro is running. Use ERROR to control whether Microsoft Excel error

  messages are displayed, or to run your own macro when an error is

  encountered.



  Syntax



  ERROR(enable_logical, macro_ref)



  Enable_logical is a logical value or number that selects or clears

  error-checking.



    + If enable_logical is FALSE or 0, all error-checking is cleared. If

      error-checking is cleared and an error is encountered while a

      macro is running, Microsoft Excel ignores it and

      continues. Error-checking is selected again by an ERROR(TRUE)

      statement, or when the macro stops running.



    + If enable_logical is TRUE or 1, you can either select normal

      error-checking (by omitting the other argument) or specify a macro

      to run when an error is encountered by using the macro_ref

      argument. When normal error-checking is active, the Macro Error

      dialog box is displayed when an error is encountered. You can

      choose to halt the macro, start single-stepping through the macro,

      continue running the macro normally, or go to the macro cell where

      the error occurred.



    + If enable_logical is 2 and macro_ref is omitted, error-checking is

      normal except that if the user chooses the Cancel button in an

      alert message, ERROR returns FALSE and the macro is not

      interrupted.



    + If enable_logical is 2 and macro_ref is given, the macro goes to

      that macro_ref when an error is encountered. If the user chooses

      the Cancel button in an alert message, FALSE is returned and the

      macro is not interrupted.



  Macro_ref specifies a macro to run if enable_logical is TRUE, 1, or 2

  and an error is encountered. It can be either the name of the macro or

  a cell reference. If enable_logical is FALSE or 0, macro_ref is

  ignored.



  Important



  Both ERROR(FALSE) and ERROR(TRUE, macro_ref ) keep Microsoft Excel

  from displaying any messages at all, including the message asking

  whether to save changes when you close an unsaved document. If you

  want alert messages but not error messages to be displayed, use

  ERROR(2, macro_ref ).



  Remarks



  You can use GET.WORKSPACE to determine whether error-checking is on or

  off.



  Examples



  ERROR(FALSE) clears error-checking.



  ERROR(TRUE, Recover) selects error-checking and runs the macro named

                      Recover when an error is encountered.



  The following macro runs the macro ForceMenus if an error occurs in

  the current macro:



  =ERROR(TRUE, ForceMenus)



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

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

Excerpt from "MACROFUN.HLP" file:

  QUIT



  Macro Sheets Only



  Equivalent to choosing the Exit command from the

  File menu in Microsoft Excel for Windows. Equivalent to choosing the

  Quit command from the File menu in Microsoft Excel for the

  Macintosh. Quits Microsoft Excel and closes any open documents. If

  open documents have unsaved changes, Microsoft Excel displays a

  message asking if you want to save them. You can use QUIT in an

  Auto_Close macro to force Microsoft Excel to quit when a particular

  worksheet or macro sheet is closed.



  Syntax



  QUIT( )



  ------------------------------

  Caution: If you have cleared error-checking with an ERROR(FALSE)

          function, QUIT will not ask whether you want to save changes.

  ------------------------------



  Remarks



    When you use the QUIT function, Microsoft Excel does not run any

    Auto_Close macros before closing the document.



  Examples



    The following function displays a confirmation alert and quits

    Microsoft Excel if the user chooses OK:



    IF(ALERT("Are you sure you want to quit Microsoft Excel?",1), QUIT(),)



  Related Function



    FILE.CLOSE Closes the active document

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

Powered by peut-publier

©2020 Matt Trzcinski