Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.

Dismiss

Re: Mail Merge With Seperate Source and Header files

1,023 views

Skip to first unread message

Peter Jamieson

unread,

Mar 30, 2007, 4:06:53 PM3/30/07

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

"kclark" <kcl...@discussions.microsoft.com> wrote in message
news:5D427EEC-22F2-4E17...@microsoft.com...
> 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
>

Ed

unread,

Mar 31, 2007, 2:26:00 PM3/31/07

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.

Peter Jamieson

unread,

Apr 2, 2007, 10:08:19 AM4/2/07

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

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

<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><o:DocumentProperties
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns="http://www.w3.org/TR/REC-html40">
<o:Name>mydata#txt</o:Name>
</o:DocumentProperties>
</xml><xml id=msodc><odc:OfficeDataConnection
xmlns:odc="urn:schemas-microsoft-com:office:odc"
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="";Jet OLEDB:System database="";Jet
OLEDB:Registry Path="";Jet OLEDB:Engine Type=96;Jet OLEDB:Database
Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk
Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create
System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don'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>

</style>

</head>

 
</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'>

 
</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>

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

"kclark" <kcl...@discussions.microsoft.com> wrote in message

news:B2F2FFDF-8AF8-4040...@microsoft.com...
> 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.
>

0 new messages