Merging with a macro

[Donna]

I have a macro that merges serveral documents with an Access database and
then combines all the document together. When I try the macro in Word 2003, I
get an alert message for each merge document "Opening this document will run
an SQL statement...". You have to click on Yes for each document. If I use
the "Application.DisplayAlerts = wdAlertsNone" function, it does not display
the message, but it default to No. I need it to default to Yes. How do I
respond to the message automatically?

 

[Cindy M -WordMVP-]

Hi =?Utf-8?B?RG9ubmE=?=,

I have a macro that merges serveral documents with an Access database and
then combines all the document together. When I try the macro in Word 2003, I
get an alert message for each merge document "Opening this document will run
an SQL statement...". You have to click on Yes for each document. If I use
the "Application.DisplayAlerts = wdAlertsNone" function, it does not display
the message, but it default to No. I need it to default to Yes. How do I
respond to the message automatically?

See this article

"Opening This Will Run the Following SQL Command" Message When You Open a Word
Document - 825765
http://support.microsoft.com?kbid=825765

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

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

 

[Donna]

Thanks for the link to the article. Since my macro will run on many different
computers do you know of any programming code that I can use in the macro to
capture the alert and respond to it?

 

[zkid]

I feel your pain. I'm sure you've noticed that 97 and 2000 have their own
way of merging, XP again is different, and 2003 is different still. So, you
will need to test for the Office version and apply the appropriate merge code
for each. It's not that difficult to do - let me know if you need the code.

Bottom line is that in 2003, you need code similar to the following:

(From 2003 help)
This example uses ODBC to attach the Microsoft Access database named
"Northwind.mdb" to the active document. The SQLStatement argument selects the
records in the Customers table.

Dim strConnection As String

With ActiveDocument.MailMerge
.MainDocumentType = wdFormLetters
strConnection = "DSN=MS Access Databases;" _
& "DBQ=C:\Northwind.mdb;" _
& "FIL=RedISAM;"
.OpenDataSource Name:="C:\NorthWind.mdb", _
Connection:=strConnection, _
SQLStatement:="SELECT * FROM Customers"
End With

The problem here is that you need to fill in the SQLStatement. As far as I
know (and believe me, I've tried), the only way to turn off the SQL prompt is
to actually retrieve all of your main documents and tell them not to connect
to the datasource. Then re-save and close. Once you have inserted all of
the merge fields that you need, unlnking the main doc from the datasource
shouldn't create any problems. If you ever need to insert more fields, you
can always re-connect it manually, temporarily.

I had to punt by doing the above, because I have not succeded in "capturing"
the SQL prompt and dealing with it. Anyone else been able to do it?

zkid

 

[Cindy M -WordMVP-]

Hi =?Utf-8?B?emtpZA==?=,

I had to punt by doing the above, because I have not succeded in "capturing"
the SQL prompt and dealing with it. Anyone else been able to do it?

Which version of Word are we talking about, in this case? The SQL prompt should
never appear when opening a main merge document using automation. The data
source should be disconnected automatically, with no prompt.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

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

 

[Donna]

I am using Word 2003. I ended up doing the link after I open the document. I
was surprised that it did not increase the amount of time it takes to run the
macro. There are 5 different merge documents that get opened, linked and
combined into a single document and the whole thing takes under 30 seconds.
Works for me!