Mailmerge from Excel Datasource

[JonathanF]

Trying to open an excel datasource, I got message 5640 - Unable to
re-establish DDE to...

Is there something that I need to do to enable DDE?

 

[zkid]

This all depends on the version of Word you are using. 97 and 2000 are
different than XP/2002 which is still different than 2003. Which one are you
using?

 

[JonathanF]

Thanks for the reply. I use Office Pro 2000 on Windows XP. What I am trying
to do is to create an excel spreadsheet from Access, then open Word and mail
merge using the spreadsheet as data source.

 

[zkid]

I'm a bit pressed for time at the moment, but try the info. at:

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

Let me know if it helps. If not, I will find more info. tomorrow.

zkid

 

[JonathanF]

Thanks for the link. However, I am not trying to get word to access a
database. I have an access project frontend to a SQL Server database, and I
have some general filter forms generating different reports or spreadsheet or
other output. I am trying to extend the options to doing word mailmerge.
Generating the excel datasource is no problem, and I am able to start up
word, but it fails to link to the excel datasource, erroring on
"re-establishing DDE".

 

[zkid]

Okay, let me see if I have this straight. You are controlling Excel, as well
as Word, from within Access, yes? Can you supply your code snippet that has
Word accessing the excel datasource? I will take a look at it in the morning
once I boot up my computer that is running Office 2000 and look at some of my
existing Word/Excel datasource code.

For additional info., you might try this link too:
http://support.microsoft.com/kb/189259/

 

[zkid]

Hi Jonathan,

Since I don't have your code, and this is programmed from within Word, not
Access, you might need to tweak it a bit. However, this code will merge an
excel spreadsheet containing corresponding headings to a Word document's
field codes:

Sub Word2000MailMerge()

Dim strDataSource As String, strDocWithCodes As String, myDoc As Document,
strCurDoc As String

strDocWithCodes = "c:\zivcomdocsss\MergeLtrs\All Fields Template.doc"
strDataSource = "c:\zivcomdocsss\contacts\filtered\ziv.xls"

Documents.Open strDocWithCodes
Set myDoc = ActiveDocument

myDoc.MailMerge.MainDocumentType = wdFormLetters
myDoc.MailMerge.OpenDataSource Name:= _
strDataSource, ConfirmConversions:=False, _
ReadOnly:=False, LinkToSource:=False, AddToRecentFiles:=False, _
PasswordDocument:="", PasswordTemplate:="",
WritePasswordDocument:="", _
WritePasswordTemplate:="", Revert:=False, Format:=wdOpenFormatAuto, _
Connection:="Entire Spreadsheet", SQLStatement:="", SQLStatement1:=""
With myDoc.MailMerge
.Destination = wdSendToNewDocument
.MailAsAttachment = False
.MailAddressFieldName = ""
.MailSubject = ""
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=True
End With

strCurDoc = ActiveDocument.ActiveWindow.Caption
myDoc.Close SaveChanges:=wdDoNotSaveChanges
Windows(strCurDoc).Activate

End Sub

If anyone knows how to merge the document with the field codes in it,
without actually opening it up, please add to this thread.

 

[JonathanF]

Thanks for the link and code. I tried your code both in Access and in Word
(slight syntax mod in Access), and got the same dde error.

I thought it might have to do with macro security, but got same error with
macro security set to low in both Word and Excel.

I think the problem is probably not in the VBA code, but in something more
fundamental in the O/S, registry, or something. I am going to find another
computer to test it out.

Thanks for your help.