Mail Merge With Seperate Source and Header files

[kclark]

I'm working with some legacy VB6 code and Word 2003 to automate mail merge
coming from a mainframe. The merge works fine when I combine both header and
footer in the same file, but this causes the choose delimiter pop up box to
open. I tried using two header file lines, but the pop up box still appears.
So now I'm trying to use a seperate header and source but when I do, I keep
getting the error, source cannot be found. However, before I get the error
the delimiter box pops up twice, the first time it has a list of the actual
header fields and the second time it has the datafields asking me to select a
delimiter. Below is a snipet of the VB code I'm using. Thanks in advance!

Set objWordDoc = New Word.Document
strsFileName = strsFileName & ".doc"
Set objWordDoc = objWordApp.Documents.Add(strsFileName)
objWordDoc.Activate

With objWordApp.ActiveDocument.MailMerge
.MainDocumentType = wdFormLetters
.OpenHeaderSource Name:="C:\WinsDoc\FldHead.txt", _
Revert:=False, AddToRecentFiles:=False
.OpenDataSource Name:="C:\WinsDoc\CombData.doc", _
Format:=wdOpenFormatAuto, _
ConfirmConversions:=False, _
ReadOnly:=True, _
LinkToSource:=True, _
AddToRecentFiles:=False, _
Revert:=True
End With

 

[Peter Jamieson]

This area can be a real nightmare - something works fine with a small number
of field names, but not with a large number unless the names are longer
rather than shorter. Or fine with duplicate rows in the header. Or fine with
at least two rows in the data source, but not if you only have one. And so
on.

Faced with that, there are a couple of things you could try, but what is
likely to work depends partly on exactly how your data is structured - for
example,
a. when you say "coming from a mianframe" I'm a bit surprised that your
data sources seem to be .doc files - are these Word .docs or just text files
named .doc? Do you have a choice?
b. /could/ the data be in text files, not Word files?
c. can you choose the field delimiters?
d. what is the maximum number of columns in any of your data sources?

Peter Jamieson

 

[Ed]

Hi kclark,

In addition to Peter's reply ...

We've had this happen when using a delimited text file as the data source.

In our case the problem seemed to be that Word couldn't unambiguously
determine what the delimiters are.

I think that when Word is given a delimited line as the header line it tries
to figure out what acts as the delimiter. It seems to select a likely
candidate character (such as a comma), determine how many fields the header
line has if the candidate character is the delimiter and then check whether
(some of?) the data lines match the same pattern.

If it doesn't find a suitable pattern using the first candidate character,
it tries another one (such as a tilde).

If it tries all candidate characters and hasn't found one where the pattern
of the header line matches the pattern of the data lines it gives up and asks
you to choose the delimiters.

If it finds more than one character in the header line that give a header
line pattern which matches some data line patterns then it also gives up and
asks you to choose.

For example, let's say your delimited file actually uses tildes as the
delimiter but the header line looks like this:

Fld1~Fld_Number_Two~Fld3~Fld4~Fld5

Word doesn't know initially whether the line contains five fields separated
by tildes or two fields separated by underscores.

If most of the data lines contain five data fields separated by tildes but
one of the lines that Word checks when trying to establish the delimiter
happens as well to contain two underscores then Word seems to regard both the
five-field pattern and the two-field pattern as potentially the "true"
pattern and since it doesn't favour one over the other it gives up and asks
you to choose.

Don't know if any of this is relevant to your situation.

Regards.

Ed

 

[kclark]

Peter,

The fields are coming from the mainframe as text, bu the text file is being
saved a a Word doc before the mail merge. However, after reading your first
response I attempted the merge with both the Header and the source being text
files and Word is still pop up the choose delimiter box for both header and
source files. I then get the error "Word was unable to open source file"

To anser Ed's question...None of the header field names contain characters
that can be used as a delimiter option.

 

[Peter Jamieson]

As long as you have 255 fields or fewer, try the following:
a. use a .odc file to connect to the data
b. use a SCHEMA.INI file to specify the delimiter, and if necessary the
column names
c. don't use a header source, just a data source

For example, suppose you want to connect to c:\myfiles\mydata.txt.
Then you need a .odc file (Office Data Connection
File) as follows (this has a lot of stuff which you do not absolutely need
but which Office inserts when you create it):

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

<html xmlns="urn:schemas-microsoft-comfficeffice"
xmlns="http://www.w3.org/TR/REC-html40">

<head>
<meta http-equiv=Content-Type content="text/x-ms-odc; charset=utf-8">
<meta name=ProgId content=ODC.Table>
<meta name=SourceType content=OLEDB>
<meta name=Table content="mydata#txt">
<title>mydata#txt</title>
<xml id=docprops><oocumentProperties
xmlns="urn:schemas-microsoft-comfficeffice"
xmlns="http://www.w3.org/TR/REC-html40">
<o:Name>mydata#txt</o:Name>
</oocumentProperties>
</xml><xml id=msodc><odc:OfficeDataConnection
xmlnsdc="urn:schemas-microsoft-comfficedc"
xmlns="http://www.w3.org/TR/REC-html40">
<odc:Connection odc:Type="OLEDB">
<odc:ConnectionString>Provider=Microsoft.ACE.OLEDB.12.0;User
ID=Admin;Data Source=c:\myfiles\;Mode=Share Deny None;Extended
Properties=&quot;&quot;;Jet OLEDB:System database=&quot;&quot;;Jet
OLEDB:Registry Path=&quot;&quot;;Jet OLEDB:Engine Type=96;Jet OLEDBatabase
Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk
Transactions=1;Jet OLEDB:New Database Password=&quot;&quot;;Jet OLEDB:Create
System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDBon't Copy
Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet
OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False</odc:ConnectionString>
<odc:CommandType>Table</odc:CommandType>
<odc:CommandText>mydata#txt</odc:CommandText>
</odc:Connection>
</odc:OfficeDataConnection>
</xml>
<style>
<!--
.ODCDataSource
{
behavior: url(dataconn.htc);
}
-->
</style>

</head>

<body onload='init()' scroll=no leftmargin=0 topmargin=0 rightmargin=0
style='border: 0px'>
<table style='border: solid 1px threedface; height: 100%; width: 100%'
cellpadding=0 cellspacing=0 width='100%'>
<tr>
<td id=tdName style='font-family:arial; font-size:medium; padding: 3px;
background-color: threedface'>
&nbsp;
</td>
<td id=tdTableDropdown style='padding: 3px; background-color:
threedface; vertical-align: top; padding-bottom: 3px'>

&nbsp;
</td>
</tr>
<tr>
<td id=tdDesc colspan='2' style='border-bottom: 1px threedshadow solid;
font-family: Arial; font-size: 1pt; padding: 2px; background-color:
threedface'>

&nbsp;
</td>
</tr>
<tr>
<td colspan='2' style='height: 100%; padding-bottom: 4px; border-top:
1px threedhighlight solid;'>
<div id='pt' style='height: 100%' class='ODCDataSource'></div>
</td>
</tr>
</table>


<script language='javascript'>

function init() {
var sName, sDescription;
var i, j;

try {
sName = unescape(location.href)

i = sName.lastIndexOf(".")
if (i>=0) { sName = sName.substring(1, i); }

i = sName.lastIndexOf("/")
if (i>=0) { sName = sName.substring(i+1, sName.length); }

document.title = sName;
document.getElementById("tdName").innerText = sName;

sDescription = document.getElementById("docprops").innerHTML;

i = sDescription.indexOf("escription>")
if (i>=0) { j = sDescription.indexOf("escription>", i + 11); }

if (i>=0 && j >= 0) {
j = sDescription.lastIndexOf("</", j);

if (j>=0) {
sDescription = sDescription.substring(i+11, j);
if (sDescription != "") {
document.getElementById("tdDesc").style.fontSize="x-small";
document.getElementById("tdDesc").innerHTML = sDescription;
}
}
}
}
catch(e) {

}
}
</script>

</body>

</html>

-----------------------------------------------------------------
You need one of those for each data source. You will need to change the path
name in the connection string to be the name of the folder containing the
text file you want to use, and change the file name in the various places it
occurs (Actually, I suspect it would be enough to change it in the line that
says "<meta name=Table content="mydata#txt">". Notice that "#" is used
instead of "." in this type of .odc file - I don't know if that is entirely
necessary. If you copy the DATACONN.HTC file from your My Data Sources
folder to the same folder as the .odc and try to open the .odc in Internet
Explorer, with any luck you wil see the text file content. That's not
essential for your task, but a useful technique when debugging .odc files.

To specify the character encoding of the text file, you need a SCHEMA.INI
file in the same folder as the text file. The SCHEMA.INI file is a text file
that you can edit with Notepad. has one "section" for each file you want to
describe. For a comma-delimited file with a header record try

[mydata.txt]
ColNameHeader=True
Format=Delimited(,)
MaxScanRows=25


If you need to use a different delimiter character use it instead of the ","

If you need to use a different character encoding, you can specify many
different encoding using another line, e.g. the following one is for Unicode
UTF-8:

CharacterSet=65001

If you want to put the column names in the SCHEMA.INI file rather than the
data source, you can do so by using

ColNameHeader=False

and adding lines such as

Col1=nameofcolumn1
Col2=nameofcolumn2

and so on, e.g. you might have

Col1=firstname
Col2=lastname

If you want to specify the data type of the column, you can do so to an
extent, e.g. for text columns use

Col1=firstname text
Col2=lastname text

As background, there are three methods you can use when connecting to a text
file as a data source-
a. Word's text converter (which pops up a dialog asking for field and
record delimiters)
b. the ODBC text driver (which relies on SCHEMA.INI to tell you the field
delimiter, but only really understands "CRLF" type record delimiters)
c. the OLE DB text provider (which behaves differently depnding on whther
you try to connect directly to the text file or use a .odc as an
intermediary - when you connect directly, it ignores any SCHEMA.INI and
prompts for the field delimiter, offering, tab, comma, and "other". When you
connect via .odc, the SCHEMA.INI is honoured and you avoid the dialog)

Word 2000 and earlier would use either (a) or (b) depending on
circumstances. Neither could cope with some encodings such as Unicode. (a)
is the only one that allows more than 255 fields.

Word 2002 and 2003 use (c) by default, then fall back to (a) if the
connection fails. "Header Source" is stil there but as you have found
doesn't really help. Further, as far as I know, only method (a) is used to
open a Header Source.

I've been researching this for a web page but there are a few remaining
unknowns to sort out first.

Peter Jamieson