My colleague Steph McGrew posted a question back in Feb 2000 when she was looking for a way to command Word 97 in ruuning a (visual basic) macro. Steph passed away two weeks ago and I have been continuing her work since.

With assistance from a number of people here at Deja.com and those at SAS Institute, I have completed her work and would like to share with people who may be looking for help in this same area. This is for Steph...

The original intent is to automate a process which creates a large number of single-page customer-specific reports with data table and charts. The first approach is to use only Excel 97 and duplicate/repopulate the worksheets. However, we ran into a "not enough memory" error after creating thirty some worksheets. From then on we had to press the 'OK' button four or five times for each additional worksheets created. We were told this error exists in Windows 98 OS.

The second approach uses Excel as a "pass-by" template with only one worksheet containing the data table and charts that get updated every time SAS dumps data in a do-loop. The data and charts will be copied to the clip board and then pasted into a Word template document which stores the final reports.

Key elements include: SAS codes, Excel template, Excel macros, Word template and Word macros. Please note that Word's VB syntax is different from Excel's. The following are some of the codes:

* SAS do-loop statement; * determine number of runs needed; data subs; set data(keep=customer); by customer; if first.customer; run;

proc sql; select count(*) into :loopto from subs;

*Run macro and get rid of already completed subs after processing; %macro once; %do i=1 %to &loopto.; data current subs; set subs; if _n_=1 then output current; /* first observation, currently processed */ else output subs; run;

filename cmds dde 'EXCEL|SYSTEM'; filename cmdword dde 'WINWORD|SYSTEM';

/* outputting customer-specific data to Excel and update the worksheet with new data -> send data and charts to the clip board */

* call an Excel macro to copy the data range to the clip board; data _null_; file cmds; put %unquote(%str(%'[RUN("PROGRESS.XLS!CopyTableToClipBoard")]%')); run;

* call a Word macro to paste the data range from the clip board; data _nulll_; file cmdword; put %unquote(%str(%'[ToolsMacro .Name = "PasteTableFromClipBoard", .Run]%')); run;

* call an Excel macro to copy the charts to the clip board; data _null_; file cmds; put %unquote(%str(%'[RUN("PROGRESS.XLS!CopyChartsToClipBoard")]%')); run;

* call a Word macro to paste the charts from the clip board; data _null_; file cmdword; put %unquote(%str(%'[ToolsMacro .Name = "PasteChartsFromClipBoard", .Run]%')); run;

%end; %mend once;

%once; * the end of SAS codes;

< Excel macro CopyTableToClipBoard > Sub CopyTableToClipBoard() ' ' CopyTableToClipBoard Macro ' Macro recorded 04/28/2000 by Preferred Customer '

' Range("A5:L13").Select Selection.Copy End Sub

< Excel macro CopyChartsToClipBoard > Sub CopyChartsToClipBoard() ' ' CopyChartsToClipBoard Macro ' Macro recorded 04/25/2000 by Preferred Customer '

' ActiveSheet.ChartObjects("Chart 3").Activate ActiveChart.ChartArea.Select ActiveWindow.Visible = False Windows("PROGRESS.XLS").Activate ActiveSheet.Shapes.Range(Array("Chart 3", "Chart 4")).Select ActiveWindow.LargeScroll Down:=1 ActiveSheet.Shapes.Range(Array("Chart 3", "Chart 4", "Chart 5")).Select ActiveSheet.Shapes.Range(Array("Chart 3", "Chart 4", "Chart 5", "Chart 6")). _ Select Application.CutCopyMode = False ActiveWindow.WindowState = xlNormal ActiveWindow.WindowState = xlMaximized ActiveWindow.WindowState = xlNormal ActiveWindow.WindowState = xlMaximized ActiveWindow.WindowState = xlNormal ActiveWindow.WindowState = xlMaximized ActiveWindow.WindowState = xlNormal ActiveWindow.WindowState = xlMaximized Selection.Copy End Sub

< Word macro PasteTableFromClipBoard > Sub PasteTableFromClipBoard() ' ' PasteTableFromClipBoard Macro ' Macro recorded 04/28/00 by Preferred Customer ' Selection.PasteSpecial Link:=False, DataType:=wdPasteMetafilePicture, _ Placement:=wdInLine, DisplayAsIcon:=False Selection.TypeParagraph Selection.TypeParagraph End Sub

< Word macro PasteChartsFromClipBoard > Sub PasteChartsFromClipBoard() ' ' PasteChartsFromClipBoard Macro ' Macro recorded 05/01/00 by Preferred Customer ' Selection.PasteSpecial Link:=False, DataType:=wdPasteMetafilePicture, _ Placement:=wdInLine, DisplayAsIcon:=False Selection.InsertBreak Type:=wdPageBreak End Sub

Sent via Deja.com http://www.deja.com/ Before you buy.