Data Source Dialog, how to suppress

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Ragnar Midtskogen - 29 Oct 2003 00:46 GMT

Hello,

I have an Access 97 application where I do a single record mail merge using
Word 97, print the document, give it a name and save it.
This is all done automatically and without making the document visible. I
open a temporary comma delimited text file and output the merge data in that
file. Then I open a template document,set the data source and run the merge.
Initially I was using early binding, but since I have Word 2000 on my
development machine, I had to reset the references everytime I gave the
client an update, so I changed to late binding.
After that change the client sees a data source dialog and have to click OK
to run the merge. I do not see that on my machine.
I have looked through the MailMerge object for Word 97 but I do not see any
properties I could use to force a suppression of the dialog.

Any help would be appreciated.

Ragnar

Reply to this Message

Doug Robbins - Word MVP - 29 Oct 2003 10:38 GMT

Hi Ragnar,

Take a look at fellow MVP Albert Kallal's website at

http://www.attcanada.net/~kallal.msn/msaccess/msaccess.html

He has a one-click word merge for ms-access routine there that sends only
the one record to
the word

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

> Hello,
>
[quoted text clipped - 14 lines]
>
> Ragnar

Reply to this Message

Ragnar Midtskogen - 30 Oct 2003 02:57 GMT

Thanks Doug, but I can't use his approach, he uses Access as the data
source. My app is a multiuser app with clients and linked tablesa back end
file setup. When you do a mail merge using Access as data source another
instance of Access is opened but not closed when done. This is a well known
problem and rather than jumping through hoops trying to fix the problem I
always export the data to a text file and use that as the data source. This
is the first time I have run into this problem. It must be some kind of a
glitch because it worked fine before I made an unrelated change.

Ragnar

> > Hello,
> >
[quoted text clipped - 19 lines]
> >
> > Ragnar

Reply to this Message

Cindy M -WordMVP- - 29 Oct 2003 16:58 GMT

Hi Ragnar,

Could you show us the code in question, please? Most especially the
variable declarations and how the variables are populated, plus the
OpenDataSource lines

> I have an Access 97 application where I do a single record mail merge using
> Word 97, print the document, give it a name and save it.
[quoted text clipped - 8 lines]
> I have looked through the MailMerge object for Word 97 but I do not see any
> properties I could use to force a suppression of the dialog.

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 :-)

Reply to this Message

Ragnar Midtskogen - 30 Oct 2003 03:07 GMT

Hello Cindy,

Good to hear from you.

Here is the code

Dim objWord As Object
Dim objWordDoc As Object
Dim objDoc As Object
Dim objDocs As Object
Dim strDocFileName As String
Dim strDocFilePath As String
Dim varRetVal As Variant
Dim rngDoc As Object
Dim rngBkMk As Object

' Set the mail merge data source
Set objWord = CreateObject("Word.Application")
Set objWordDoc = objWord.Application.Documents.Open( _
FileName:=strTextFilePath & "\" & DocName)
' Insert document filespec in the document at Bookmark field 'FileSpec'.
Set rngDoc = objWordDoc.Content
If (rngDoc.Bookmarks.Exists("FileSpec") = True) Then
rngDoc.Bookmarks("FileSpec").Select
objWord.Selection.Text = strDocFilePath & "\" & strDocFileName
Else
' Handle missing bookmark
MsgBox ("*** ERROR ***" & vbCrLf _
& "This document does not have a bookmark named 'FileSpec'")
GoTo Err_ExportFeeQuoteData
End If
With objWordDoc.MailMerge
.MainDocumentType = wdFormLetters
.OpenDataSource NAME:=strTextFilePath & "\" & FileName
' Set destination
.Destination = wdSendToNewDocument
.MailAsAttachment = False
.MailAddressFieldName = ""
.MailSubject = ""
.SuppressBlankLines = True
With .DataSource
.FirstRecord = 1
.LastRecord = 1
End With
' Run the merge.
.Execute Pause:=False
End With

The strTextFilePath & "\" & FileName is the filespec for the comma delimited
text file with the merge data

I forrgot to mention that this is running on NT4 with all the latest SP's,
but it probably is irrelevant.

Ragnar

Reply to this Message

Cindy M -WordMVP- - 30 Oct 2003 19:03 GMT

Hi Ragnar,

> Good to hear from you.

And I'm glad to see you're still pluggin' away at Word and
mail merge <g>

Picking up on your reply to Doug:

"When you do a mail merge using Access as data source
another instance of Access is opened but not closed when
done."

This is only a problem if you use a DDE connection. You
might want to consider an ODBC connection, instead, which
would save the convert-to-text-file step.

Now, on learning that you're dealing with a text file, I
have to wonder exactly WHICH dialog box you're dealing
with, here? Is it the one about confirming the field
delimiters? Or something else?

A tip, here (has nothing to do with your problem, but will
speed things up and make them more reliable):

If (rngDoc.Bookmarks.Exists("FileSpec") = True) Then
rngDoc.Bookmarks("FileSpec").Select
objWord.Selection.Text = _
strDocFilePath & "\" & strDocFileName

Use this instead to put the data into a bookmark:
rngDoc.Bookmarks("FileSpec").Range.Text = szString

Your OpenDataSource statement appears rather "sparse" to
me. This may be what's causing a dialog box to appear (if
we're not talking about the delimiter one).

.OpenDataSource NAME:=strTextFilePath & "\" & FileName

There are a lot more arguments to this method than just
Name. Quite a few are unnecessary, but others help Word to
understand which connection method it should use. I suggest
you record a macro in the Word UI linking to the data
source and bring that over into your code, see if it helps
at all.

Note that you also want to do this if you decide to go the
ODBC connection-to-Access route. In order to see/set which
connection method to be used in a merge in the UI, activate
the "Select Method" checkbox in the OpenDataSource dialog
box. You should then get a list.

Oh, and a question for you: what are the chances that this
same application will be upgraded to Word 2002 or later?

Cindy Meister

Signature

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

:-)

Reply to this Message

Ragnar Midtskogen - 31 Oct 2003 19:41 GMT

Hi Cindy,

Thank you for helping me navigate the Word world.
It is not such a jungle, but I do not work enough with Word
to keep current.

> "When you do a mail merge using Access as data source
> another instance of Access is opened but not closed when
[quoted text clipped - 3 lines]
> might want to consider an ODBC connection, instead, which
> would save the convert-to-text-file step.

I feel safer using the text file approach. The application runs on a
network of NT4 systems where things seem to change without
warning, and where NT sometimes seems to have a mind of it's
own. For example, on some machines a file that is opened then
flushed and closed can not be accessed since NT insists that
it is opened exclusively by another user.
The text file approach is quick enough and reliable, we are only
talking about two records, the column names and the data.

> Now, on learning that you're dealing with a text file, I
> have to wonder exactly WHICH dialog box you're dealing
> with, here? Is it the one about confirming the field
> delimiters? Or something else?

It is a dialog to to confirm the data source as being the text file.
I have seen it once before with another application where I had used
Word 2000 on my development machine and one user had
Office XP. I found that I could fix the problem by setting the
SubType parameter for the MailMerge.OpenDataSource
method that told Word to behave like Word 2000..

> A tip, here (has nothing to do with your problem, but will
> speed things up and make them more reliable):
[quoted text clipped - 6 lines]
> Use this instead to put the data into a bookmark:
> rngDoc.Bookmarks("FileSpec").Range.Text = szString

Thank you, I am never sure when to use the Range and when
to use the Selection. I have the impression that Range is the
preferred way of referring to a part of a document, but I am
not always sure what the Range includes. I think I have also
found that there are situations where Range can not be used..

> Your OpenDataSource statement appears rather "sparse" to
> me. This may be what's causing a dialog box to appear (if
[quoted text clipped - 8 lines]
> source and bring that over into your code, see if it helps
> at all.

I will try that, I am having some problems with my current
Office setup, the Object Browser do not open the asssociated
help file so I am stuck with using my MSDN CD's which are
not up to date. I was no able to get a detailed list of all the
arguments with a desciption of what each argument does.

> Note that you also want to do this if you decide to go the
> ODBC connection-to-Access route. In order to see/set which
> connection method to be used in a merge in the UI, activate
> the "Select Method" checkbox in the OpenDataSource dialog
> box. You should then get a list.

I assume you mean while setting it up? The user just wants to
click a button and be done with it, they prefer not to have to
respond to any dialogs, which is why I startted this thread.

> Oh, and a question for you: what are the chances that this
> same application will be upgraded to Word 2002 or later?

The person responsible for the overall IT situation says he is
planning to convert all machines to Windows 2000 and to
upgrade Office, in which case I think he would have to go to
either Office 2000 or Office XP.
I hesitate to make any changes because the application It
has been upgraded, started out as an Access 2 app using
strictly macros. Since then it has been converted to 95,
then 97 but most of the functionality is still in macros. This
make it very difficult and cumbersome to figure out how
the thing works (Or is supposed to work).
The guy who wrote it was the owner of the company and
he was learning on the job, this being his first Access
application, so the design is not always logical.

I will cross that bridge when I get to it. The conversion
will probably go smoothly but I would want the client to
sign of on any extra effort needed to deal with conversion
issues. before going ahead.

Take care,
Ragnar

Reply to this Message

Cindy M -WordMVP- - 04 Nov 2003 15:16 GMT

Hi Ragnar,

> The person responsible for the overall IT situation says he is
> planning to convert all machines to Windows 2000 and to
> upgrade Office, in which case I think he would have to go to
> either Office 2000 or Office XP.

How have you been getting on?

The reason I asked the above is because the way Word mail merge
handles text files has changed coming through the last couple of
version releases. For instance, Word will start to ask for the
encoding (and there's absolutely no way to set that using VBA).
It's also more difficult to get Word to use its internal
converter, with the change to OLEDB default connection method.

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 :-)

Reply to this Message

Ragnar Midtskogen - 05 Nov 2003 23:40 GMT

Hello Cindy,

> How have you been getting on?

Just fine, as long as I don't mix too many versions of Office (:-)).
Otherwise, we just got through Halloween with lots of candy left
and we have already had our first snow, back in October (Lasted about 30
minutes).

Thanks for the warning about handling of text files, I guess I should bite
the bullet and go ODBC.
I am about to do a major update of another Access app with mail merge. So,
once I have made the changes for this one the other one will be easy.

Again, thanks for the help

Ragnar

Reply to this Message