*
;
/*
From: adamhndrx@aol.com (AdamHndrx)
Newsgroups: comp.soft-sys.sas
Subject: Re: SAS macros driving Excel/VB macros
Date: 18 Jan 1996 11:18:14 -0500
Organization: America Online, Inc. (1-800-827-6364)

Here is a macro I wrote a few years back that submits WordBasic commands
from SAS to Word for Windows 6.0 and a sample of it in use.  The same
method
can be applied to Excel.  What I did was create a macro in Word and save
it to
a text file which I then converted to a data _null_ which put the command
to a
DDE filename call to 'WinWord|System'.  Excel macro command can be
submitted using 'Excel|System'.  Both SAS -dms and Windows application
have to be running to use this method.

Adam Hendricks
ICOS Corporation
Bothell, WA

============= Macro =================*/

*****************************************************************;
*                                                               *;
* Program: gettable.sas                                         *;
*                                                               *;
*  Author: Adam Hendricks                                       *;
*                                                               *;
*    Date: 08AUG94                                              *;
*                                                               *;
* Purpose: Uses Word for Windows as a print server for SAS dms. *;
*                                                               *;
*****************************************************************;

*** Necesary Option Statements ***;
options noxwait noxsync macrogen;

**************************************************************************
***;
***                                                                      
***;
*** SAS Macro for using Word for Windows as a print server for SAS dms.  
***;
***                                                                      
***;
*** Required global macro variable:                                      
***;
***                                                                      
***;
***    print_it:                  
***;
***                                                                      
***;
*** Required local macro variables:                                      
***;
***                                                                      
***;
***     file:                          
***;
***                                                                      
***;
***    study:             
***;
***                                                                      
***;
***     type: Either T(able) or L(isting).                               
***;
***                                                                      
***;
***   number: .
***;
***                                                                      
***;
**************************************************************************
***;
%macro wordprnt(file=, outfile=, study=, type=, number=);
data _null_;
  file cmds;

  *** Open SAS Table to Buffer ***;
  put '[FileOpen.Name = "' "&file" '"]';

  *** Word Page Setup ***;
  *** Set-up Landscape format ***;
  put '[FilePageSetup.PageWidth = "11" + Chr$(34)]';
  put '[FilePageSetup.PageHeight = "8.5" + Chr$(34)]';
  put '[FilePageSetup.Orientation = 1]';

  *** Set bottom of page at 1 inch ***;
  put '[FilePageSetup.BottomMargin = "1" + Chr$(34)]';

  *** Set left margin at 1 inch ***;
  put '[FilePageSetup.LeftMargin = "1" + Chr$(34)]';

  *** Set right margin at 1.5 inches ***;
  put '[FilePageSetup.RightMargin = "1.5" + Chr$(34)]';

  *** Set Top of Page at 1.5 inches ***;
  put '[FilePageSetup.HeaderDistance = "1.5" + Chr$(34), .TopMargin = "2"
+ Chr$(34)]';

  *** Delete First Page Break ***;
  put '[EditGoTo.Destination = "l1"]';
  put '[LineDown 1, 1]';
  put '[EditClear]';

  *** Word Font Setup ***;
  *** LinePrinter Font (Same as SAS uses). 10 characters/inch. ***;
  put '[EditGoTo.Destination = "l1"]';
  put '[EditSelectAll]';
  put '[FormatFont .Font = "LinePrinter", .Points = "10"]';

  *** Shift All Text Over 1/2 Inch to Left ***;
  put '[EditGoTo.Destination = "l1"]';
  put '[EditSelectAll]';
  put '[FormatParagraph.LeftIndent = "-0.5"]';

  *** Word Header Setup ***;
  *** Open Header ***;
  put '[ViewHeader]';

  put '[FormatFont .Font = "LinePrinter", .Points = "10"]';

  *** Put study number at top-left of page ***;
  put '[Insert "STUDY ' "&STUDY" ' PAGE "]';
  put '[CharLeft 5]';

  *** Put 'PAGE  OF ' at top-right of page (x=page, y=total pages).
***;
  put '[Insert "                                                          
 "]';
  put '[Insert "       "]';
  put '[HLine 5]';
  put '[Insert "                                                "]';
  put '[CharRight 5]';
  put '[InsertField.Field = "PAGE  \* MERGEFORMAT"]';
  put '[Insert " OF "]';
  put '[InsertField.Field = "NUMPAGES  \* MERGEFORMAT"]';

  *** Close Header ***;
  put '[CloseViewHeaderFooter]';

  *** Add table or listing number to table. ***;
  put '[EditGoTo.Destination = "l1"]';

%if %upcase(&type) eq L
%then put '[EditReplace .Find = "LISTING:", .Replace = "LISTING '
"&number" ':", .ReplaceAll]';
%else put '[EditReplace .Find = "TABLE:", .Replace = "TABLE ' "&number"
':", .ReplaceAll]';;

  *** Print Word Document ***;
%if %upcase(&print_it) eq Y
%then put '[FilePrint]';;

  *** Save as Word Document with New Name. ***;
  put '[FileSaveAs.Name = "' "&outfile" '", .Format = 2, .AddToMru = 1]';

  *** Close File ***;
  put '[FileClose]';
run;
%mend;

*** End of Code ***;

================ Call to macro ======================

******************************************************************;
*                                                                *;
*  Program: sas2word.sas                                         *;
*                                                                *;
*   Author: Adam Hendricks                                       *;
*                                                                *;
* Location: s:\v606\p9207\FINAL\CODE                             *;
*                                                                *;
*     Date: 17AUG94                                              *;
*                                                                *;
*  Purpose: Converts all tables & listings to Word 6.0 format.   *;
*                                                                *;
******************************************************************;
*** SAS to Word Conversion Code ***;
filename wordmac 'c:\sascode\gettable.sas';

*** Include SAS to Word Conversion Code ***;
%include wordmac/nosource2;

*** Set-up DDE between SAS and WinWord ***;
filename cmds dde 'WinWord|System';

**************************************************************************
***;
***                                                                      
***;
*** SAS Macro for using Word for Windows as a print server for SAS dms.  
***;
***                                                                      
***;
*** Required global macro variable:                                      
***;
***                                                                      
***;
***    print_it:                  
***;
***                                                                      
***;
*** Required local macro variables:                                      
***;
***                                                                      
***;
***     file:                          
***;
***                                                                      
***;
***    study:             
***;
***                                                                      
***;
***     type: Either T(able) or L(isting).                               
***;
***                                                                      
***;
***   number: 
. ***; *** ***; ************************************************************************** ***; *** Send Print Command to Word After Conversion ***; %let print_it = Y; *** Call Word Print Macro ***; *** Tables ***; %wordprnt(FILE=c:\reports\table1.lst, /* Input File (text) */ OUTFILE=c:\reports\table1.doc, /* Output File (Word Document) */ STUDY=9501, /* Study Number */ TYPE=T, /* Table or Listing */ NUMBER=1 /* Table or Listing Number */); *** End of Program ***; *;