EstablishDDELink.sas

/****************************************************************************/
/*  Program Name  : EstablishDDELink                                        */
/*  Purpose       : Macro utility to establish DDE Link with Excel before   */
/*                  allowing program execution to continue.  Terminates     */
/*                  program if connection is not established within 10      */
/*                  seconds.                                                */
/*                                                                          */
/*--------------------------------------------------------------------------*/
/*  Author        : Matt Trzcinski                                          */
/*  Last Update   : 2017/04/29                                              */
/*                                                                          */
/*--------------------------------------------------------------------------*/
/*  Input(s)      : Optional inputs marked with *.                          */
/*                  fileRef    = File reference. Cannot exceed 8 characters.*/
/*                  *dirData   = Directory of workbook.                     */
/*                  *fileName  = Name of workbook, including file extension.*/
/*                  *sheetName = Name of worksheet to connect with.         */
/*                  *range     = Range within workhseet, given as R#C#:R#C# */
/*  Output(s)     : None                                                    */
/*  Usage         : Two types of DDE links can be established: System and   */
/*                  Workbook.  A system link requires only the fileRef      */
/*                  input. A workbook link requires all inputs.             */
/*                                                                          */
/*--------------------------------------------------------------------------*/
/*  Example       : System Link:                                            */
/*                                                                          */
/*                      %EstablishDDELink(fileRef=xlDDE);                   */
/*                                                                          */
/*                      data _null_;                                        */
/*                        file xlDDE;                                       */
/*                        put '[QUIT()]';                                   */
/*                      run;                                                */
/*                                                                          */
/*                  Workbook Link:                                          */
/*                                                                          */
/*                      %EstablishDDELink(    fileRef= xlBook               */
/*                                        ,   dirData= C:\temp              */
/*                                        ,  fileName= example.xlsx         */
/*                                        , sheetName= Sheet1               */
/*                                        ,     range= R2C1:R400C5          */
/*                      );                                                  */
/*                                                                          */
/*                      data _raw_example;                                  */
/*                        infile xlBook dlm='09'x notab missover;           */
/*                        length                                            */
/*                          var1 $ 100                                      */
/*                          var2 $ 100                                      */
/*                          var3 8                                          */
/*                        ;                                                 */
/*                        input (_all_) (:) ;                               */
/*                      run;                                                */
/*                                                                          */
/*--------------------------------------------------------------------------*/
/*  Note          : Sometimes when reading a workbook from a network drive  */
/*                  program execution continues before the workbook is      */
/*                  loaded and a link can be established, causing errors.   */
/*                  This program ensures that this doesn't happen.          */
/*                                                                          */
/*                  Sometimes a workbook will open with 'PROTECTED VIEW'    */
/*                  toggled on.  When workbook protection is enabled, a     */
/*                  DDE link cannot be established.  In the workbook, a     */
/*                  gold status bar will appear beneath the ribbon with a   */
/*                  'Enable Editing' button.  Click this button to disable  */
/*                  the 'PROTECTED VIEW'.  This should only need to be done */
/*                  a single time.                                          */
/*                                                                          */
/*  Developer     : Provide better input verification.                      */
/*                                                                          */
/****************************************************************************/

%macro ClearFileRef(fileRef);
  filename &fileRef. clear;
%mend;

%macro EstablishSystemLink(fileRef);
  filename &fileRef. dde 'excel|system';
%mend;

%macro EstablishWorkbookLink(fileRef, dirData, fileName, sheetName, range);
  filename &fileRef. dde "excel|&dirData.\[&fileName.]&sheetName.!&range.";
%mend;

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

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

%macro EstablishDDELink(fileRef, dirData, fileName, sheetName, range);
%put NOTE: [&SYSMACRONAME] Executing: EstablishDDELink(fileRef=&fileRef, dirData=&dirData, fileName=&fileName, sheetName=&sheetName, range=&range);

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

  options nonotes;

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

** Validate arguments

********************************************************************;
  %if %IsEmpty(fileRef) %then %do;
    %put ERROR: [&SYSMACRONAME] fileRef is blank.;
    %SetSystemOptions(&originalNOTES.);
    %abort cancel;
    %end;

  %if %length(&fileRef.) > 8 %then %do;
    %put ERROR: [&SYSMACRONAME] Fileref &fileRef exceeds 8 character limit.;
    %SetSystemOptions(&originalNOTES.);
    %abort cancel;
    %end;

  %if not %IsEmpty(dirData) %then %let dirData = %sysfunc(dequote(&dirData.));

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

** Assign fileref according to link type

********************************************************************;
  %if     %IsEmpty(dirData)
      and %IsEmpty(fileName)
      and %IsEmpty(sheetName)
      and %IsEmpty(range) %then %EstablishSystemLink(&fileRef.);
  %else %EstablishWorkbookLink(&fileRef., &dirData., &fileName., &sheetName., &range.);

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

** Check that link has been established

********************************************************************;
  %let linkConnection = %sysfunc(fopen(&fileRef, S));

  %if not (&linkConnection. > 0) %then %do;

    /*Run until either Excel opens (linkConnection > 0)

      or until 10 seconds have passed.*/
    %let stopTime = %sysevalf(%sysfunc(datetime()) + 10);

    %do %until (&linkConnection. > 0);
      %if (%sysfunc(datetime()) >= &stopTime.) %then %do;
        %put ERROR: [&SYSMACRONAME] DDE system link was not established. Operation timed out.;
        %ClearFileRef(fileRef.);
        %SetSystemOptions(&originalNOTES.);
        %abort cancel;
        %end;

      %let linkConnection = %sysfunc(fopen(&fileRef, S));
      %end;
    %end;

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

** Housekeeping

********************************************************************;
  %let closeReturnCode = %sysfunc(fclose(&linkConnection));
  option notes;

  %put NOTE: [&SYSMACRONAME] DDE link %upcase(&fileRef) established.;
  %put NOTE: [&SYSMACRONAME] ESTABLISHDDELINK 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;

Powered by peut-publier

©2020 Matt Trzcinski