Mail Merge Edit Data Source with VBA

[Dietmar]

Hello,

Word 2002

I have made a macro which makes a mail merge. Each letter is saved
seperately in a different file. Additionately the macro should edit
the datasource and add for each line the full path of the document.
The datasource is linked to Word via "Microsoft Excel Worksheet via
Converter". This enables me to use the Edit function of Mail Merge
Recipients. There is no problem changing the datasource manually.
However I had some problems to do it via VBA.

I found the EditDataSource Methode. This works but I can only change
the data via Word. Word opens The Excel Datasource and changes the
datas. That means that I cannot open the datasource in Excel (File
Format is not valid). Word obviously saves the file with a code which
makes the whole unusable for Excel. Moreover to edit datas with this
method is very unsave because I have no key in my Excel file. I would
prefer this methode:

Mail1 = Documents("Serienbrief3").MailMerge.DataSource.DataFields.Item(11)

Unfortunately this is a read only method. I have also tried to use the
Macro recorder (Mail Merge Recipients/Edit) but the Macro recorder
does not record anything. I think Word does something with SQL.

So my question is there a way to edit the datasource via VBA in Word
without the EditDataSource Methode? Any help is appreciated.

Dietmar

 

[Doug Robbins - Word MVP]

Hi Dietmar,

See the article "Control Excel from Word" at:

http://www.mvps.org/word/FAQs/InterDev/ControlXLFromWord.htm

Please post any further questions or followup to the newsgroups for the
benefit of others who may be interested. Unsolicited questions forwarded
directly to me will only be answered on a paid consulting basis.


Hope this helps
Doug Robbins - Word MVP

 

[Dietmar]

Hello Doug,

thanks for your fast reply and the link. I'm afraid this doesn't fit
to me. I use the Excel file as a datasource for the mail merge
document in Word. Therefore the Excel file is in use (because the main
document is open). That's why I think I cannot not modify the Excel
file this way. I think there should be a way to modify the datasource
with the mail merge methode. As I have said manually it is very easy
(Mail merge recipients / Edit). Unfortuanetly I cannot get the VBA
code for this methode.

Any hints???

Thanks a lot in advance

 

[Cindy M -WordMVP-]

Hi Dietmar,

Basically, you don't edit an Excel data source file linked in via DDE,
ODBC or OLE DB with Word data managing tools. If you're using Words
internal spreadsheet converter, which actually generates a virtual,
delimited text file, then the tools might work, in the UI.

There is no VBA interface for these tools because they shouldn't be
necessary for a programmer. VBA allows you to use DAO, ODBCDirect or ADO
to work directly with data sources. For Excel, the method Doug suggests
(automation) is also a possibility.

thanks for your fast reply and the link. I'm afraid this doesn't fit
to me. I use the Excel file as a datasource for the mail merge
document in Word. Therefore the Excel file is in use (because the main
document is open). That's why I think I cannot not modify the Excel
file this way. I think there should be a way to modify the datasource
with the mail merge methode. As I have said manually it is very easy
(Mail merge recipients / Edit). Unfortuanetly I cannot get the VBA
code for this methode.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Sep 30 2003)
http://www.mvps.org/word

This reply is posted in the Newsgroup; please post any follow question
or reply in the newsgroup and not by e-mail