Kaz's SAS, HLM, and Rasch Model
Making Customized Reports using SAS DDE, Excel, and WORD
Home
Large National Data Sets
Kaz Library
SAS manuals
What is "error"?
Rasch Model
HLM
SAS PROC NLMIXED and GLIMMIX
Factor Analysis
Reading output via SAS
Excel functions for Statistical Analysis
Essays on learning statistics
Statistics in Japanese
My profile
My SAS questions and SAS's responses
My work tool box
Cutomized Reports
I need your help in improving this SAS program!
 
You are doing your survey at 100 organizations and you need to report the results to all of them.  Create a word document template.  Make graphs in them by using Excel with links.  Use SAS to populate both the EXCEL and the WORD document, so each report carries different statistics.

I need your help in improving this program.

 

MY syntax looks like you can almost automate the process such that if you need to report to 100 places, you can run the program overnight to find all 100 results the next morning.  I have tried to make this happen, but it has not happened.  As of now, you need to execute it one report at a time.

When running this syntax with OFFICE 2004 I get a message about updating the word document.  I have to sit in front of the computer all day to click YES every five minutes.  This is what I do with the current configuration of the program. 

In one machine that uses OFFICE 2000, for some reason, a click gets me all the reports, but when I open them, these documents update themselves automatically, such that the excel graphics will be updated to whatever the excel templates has at the last iteration, which is not good.  In other words, the results will be all the same in these reports, which is not good.

I wrote above as if the difference is the OFFICE's version difference, but I don't know if that is it.

 I need someone to get back to me and say "here is a sas syntax, as well as the VB macros embedded in the word document (see the contents of the zip file).  Try it, it works."  I look forward to hearing from you.  Many people have kindly suggested solutions.  I tried them and feel like I am almost there, but I never made it to work.

SOLUTION 1 that failed:

Open the template word document.  Go to TOOLS --> OPTIONS --> GENERAL --> Click off "Update Automatics Links OPEN"

Create this macro in a word template.

Sub Macro1()
    Selection.Fields.Update
End Sub

and do in SAS:

options noxwait noxsync;
filename cmds dde 'WinWord|System';
data _null_;
file cmds;
put '[FileOpen .Name = "' "C:\temp\doc1.doc" '"]';
put '[macro1]' ;  /* call the macro to update the links */
run;

So what this is doing is that first the word document's automatic updating is OFF, so the document just opens without asking a user whether he/she wants to update (which is good because otherwise, a user has to sit in front of the computer all day).  Still, because we want SAS to update info from the linked excel sheet, it excecutes the macro "macro 1" through SAS DDE.

I thought this worked on the day I did this, but next day it stopped working. 

 

SOLUTION 2 that failed:

In the word template create these two macros:

Sub SetOptions1()
'
' Macro5 Macro
' Macro recorded 7/13/2004 by kuekawa'
    With Options
        .UpdateLinksAtOpen = True
    End With
End Sub

Sub SetOptions0()
'
' Macro5 Macro
' Macro recorded 7/13/2004 by kuekawa'
    With Options
        .UpdateLinksAtOpen = False
    End With
End Sub

And in the same way as in SOLUTION 1, we control one or the other.  This failed also.  I could not automate the proess.  In one situation, the reports stopped responding to the udpate in the excel sheet.  In other situations, I open the result reports and they seem to contain the same results, while they have to be different.

SOLUTION 3 that I have not tried (suggested by SAS support)  But it seems this is about Excel rather thatn WORD.

<=== Page: 1 === SAS Consultant === emailed w/answer === 24Sep2004 10:58:28 ===>

How to disable the Macro prompt in Excel when running DDE
When an Excel spreadsheet that contains a macro is invoked, it will
prompt you to enable the macro. Excel will stop the SAS DDE program from
executing until the user responds to the prompt.  To disable the prompt,
do one of the following:

For Excel 2000 only.  You cannot do this with earlier versions of Excel.
Option 1 - Use medium security
1 Choose security level.
   a Select Tools>Options>Macro.
   b Select Security Level tab and select Medium.
2 Create macros that have a digital certificate.
   a Rerun the Microsoft Setup program.
   b In Select Features, expand Office tools.
   c Select Digital Signature for VBA projects and select
      My Computer>Run.
   d In Windows Explorer>Office, double-click SelfCert.exe.
3 Sign the macro.
   a Select Tools=>Macro and then select the Visual Basic Editor.
   b In the Project explorer, select the project you want to sign
   c Select Tools>Digital Signatures.
   d To use the current certificate, select OK.
4 Add the source.
   a Select Tools>Macro>Security Panel.
   b Select Trusted Sources tab.

 

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

I could give up and just create a report one by one.  But I cannot stand the fact that I am almost there to a perfect automation.I hope you can help me with this.  Email me at inquiry (at) estat.us

Enter supporting content here

Copyright 2005 KU
For information inquiry (AT) estat.us