DocType Directory changes to Letters

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Don Petersen - 09 Mar 2005 23:52 GMT

I'm updating an application from 2000 to 2003. The application did a
mailmerge with a doctype of Catalog. I have changed the doctype to
Directory. When I open the word doc manually, it has the properties it
should: doctype=Directory. When I open it programmatically from Access
using VBA, doctype=Letters and the mailmerge doesn't behave as anticipated.
code fragment to open file:
Set doc = app.Documents.Open(Path & "Merge.doc")

merge.MainDocumentType = wdDirectory
doesn't have any affect either.

Reply to this Message

Peter Jamieson - 10 Mar 2005 14:14 GMT

> Set doc = app.Documents.Open(Path & "Merge.doc")
>
> merge.MainDocumentType = wdDirectory

There seems to be a bit of text missing here, but you need something like:

Set doc = app.Documents.Open(Path & "Merge.doc")
doc.MailMerge.MainDocumentType = wdDirectory

But first you may need to follow the instructions in this article:

http://support.microsoft.com/default.aspx?scid=kb;en-us;825765

Peter Jamieson

> I'm updating an application from 2000 to 2003. The application did a
> mailmerge with a doctype of Catalog. I have changed the doctype to
[quoted text clipped - 7 lines]
> merge.MainDocumentType = wdDirectory
> doesn't have any affect either.

Reply to this Message

Don Petersen - 10 Mar 2005 21:02 GMT

Sorry for cutting out a little code:

Set app = CreateObject("Word.Application")
Set merge = app.ActiveDocument.MailMerge
With merge
.MainDocumentType = wdCatalog

should fill in the blanks some.

I tried your suggestion:

doc.MailMerge.MainDocumentType = wdDirectory
and even:
doc.MailMerge.MainDocumentType = wdCatalog

and the type did not change. It is still wdFormLetters.

Reply to this Message

Peter Jamieson - 11 Mar 2005 01:19 GMT

I may be missing something, but

immediately after this...

Set app = CreateObject("Word.Application")

no document is open in app, so there is no ActiveDocument, so there is no
MailMerge object for the set merge line. If you are starting from a blank
document, you will probably need at least

app.Documents.Add

and preferably

Dim oDoc as Word.Document
Set oDoc = app.Documents.Add

If you are starting from an existing document you could use

Dim oDoc as Word.Document
Set oDoc = app.Documents.Open("pathname of the .doc")

Peter Jamieson

> Sorry for cutting out a little code:
>
[quoted text clipped - 12 lines]
>
> and the type did not change. It is still wdFormLetters.

Reply to this Message

Don Petersen - 11 Mar 2005 19:03 GMT

Sorry, I left some code out for brevity's sake. If it weren't there, then I
would have gotten other run-time errors. Here is the full code:

Set app = CreateObject("Word.Application")
app.Visible = True
Set doc = app.Documents.Open(Path & "CatMergeUS.doc")
Set merge = app.ActiveDocument.MailMerge
doc.MailMerge.OpenDataSource _
Name:=Path & "IPdb.mdb", _
ConfirmConversions:=False, _
ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, _
SQLStatement:="Select * From 'Docs';" ' I've tried a number of
syntactic variations on this stmt based on a number of articles.
' These two lines are for another solution suggested by MS in a KBA.
'Connection:="TABLE [Docs]", _
'SubType:=wdMergeSubTypeWord2000
doc.MailMerge.MainDocumentType = wdDirectory
With merge
.Destination = wdSendToNewDocument
.MainDocumentType = wdDirectory
.Execute

The type of the document is still Letters. If I open the document manually,
the type is Directory, as I desire.

Reply to this Message

Peter Jamieson - 11 Mar 2005 22:47 GMT

OK, I think I suggested

doc.MailMerge.OpenDataSource _
Name:=Path & "IPdb.mdb", _
SQLStatement:="Select * From [Docs];"

but the ";" should not be there (even though it is normal SQL syntax).

/Normally/, the code I suggested should be enough.

But if that does not work, at the moment I can't see what is happening
here. Unfortunately I'm away next week so may not be able to pursue this
until after that, but here are a few suggestions...

Can you please open the document manually, then use VBA to output the values
of

ActiveDocument.MailMerge.DataSource.Name
ActiveDocument.MailMerge.DataSource.ConnectString
ActiveDocument.MailMerge.DataSource.QueryString

Are you also definitely not seeing the issues associated with the KB article
I mentioned earlier?

Does your database have either password security or user level (workgroup)
security set up?

Another thing to notice is the kind of dialog box that is displayed when
Word tries to open the file - if it has an Options button in the bottom
left, it's an ODBC dialog box, and that would typically mean that Word had
tried using OLEDB and failed, which probably means that the SQL syntax is
wrong.

FWIW,

> 'Connection:="TABLE [Docs]", _

This syntax in the Connection parameter is only used when you want to
connect using DDE, and is in fact optional as a SQL statement can be
provided in the SQLStatement parameter anyway.

> 'SubType:=wdMergeSubTypeWord2000

This parameter should only be used if you want to connect using DDE or
ODBC. It forces Word to use the old Word 2000 approach (and Word 2000 did
not support OLEDB).

Peter Jamieson

> Sorry, I left some code out for brevity's sake. If it weren't there, then
> I
[quoted text clipped - 22 lines]
> manually,
> the type is Directory, as I desire.

Reply to this Message

Don Petersen - 11 Mar 2005 23:53 GMT

I removed the ';' at the end of the SQL statement and the connection
appeared to work OK. Microsoft - sheesh!
Thanks. Have a nice trip!

Reply to this Message

Don Petersen - 12 Mar 2005 00:04 GMT

There are two issues with this code. One is that there was a prompt for a
table on opening the datasource. That seems to be fixed by removing the ';'
from the SQLStatment clause.

The other problem is that a mailmerge doc of type Directory becomes type
Letters when opened by VBA code in Access.

>>Are you also definitely not seeing the issues associated with the KB article
>>I mentioned earlier?

I do get that behavior. I don't understand the connection between that
behavior and a Letters or Directory datatype.

>>Does your database have either password security or user level (workgroup)
>>security set up?

No.

I will work on this:

>>Can you please open the document manually, then use VBA to output the

values of

>>ActiveDocument.MailMerge.DataSource.Name
>>ActiveDocument.MailMerge.DataSource.ConnectString
>>ActiveDocument.MailMerge.DataSource.QueryString

Reply to this Message

Peter Jamieson - 12 Mar 2005 00:53 GMT

> I do get that behavior. I don't understand the connection between that
> behavior and a Letters or Directory datatype.

The problem is that if you don't have the registry entry described in the KB
article, opendatasource will fail and what happens next will depend on what
error handling you have set up.

Peter Jamieson

> There are two issues with this code. One is that there was a prompt for a
> table on opening the datasource. That seems to be fixed by removing the
[quoted text clipped - 22 lines]
>>>ActiveDocument.MailMerge.DataSource.ConnectString
>>>ActiveDocument.MailMerge.DataSource.QueryString

Reply to this Message

Peter Jamieson - 12 Mar 2005 01:01 GMT

BTW, I meant to say before that the code you posted is mixing objects a bit
(once you've set up the doc object variable it's probably better to use it
rather than refer to ActiveDocument again). It may not make any difference
in practice but...

> Set app = CreateObject("Word.Application")
> app.Visible = True
> Set doc = app.Documents.Open(Path & "CatMergeUS.doc")

IMO at this point it might be better to use either something like

With doc.MailMerge
.OpenDataSource _
Name:=Path & "IPdb.mdb", _
SQLStatement:="Select * From [Docs]"
.MainDocumentType = wdDirectory
.Destination = wdSendToNewDocument
.MainDocumentType = wdDirectory
.Execute
End With

or

Set merge = doc.MailMerge
with merge
.OpenDataSource _
Name:=Path & "IPdb.mdb", _
SQLStatement:="Select * From [Docs]"
.MainDocumentType = wdDirectory
.Destination = wdSendToNewDocument
.MainDocumentType = wdDirectory
.Execute
end with

> Sorry, I left some code out for brevity's sake. If it weren't there, then
> I
[quoted text clipped - 22 lines]
> manually,
> the type is Directory, as I desire.

Reply to this Message

Don Petersen - 14 Mar 2005 20:03 GMT

Hi,
Thanks for the response. I looked at the KB article again. It suggests
lowering security. Don't think I want to do that. I get the prompt when I
open the doc manually, but not from VB. The datasource is getting opened. I
get the correct info, just the wrong format (Letters vs Catalog).

I tried both your code fragments and neither was successful at changing the
MainDocumentType from FormLetters to Catalog / Directory

I opened the doc manually and got:
Debug.Print ActiveDocument.MailMerge.DataSource.Name
E:\mii\ipdb\pluto\IPdb.mdb
Debug.Print ActiveDocument.MailMerge.DataSource.ConnectString
DSN=MS Access Database;DBQ=E:\mii\ipdb\pluto\IPdb.mdb;DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;
Debug.Print ActiveDocument.MailMerge.DataSource.QueryString
SELECT * FROM `Docs` WHERE ((`Class` = 'All') AND (`Type` = 'US'))
Debug.Print ActiveDocument.MailMerge.MainDocumentType
3 (wdCatalog / wdDirectory)

This all looks pretty good to me. Any other ideas? Thanks.

Reply to this Message

Don Petersen - 14 Mar 2005 20:34 GMT

I have a solution!! It's not pretty, but it works. I was stepping through
the code and noticed that when setting MainDocumentType to wdCatalog or
wdDirectory, MainDocumentType changed from NotaMergeDoc to wdFormLetters!!
So, I changed the code to set MainDocumentType to '3'. It showed as
wdDirectory and the merge ran just as desired.
Looks like a big fat M$ bug to me!! What a waste of time.

Reply to this Message

Peter Jamieson - 20 Mar 2005 15:11 GMT

Hi,

> Looks like a big fat M$ bug to me!! What a waste of time.

Thanks for posting this.

Peter Jamieson

"wdCatalog" or "wdDirectory" as a built-in constant, is treating it as a
variable, and using

>I have a solution!! It's not pretty, but it works. I was stepping through
> the code and noticed that when setting MainDocumentType to wdCatalog or
> wdDirectory, MainDocumentType changed from NotaMergeDoc to wdFormLetters!!
> So, I changed the code to set MainDocumentType to '3'. It showed as
> wdDirectory and the merge ran just as desired.
> Looks like a big fat M$ bug to me!! What a waste of time.

Reply to this Message

Rate this thread: