You should be able to use VBA for all the Word-rlelated stuff. You may also
need to consider modifying any ODBC DSNs (or creating new ones) and other
relevant files containing connection info. (e.g. .dqy, and for Word 2002 and
later, .udl and .odc) that refer to the server name. But the process is not
straightforward, and I don't have source code for you.
Assuming you need to deal with .doc documents, the first thing to bear in
mind is that Word will look for a document's data source as soon as it opens
the document, /before/ it runs any VBA code or AutoOpen macros etc. If the
data source is not there, Word will display an error message. As far as I
know, there's no way to trap it. So in order to do any batch processing, the
/existing/ data sources must be present when you start the conversion. To
complete the conversion, the /new/ data sources must be present. So you will
either need to be able to access the same data sources (or copies of them)
using both server names at the same time, or you will need to run the
conversion in two stages:
a. with the existing server in place, record the details of the data source
for each document, then disconnect the data source using e.g.
ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument
b. with the new server in place, re-connect each document to the data
source using the details you recorded.
Whatever process you create will also need to deal with documents whose data
sources cannot be located. It's difficult to recommend any particular
approach to this - if I were doing it, I'd probably accept that I'd have to
identify problem documents by running a subset of part (a) multiple times to
identify problem documents and deal with them manually.
When you are doing (a), the second thing you need to bear in mind is that
when you connect to a data source, and filters and sequencing defined by the
user in Word, and in the case of Word 2002/2003 documents, any individual
record inclusions/exclusions, will be lost. In the case of filter/sort
sequences, you /should/ be able to extract the current value of
ActiveDocument.MailMerge.Datasource.Querystring and to provide that SQL to
OpenDataSource when you connect to the new data source. You really need to
test that. If you really need to preserve record inclusions/exclusions (Word
2002 and later only) you will have to loop through every record in the data
source and inspect the relevant property. But I really think you should try
to avoid that.
If you are using Word 2002, and possibly 2003, the third thing to bear in
mind is that if you try to get existing connection details using
..Connectstring and .Querystring, Word will probably crash because of an
error in the Word 2002 code. I suspect that the only programmatic way to
extract the connection string and Query string in that case will be to save
the document as a web page, and programmatically dig the necessary
information out of the HTML file - it's unambiguously tagged using XML so it
should be relatively easy to get the info. In fact, it may make sense to get
all your connection data using this approach.
If you are also changing your version of Word as part of this process, you
are likely to face yet another bunch of problems related to connection
methods and so on. So "don't", is probably the best approach.
Finally, you may need to think about other types of link in your Word
documents that refer to files on the server - any document, not just a
mailmerge main document, might have a { DATABASE } field in it, and other
types of link such as INCLUDETEXT, INCLUDEPICTURE, LINK, HYPERLINK,
(unlikely, but possible), DDE/DDEAUTO, and probably other stuff I've
forgotten.
