*
;
/*
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 ***;
*;