Date: Wed, 15 Jun 2005 17:18:50 -0600
Reply-To: Alan Churchill <SASL001@SAVIAN.NET>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Alan Churchill <SASL001@SAVIAN.NET>
Subject: Re: MSWord, MailMerge, and reading SAS data
In-Reply-To: <42b0b110$0$3228$5a62ac22@per-qv1-newsreader-01.iinet.net.au>
Content-Type: text/plain; charset="US-ASCII"
Scott,
I'm talking to my developers about this issue. We have a major release
coming up next week but we'll try and code up a sample solution. When we do,
I'll post it to our website for anyone to download. I'll also let you know
about it directly.
Possible ways off of the top of my head:
- ODBC
- XML
- DDE
- OleDB
- InfoPath
- VSTO (programmer utility. Doesn't require Word or Excel to be installed on
the server)
- Delimited files
If you want areal quick and dirty way then look at the example I posted
yesterday on using XML and Word. Basically, save your existing word doc as
XML, find the fields in the XML doc that correspond to the data fields you
need to replace and then loop through a macro replacing those values:
XML Word Doc
|
V
Put header pieces (things that don't change) in an external XML file and
include them
|
V
%macro createXMLDataStatements
|
V
Put footer pieces (things that don't change) in an external XML file and
include them.
|
V
Write new XML to disk.
If you want to pursue this less than optimal solution look for messages I
sent last week and this week on Word and Excel.
We'll try and code up a VSTO solution for you in the next few days. We've
been meaning to do it anyway to start building some SAS to Office bridge
products. Bear with us.
If you want to see some sample code, look on our website under utilities and
download the sample app for the web site. You can see the call to SAS in
that file.
Thanks,
Alan
Savian
"Bridging SAS and Microsoft Technologies"
http://www.savian.net
-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Scott
Bass
Sent: Wednesday, June 15, 2005 3:54 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: MSWord, MailMerge, and reading SAS data
Hi Alan and David,
Thanks for the replies, much appreciated.
More details may be helpful: the end users are medical writers. They don't
have SAS, don't know SAS, don't want to know SAS. Right now they cut and
paste various statistics into reports. They will use MS Word to create
those reports.
The final solution needs to be as easy as possible for them. I envision a
dictionary of fields they insert in the appropriate place in the doc, with
those fields resolving to the proper data when the "resolution process"
(MailMerge, Macro, whatever) takes place. If I can setup the initial
template, hopefully they can take over from there and extend as necessary
without programming support.
Yes, Alan, sample code or links would be fantastic. I did look at MS Word's
MailMerge feature before posting, but on first glance it didn't look that
extensible. I hope that's just my unfamiliarity with Word's capabilities
(I've never used MailMerge). You also said there are "lots of ways of
handling" this. Could you give a bulleted list off the top of your head?
That would help me Google for various approaches. How much is VSTO, and
would the medical writers need it or just the programmers?
If someone could talk me thru reading in a single data value into Word, that
would be good, i.e. what field attributes do I choose (database I presume?),
etc. If there is a way to setup a SAS dataset as an ODBC DSN, I would be
interested in that as well. It looks like I need to connect to a server to
do this (bummer), rather than just define attributes to the file itself (vs.
ODBC connection to Excel, for example).
I realize these are MS centric questions, so feel free to shoo me off to
another Usenet group. However, I was hoping someone here might have done
this exact scenario already.
Kind Regards,
Scott
"Alan Churchill" <SASL001@SAVIAN.NET> wrote in message
news:200506151712.j5FHCrcZ010183@listserv.cc.uga.edu...
> Scott,
>
> There are loads of ways of handling this so you will have to figure out
> which approach you want to use.
>
> If I was doing it, I would access the SAS datasets using OleDB using the
> new
> Visual Studios Tools for Office. This keeps you from having Word installed
> and gives you complete control over how to handle. This is clean and fits
> into a nice structured means of handling.
>
> An alternative is to export SAS out as XML and then have Word read in that
> XML and populate the fields. Again, I would use VSTO but you could
> probably
> do it in VBA or macro code as well.
>
> Again, lots of other ways of approaching...The above is how I would do it.
> If you need some sample code or links, let me know.
>
> Thanks,
> Alan
>
> Savian
> "Bridging SAS and Microsoft Technologies"
> http://www.savian.net
>
>
> -----Original Message-----
> From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Scott
> Bass
> Sent: Wednesday, June 15, 2005 9:48 AM
> To: SAS-L@LISTSERV.UGA.EDU
> Subject: MSWord, MailMerge, and reading SAS data
>
> Hi,
>
> Is there a way to have SAS data read into MSWord? Sort of like PROC
> FSLETTER, but using MSWord instead.
>
> We have various reports written in Word, with statistics cut-and-pasted
> in.
> It would be nice to automate that process, where those statistics are in a
> SAS dataset (or could be imported into SQL, Access, Excel, etc if
> necessary), and fields marking those statistics in the Word doc. Then,
> run
> MailMerge, some Word macro, etc, and the fields update with the data in
> the
> SAS dataset.
>
> Thanks,
> Scott
| 
