ExcelOpen.sas

/****************************************************************************/
/*  Program Name  : ExcelOpen                                               */
/*  Purpose       : Macro utility to open Excel application or specific     */
/*                  Excel file. Terminates if process takes longer than     */
/*                  10 seconds.                                             */
/*                                                                          */
/*--------------------------------------------------------------------------*/
/*  Author        : Matt Trzcinski                                          */
/*  Last Update   : 2017/04/29                                              */
/*                                                                          */
/*--------------------------------------------------------------------------*/
/*  Input(s)      : None or Excel file (.xlsx, .xls, .xlsm, .xlsb, .csv)    */
/*  Output(s)     : None                                                    */
/*  Usage         : To open an Excel file, pass the full file path into     */
/*                  ExcelOpen(). Arguments may be either quoted or          */
/*                  unquoted. Arguments are not case sensitive. To open     */
/*                  the Excel application leave argument blank. A single    */
/*                  Excel session is forced to avoid conflicts.             */
/*                                                                          */
/*--------------------------------------------------------------------------*/
/*  Example(s)    : Open an Excel file:                                     */
/*                                                                          */
/*                    %ExcelOpen(C:\filepath\name.xlsx);                    */
/*                                                                          */
/*                  Open the Excel application:                             */
/*                                                                          */
/*                    %ExcelOpen();                                         */
/*                                                                          */
/*--------------------------------------------------------------------------*/
/*  Note(s)       : Program checks if the Excel application has opened by   */
/*                  attempting to establish an Excel|System DDE link.       */
/*                  ExcelOpen() may run longer than 10 seconds in the case  */
/*                  where the Excel application itself has not been fully   */
/*                  loaded due to OS delays.  In terms of DATA step         */
/*                  diagnostics, ExcelOpen() times out according to the     */
/*                  'cpu time' and not the 'real time'. If the process      */
/*                  hangs on a data step, try closing all Mircosoft         */
/*                  applications before running this macro.                 */
/*                                                                          */
/*                  It has been observed that if a Word document is open    */
/*                  prior to running ExcelOpen() then the process hangs at  */
/*                  the first fopen statement.  Upon closing the document,  */
/*                  the Macro will run. Once the process has run once, then */
/*                  it no longer matters whether any other MS applications  */
/*                  are open. After closing Word, ExcelOpen() proceeds with */
/*                  its normal function. Similar behavior has been observed */
/*                  with other MS applications such as Skype (Lync) and     */
/*                  Outlook.  If the operation hangs and does not time out, */
/*                  try closing all MS applications.                        */
/*                                                                          */
/*                  Exercise caution when opening a workbook from a network */
/*                  drive. Excel must open before loading the workbook.     */
/*                  Since ExcelOpen() checks for whether the Excel          */
/*                  application has loaded, program execution may continue  */
/*                  before the workbook has been opened due to network      */
/*                  delays. If a workbook DDE link is attempted prior to    */
/*                  the workbook having loaded, an error will occur. See    */
/*                  %EstablishDDELink to prevent this type of error.        */
/*                                                                          */
/*  Development   :                                                         */
/*                                                                          */
/****************************************************************************/

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

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

%macro ExcelOpen(inFile) / minoperator mindelimiter = ',';
%put NOTE: [&SYSMACRONAME] Executing: ExcelOpen(inFile=&inFile);

  %let macroStart    = %sysfunc(datetime());

  %let originalNOTES = %sysfunc(getoption(notes));
  %let originalXWAIT = %sysfunc(getoption(xwait));
  %let originalXSYNC = %sysfunc(getoption(xsync));

  options nonotes noxsync noxwait;

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

** Verifty Arguments

********************************************************************;
  %if %IsEmpty(inFile) %then %let filePath = EXCEL;
  %else %let filePath = %sysfunc(dequote(&inFile.));

  %let fileExtension = %substr(&filePath., %sysfunc(findc(&filePath., ., B)) + 1);

  %if not (%upcase(&fileExtension.) in (XLSX, XLS, XLSM, XLSB, CSV, EXCEL))%then %do;
    %put ERROR: [&SYSMACRONAME] Invalid input. Not an Excel file.;
    %SetSystemOptions(&originalNOTES., &originalXWAIT., &originalXSYNC.);
    %abort cancel;
    %end;

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

** Attempt to open Excel/Test whether Excel has opened

********************************************************************;
  /*filePath must be quoted in case path contains space.

    Double quotes are used on "EXCEL" to match those

    surrounding the resolved &filePath. The switch /embed

    prevents the Excel splash screen from appearing and

    prevents a new workbook from being created.*/
  %if "&filePath." = "EXCEL" %then %do;
    %let cmdStatement = start excel;
    %let openMessage  = Excel application;
    %end;
  %else %do;
    %let cmdStatement = start excel /embed "&filePath.";
    %let openMessage  = &filePath.;
    %end;

  filename xlDDE dde 'excel|system';

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

  %if &linkConnection > 0 %then %do;
    %put ERROR: [&SYSMACRONAME] Excel already open.;
    %SetSystemOptions(&originalNOTES., &originalXWAIT., &originalXSYNC.);
    %abort cancel;
    %end;
  %else %do;
    %SetSystemOptions(notes);
    %put NOTE: [&SYSMACRONAME] Opening &openMessage..;
    %SetSystemOptions(nonotes);
    %sysexec(&cmdStatement.);

    /*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] Operation timed out.;
        %sysexec(taskkill /F /IM EXCEL.EXE);
        %SetSystemOptions(&originalNOTES., &originalXWAIT., &originalXSYNC.);
        %abort cancel;
        %end;

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

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

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

** Housekeeping

********************************************************************;
  filename xlDDE clear;
  %SetSystemOptions(notes, &originalXWAIT., &originalXSYNC.);

  %put NOTE: [&SYSMACRONAME] Succesfully opened &openMessage..;
  %put NOTE: [&SYSMACRONAME] EXCELOPEN 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