My end users cannot access SQL Server.
OK.
I'm trying to get rid of the extra steps
they need to take and provide the merged docs for them.
Can I just clarify something here - do you mean that
a. if possible, you would do all the merge work at your end and
provide your end users with an output document (or perhaps some other
product such as a folder of output documents) or
b. for whatever reason, your end users will still have to perform the
merge(s) so you need to provide them with either a data source or a data
source + mail merge main document ?
If it's (a), you can consider "using mail merge to connect directly to
SQL Server", but you would need to be able to retrieve the required data
for each user using a SQL SELECT not more than 511 characters (possibly
255 in some cases) long. Otherwise, the only way to do it AFAIK is to
create a table-valued Transact-SQL query on the server that accepts the
date range parameters, and to get the syntax of your SQL QUERY in Word
right.
If you can't do either of those two types of query, you have a few
options, including:
c. create a mail merge data source that Word /can/ use. Maybe you
could say a bit more about your requirements and options there (e.g.
could you use comma- or tab-delimited data (appropriately wrapped with
text delimiters where necessary)? Do you have multiline data? Could you
create a .mdb? However, I couldn't say for certain that you would not
run into the same problem you have with Excel).
d. "Roll your own" mail merge that uses (say) VBA+ADO to get data from
SQL Server and insert it - e.g., one way is to use { DOCVARIABLE } fields
e. "Roll your own" mail merge that uses .docx format, its facility to
contain a private XML data store, and content controls instead of
fields. If it's a "letter" type merge, the Merge then consists of
pointing to the appropriate record in the store and letting Word fill
the content controls with the values in that record. Typically that is
done with .NET and/or VSTO but I think it is feasible with VBA. However,
it seems to me to be unnecessarily complicated if you can manage (c) or
(d). If you really want to pursue it I can try to find some pointers.
f. some combination of the above and/or other techniques.
If you have to do (b) then (c) would presumably still be workable, (d)
would not, and (e) would be, but only if you can guarantee that the code
you write can be executed on the end user platform.
Peter Jamieson
http://tips.pjmsn.me.uk
On 13/04/2010 19:30, Gwal wrote:
My end users cannot access SQL Server. We currently generate an Excel file
that is sent to them based on a date range they submit from a web page. They
are doing the merge. The issue they are having is character data that is all
numbers are treated as numbers in Excel during a simple import, and their
mearge fails unless they massage the columns. I just picked up this task
today and hoped XML would merge. I'm trying to get rid of the extra steps
they need to take and provide the merged docs for them.
I may try to writing something that merges the files programatically if that
makes any sense. The word 2007 .docx files are stored as XML. Have you
heard anything about taking this approach? Are there any links you can
provide if so?
Worst case I could write some VBA to do the formatting and massaging of the
data.
Thanks
"Peter Jamieson" wrote:
Is it possible to use an XML data source for mail merge?
No. At least, not directly. Depending on the layout/content of the XML,
and what you need to achieve, you could consider
a. trying to open the XML in Excel. If you can end up with a tabular
format in there, you can save it and use it as the data source for a
merge. (Or you might be able to use some other program to do something
similar)
b. using an XSL transform to convert the XML to a format Word can use.
I am exporting data
from SQL Server in XML format
FWIW, if you can access SQL Server directly from your workstation you
should be able to connect from Word to a SQL /Table/ or /View? directly
and use it as a data source, although in recent versions of SQL AFAICS
you have to be using integrated Windows Authentication, not SQL Server
logins. However, with recent versions of Word it does not appear to be
possible to connect to stored procedures.
Peter Jamieson
http://tips.pjmsn.me.uk
On 13/04/2010 17:37, Gwal wrote:
Is it possible to use an XML data source for mail merge? I am exporting data
from SQL Server in XML format and would like to merge it into a .dotx
template. Thanks
.