XML as a MailMerge datasource

[Tim Turnquist]

Since XML is becoming a cross-platform data standard it would be nice to have
it as a possible datasouce for Word MailMerge.

I think I can do what I need to by importing the XML into Excel or Access,
then merging from there, but that seems overly cumbersome, complicated and
prone to errors.

Any other suggestions?

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...b0c&dg=microsoft.public.word.mailmerge.fields

 

[Peter Jamieson]

Unfortunately although they are definitely aware of the fact that some
people would like to be able to use XML as a simple data exchange format and
therefore as a data source, implemeting something useful in this area
doesn't appear to be on MS's current agenda.

There are problems, of course: as it stands, Word basically wants its data
sources to be simple 2-D tables with the same number of columns in every
row, cf. traditional relation database tables, whereas XML can represent
much more complicated structures. Trying to second-guess the user by
flattening more complex structures into 2-D tables (and ignoring anything
that doesn't fit) probably isn't a smart move because it just invites "why
doesn't it work as I expect?" responses. But IMO if the user is willing to
transform their data into a simple 2-D structure reading the XML data
shouldn't be that hard. I did actually do the first draft of a Word text
converter that read simple XML files in this way a few years ago but never
finished it.

I think I can do what I need to by importing the XML into Excel or Access,
then merging from there, but that seems overly cumbersome, complicated and
prone to errors.

Quite.

If your XML is basically going to transform into a tabular structure with 63
columns or fewer and you're using Word 2003 Professional, your best bet may
be to use an XSL transform to transform your XML into a WordProcessingML
document containing a table, then use that as your data source. if it has
more than 63 columns you can consider transforming into a WordProcessingML
file with the data in tab-delimited format, but then you have to deal with
tabs and double-quotes in the data. I haven't tried it.

You can try opening an XML file using a DDE connection to Excel. You could
probably also transform your XML to SpreadsheetML and try opening that using
a DDE connection to Excel. but what I am fairly sure you cannot do, despite
the fact that the XML formats are supposed to be "native," is access them
through the Excel (i.e. Jet) ODBC driver/OLEDB provider.

Going via Access is probably the simplest approach that's actually likely to
work, as long as you don't exceed its column count.

Peter Jamieson

 

[Peter Jamieson]

Oh yes, or transform into an HTML table.

Peter Jamieson

 

[Tim Turnquist]

Peter,
Thanks for the quick reply. As disappointing as it is, it actually makes a
great deal of sense.

Since this is a for daily process that is always the same and done by an
'un-techie user' I think I will just write a quick XML parser in VBA and turn
all the nice XML into a dictionary and then tab or comma delimited the fields
and write them into a simple txt file. I think that would probably be the
simpliest for the user, since they would just have to remember to run the
Macro before the MailMerge rather than importing and/or worrying about file
structure, DDE's, ODBC's etc. Keep it simple, right?

Thanks again for the quick, informative reply -- and if you have any other
suggestions I would appreciate those as well.

-Tim

 

[Doug Robbins - Word MVP]

Create the mailmerge main document as a template and create a macro named
autonew() in that template and have the user use File>New and select the
template when they want to do the Mail Merge and then the conversion macro
will run automatically when the new document is being created by the
File>New command.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP