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
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
Create this macro in a word template.
and do in SAS:
options noxwait noxsync;
put '[FileOpen .Name = "' "C:\temp\doc1.doc" '"]';
'[macro1]' ; /* call the macro to update the links */
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:
' Macro recorded 7/13/2004 by kuekawa'
.UpdateLinksAtOpen = True
' Macro recorded 7/13/2004 by kuekawa'
.UpdateLinksAtOpen = False
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
<=== 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
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
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.
the Microsoft Setup program.
b In Select Features, expand Office tools.
c Select Digital Signature
for VBA projects and select
d In Windows Explorer>Office,
3 Sign the macro.
a Select Tools=>Macro and then select the Visual Basic
b In the Project explorer, select the project you want to sign
c Select Tools>Digital
d To use the current certificate, select OK.
4 Add the source.
a Select Tools>Macro>Security
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