Mail Merge Not Merging Data

[Cole]

Hi all,

I've got a data source on a remote server. The Function I wrote seems
to be opening the data source, but the actual merge is not happening.
So I end up with a document with the merge fields showing. No error
msg returned. The data source file looks like this:

1~2~3~4~5~6~7~8~9~10~11~12~13~14~15~16~17~18~19~20~21~22~23~24~25~26~27~28~­
29~30~31
October 21, 2009~MS. SHIRLEY NAME~Her Addr Here~PHOENIX, AZ
zip_here~~~~data here~more data~person's name~01-number_here~OP-2009-
num_here~09/10/2009~01:42~ $11,111.00 ~Person's Name again~--------
~6212~stuff~stuff~text here ~text~2009-numeric_here~~ $1,111.00
~~numeric here ~acct # here~07/25/2009~~final text here

And the Function I created is here:

Function getDasFastMerge(params)

Dim doc, TNUM

Set doc = Word.ActiveDocument
TNUM=params(0,1) ' This is getting correct value.

const wdSendToNewDocument = 0
const wdDefaultFirstRecord = 0
const wdDefaultLastRecord = -16
const wdOpenFormatAuto = 0
const wdMergeSubTypeOther = 0

doc.MailMerge.OpenDataSource "\\svonfs\das01_exports\sdOffice
\MergeData" & Cstr(TNUM) & ".mrg",,True,True

' I've tried remarking out and unremarking these next lines - no
difference.

doc.MailMerge.Destination = wdSendToNewDocument
' doc.MailMerge.DataSource.FirstRecord = wdDefaultFirstRecord
' doc.MailMerge.DataSource.LastRecord = wdDefaultLastRecord
doc.MailMerge.Execute

getDasFastMerge = err.number & " " & err.description

End Function


The function is returning '0'. Any help would be appreciated. Thx,
Cole

 

[Peter Jamieson]

Can you stop the function immediately after the OpenDataSource, view the
ActiveDocument and see what is in the data source? (Sometimes Word fails
an OpenDataSource without warning).

I guess you have made the registry change documented here:

http://support.microsoft.com/kb/825765

Peter Jamieson

http://tips.pjmsn.me.uk
Visit Londinium at http://www.ralphwatson.tv

 

[Cole]

Can you stop the function immediately after the OpenDataSource, view the
ActiveDocument and see what is in the data source? (Sometimes Word fails
an OpenDataSource without warning).

I guess you have made the registry change documented here:

http://support.microsoft.com/kb/825765

Peter Jamieson

http://tips.pjmsn.me.uk
Visit Londinium athttp://www.ralphwatson.tv
















- Show quoted text -

--------------------------------------------------------------------------------------------------------------

Hi Peter,

Yes - I've made the registry change. - Thanks.
I did tell the function, just now, to return the name of the active
doc - and that is good.

I do think it is very possible that the script may not have connected
to the data source in time, and maybe taking a nap would be a good
idea. It seems that sleep is not too easy in VBA, huh?

I'm pretty green at VBA - can you suggest how I can stop the function
and get it to show me the data source? Or perhaps I could verify that
it has opened programmatically, with a sleep command until it can
verify the data source is available? Any suggested code snippets
would be much appreciated.

Thanks again,
Cole

 

[Peter Jamieson]

I'm pretty green at VBA - can you suggest how I can stop the function
and get it to show me the data source?

In the VBA Editor, you can use View-Toolbars to enable the Debug menu.
Then you can use one of the buttons to step through your code, line by
line. At each point you can switch back to the main Word document window
and look around to see what has happened, then switch back into the VBA
editor and continue executing code.

If you are new to VBA it will probably be worth having a good look
around the editor, the debug toolbar, ow to set breakpoints and inspect
variable and object property values and so on. If you do, you'll
probably know a lot more than I do, fairly quickly

Be careful not to change windows etc. within Word in the middle of code
execution as the VBA code may start working with different objects from
the ones you intended.

Peter Jamieson

http://tips.pjmsn.me.uk
Visit Londinium at http://www.ralphwatson.tv

 

[Cole]

 > I'm pretty green at VBA - can you suggest how I can stop the function
 > and get it to show me the data source?

In the VBA Editor, you can use View-Toolbars to enable the Debug menu.
Then you can use one of the buttons to step through your code, line by
line. At each point you can switch back to the main Word document window
and look around to see what has happened, then switch back into the VBA
editor and continue executing code.

If you are new to VBA it will probably be worth having a good look
around the editor, the debug toolbar, ow to set breakpoints and inspect
variable and object property values and so on. If you do, you'll
probably know a lot more than I do, fairly quickly

Be careful not to change windows etc. within Word in the middle of code
execution as the VBA code may start working with different objects from
the ones you intended.

Peter Jamieson

http://tips.pjmsn.me.uk
Visit Londinium athttp://www.ralphwatson.tv







- Show quoted text -

------------------------------------------------

Thanks for your help Peter. I essentially got it to sleep simply by
having a loop to increment a variable up to 50 million - it turns out
that was not the problem after all. I failed to mention that this is
not a macro inside the Word document, so no access to the VBA editor
during runtime. It actually is a third-party application (sdOffice)
that is executing the code. sdOffice is a product that sends data
across from our UNIX box (really Solaris) across to Windows; uses the
Word Mail Merge to merge data into a Word doc, etc. (It can also send
data into an Excel spreadsheet, or Outlook to be mailed...)

I actually thought outside the box, as it were, to try something
totally different and found that what I wanted to do is not possible.
To clarify:

Mail merges can use data in two formats that I am aware of:

Passing the data to the document in this format:

1="October 23, 2009",2="Mr Daffey Duck",3="3800 WOODHAVEN RD", etc.

OR putting the data in a file and telling the document where it can be
retrieved. In this case, the data is in this format:

October 23, 2009~Mr Daffey Duck~3800 WOODHAVEN RD~etc.

I have seen that the second method results in a MUCH fast merge. One
document can take four seconds instead of a full minute or two. So I
want to convert to the second format where ever I can. The show
stopper I just encountered is that in some of our docs, they are using
merge codes

1401="data here", 1501="more data",1601="stuff"

Trying to just convert from this format to the second results in 1600
plus merge fields, which is too much for Word to handle. So we would
have to renumber all the merge fields so they are not skipping
hundreds of slots.

Any other ideas on speeding up our existing merges would be much
appreciated.

Thanks again,

- Cole

 

[Peter Jamieson]

I failed to mention that this is

not a macro inside the Word document, so no access to the VBA editor
during runtime.

OK, I must say I had my doubts, but ran with what you told me.

It actually is a third-party application (sdOffice)

that is executing the code. sdOffice is a product that sends data
across from our UNIX box (really Solaris) across to Windows; uses the
Word Mail Merge to merge data into a Word doc, etc. (It can also send
data into an Excel spreadsheet, or Outlook to be mailed...)

I actually thought outside the box, as it were, to try something
totally different and found that what I wanted to do is not possible.
To clarify:

Mail merges can use data in two formats that I am aware of:

Passing the data to the document in this format:

1="October 23, 2009",2="Mr Daffey Duck",3="3800 WOODHAVEN RD", etc.

OR putting the data in a file and telling the document where it can be
retrieved. In this case, the data is in this format:

October 23, 2009~Mr Daffey Duck~3800 WOODHAVEN RD~etc.

OK, I would just like to stop there and say that if Word can natively
handle mailmerge in the first format, i.e.

1="October 23, 2009" etc., then it's news to me.

In other words, either
a. it's a new wrinkle (can always happen!) or
b. what's happening is that sdOffice has two ways of merging via Word,
one of which uses regular Word MailMerge, and the other (the "1="
approach) /might/ use a proprietary sdOffice approach that relies on the
users using some special type of field or placeholder in the "mail merge
main document"

The show
stopper I just encountered is that in some of our docs, they are using
merge codes

1401="data here", 1501="more data",1601="stuff"

That could be consistent with (b). What exactly are these "merge codes"
- are they Word fields, or some other kind of placeholder? Is there a
single, site-wide catalogue of some kind that says for example "we
always use 1401 to reference such-and-such a field on the Solaris box?

Trying to just convert from this format to the second results in 1600
plus merge fields, which is too much for Word to handle.

So we would
have to renumber all the merge fields so they are not skipping
hundreds of slots.

Before even commenting on that I'd have to have a better idea about how
the system works as a whole.

What is certainly true is that
a. Word's built-in MailMerge will only work with up to 255 fields if
they come from certain types of data source - for example, 255 is the
maximum number of columns Word can use from any Jet (Access, Excel) data
source.
b. you may be able to use more fields if the data source is a "plain
text file" and Word does not try to open it using a method that suffers
from that 255-constraint.
c. some data source types, e.g. SQL Server, also allow a lot of fields
in a data source.
d. Word may suffer from other limitations with very large field
numbers - e.g. the "Edit Recipients" dialog box will probably be unable
to display all the columns, and so on.

I had a quick look at the manual at

http://www.unform.com

which looks like the source of the software you are using, but couldn't
really work out precisely how you would set up a mailmerge. The other
possible candidate site for your product

http://www.sdoffice.com/

came up blank.

Peter Jamieson

http://tips.pjmsn.me.uk
Visit Londinium at http://www.ralphwatson.tv