MailMerge Issues - doc 'forgets' source data, other issues

[Robert_L_Ross]

Ok, here's the deal:
Word 2002/Excel 2002

The Excel file has 8 columns:
SSN (9 digit SSN or 9 digit account number, data validation in place)
CorrType (2 digit correspondence type, data validation in place, drop down
list)
Processor (first last name, data validation, drop down list)
DateComp (date, field d2 = today(), d3 = d2, d4 = d3, etc.)
Notes
DOCACCTSSN
[=IF(AND(A2<>"",B2<>"",TYPE(A2)=1),TEXT(A2,"000000000"),IF(AND(A2<>"",B2<>""),UPPER(A2),""))]
DOCACCTSSNFMT
[=IF(AND(A2<>"",B2<>"",TYPE(A2)=1),TEXT(A2,"000-00-0000"),IF(AND(A2<>"",B2<>""),UPPER(A2),""))]
MERGE [=IF(AND(A2<>"",B2<>"",F2<>"",G2<>"",AND(C2<>"",C2<>0),D2<>""),"X","")]

I have these validations/formats in rows 2-101, with row 1 the header.

I tried to set this up via ODBC, but ODBC won't pick up sheet 1 (because the
formulas maybe?)

I can set this up in a word doc using the mail merge wizard and it works
fine. I can go in and set the Merge field to only show the fields with an
'X' in it and that also works.

When I close the word doc and re-open, it gives me a message:
"Opening this document will run the following SQL command:
SELECT * FROM 'barcodegeneragor$' WHERE 'Merge' = 'X' ORDER BY 'Merge' DESC
Data from your database will be placed in the document. Do you want to
continue?"

This makes me think that at least initially, word 'remembers' my link
criteria, so I click Yes.

I then get this message:
"OGS_CORR_COVER_MM.doc is a mail merge main document. Word cannot find it's
data source, C:\...\OGS Image Bar Code Generator.xls."

I have to then click Find Data Source, select my file and table, then go
back in and re-do the 'merge = x' bit and it works.

Now, the source .xls file is here:
"C:\OGS_IMAGING\OGS Image Bar Code Generator.xls"
and my word doc us here:
"C:\Documents and Settings\RRoss\Desktop\OGS_CORR_COVER_MM.doc"
so it shouldn't be a 'permissions' issue or anything like that.

Can anyone explain why Word can't keep the data source?

Thanks!

 

[Peter Jamieson]

When you have successfully re-connected, can you please
a. open the Word VBA editor (Word Tools|Macro|Visual Basic Editor)
b. open the Immediate Window if it is not displayed - it's typically in the
bottom right hand corner. You can use View|Immediate Window to display it
c. Type the following three commands, and copy/paste the results into a
message in this thread:

( Since you're using Word 2002, it's possible that the third command will
crash Word, so don't do that one if you don't want to)

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

If you can't get the value of the ConnectString, can you please
a. save your document as a Web Page (.htm) - not .mht or filtered.
b. open the .htm file in Notepad
c. find the mailmerge connection information - it is a block of 5-10 lines
about 20-25 lines from the top of the file, and it's fairly obvious IMO what
it is - and copy/paste that chunk here.

Peter Jamieson

Ok, here's the deal:
Word 2002/Excel 2002

The Excel file has 8 columns:
SSN (9 digit SSN or 9 digit account number, data validation in place)
CorrType (2 digit correspondence type, data validation in place, drop down
list)
Processor (first last name, data validation, drop down list)
DateComp (date, field d2 = today(), d3 = d2, d4 = d3, etc.)
Notes
DOCACCTSSN
[=IF(AND(A2<>"",B2<>"",TYPE(A2)=1),TEXT(A2,"000000000"),IF(AND(A2<>"",B2<>""),UPPER(A2),""))]
DOCACCTSSNFMT
[=IF(AND(A2<>"",B2<>"",TYPE(A2)=1),TEXT(A2,"000-00-0000"),IF(AND(A2<>"",B2<>""),UPPER(A2),""))]
MERGE
[=IF(AND(A2<>"",B2<>"",F2<>"",G2<>"",AND(C2<>"",C2<>0),D2<>""),"X","")]

I have these validations/formats in rows 2-101, with row 1 the header.

I tried to set this up via ODBC, but ODBC won't pick up sheet 1 (because
the
formulas maybe?)

I can set this up in a word doc using the mail merge wizard and it works
fine. I can go in and set the Merge field to only show the fields with an
'X' in it and that also works.

When I close the word doc and re-open, it gives me a message:
"Opening this document will run the following SQL command:
SELECT * FROM 'barcodegeneragor$' WHERE 'Merge' = 'X' ORDER BY 'Merge'
DESC
Data from your database will be placed in the document. Do you want to
continue?"

This makes me think that at least initially, word 'remembers' my link
criteria, so I click Yes.

I then get this message:
"OGS_CORR_COVER_MM.doc is a mail merge main document. Word cannot find
it's
data source, C:\...\OGS Image Bar Code Generator.xls."

I have to then click Find Data Source, select my file and table, then go
back in and re-do the 'merge = x' bit and it works.

Now, the source .xls file is here:
"C:\OGS_IMAGING\OGS Image Bar Code Generator.xls"
and my word doc us here:
"C:\Documents and Settings\RRoss\Desktop\OGS_CORR_COVER_MM.doc"
so it shouldn't be a 'permissions' issue or anything like that.

Can anyone explain why Word can't keep the data source?

Thanks!

 

[Peter Jamieson]

NB, when you re-open the data source, does the dialog box that lets you
select the sheet have
a. an "Options" button in the bottom left corner, or
b. a check box saying "First row of data contains column headers" or
c. something else.

If it's (a), make sure you have selected the correct file in the Workbook
dropdown - unfortunately
d. the one selected when you enter the dialog box is not necessarily the
one you chose in the "Select Data Source" dialog box. If you have different
workbooks with different worksheets, that may explain why you don't see the
worksheet you are expecting.
d. with longer paths, it's not always possible to see the full path name of
the file. At that point, you can at best make an intelligent guess as to
which workbook you have selected.

Peteer Jamieson

When you have successfully re-connected, can you please
a. open the Word VBA editor (Word Tools|Macro|Visual Basic Editor)
b. open the Immediate Window if it is not displayed - it's typically in
the bottom right hand corner. You can use View|Immediate Window to display
it
c. Type the following three commands, and copy/paste the results into a
message in this thread:

( Since you're using Word 2002, it's possible that the third command will
crash Word, so don't do that one if you don't want to)

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

If you can't get the value of the ConnectString, can you please
a. save your document as a Web Page (.htm) - not .mht or filtered.
b. open the .htm file in Notepad
c. find the mailmerge connection information - it is a block of 5-10 lines
about 20-25 lines from the top of the file, and it's fairly obvious IMO
what it is - and copy/paste that chunk here.

Peter Jamieson

Ok, here's the deal:
Word 2002/Excel 2002

The Excel file has 8 columns:
SSN (9 digit SSN or 9 digit account number, data validation in place)
CorrType (2 digit correspondence type, data validation in place, drop
down
list)
Processor (first last name, data validation, drop down list)
DateComp (date, field d2 = today(), d3 = d2, d4 = d3, etc.)
Notes
DOCACCTSSN
[=IF(AND(A2<>"",B2<>"",TYPE(A2)=1),TEXT(A2,"000000000"),IF(AND(A2<>"",B2<>""),UPPER(A2),""))]
DOCACCTSSNFMT
[=IF(AND(A2<>"",B2<>"",TYPE(A2)=1),TEXT(A2,"000-00-0000"),IF(AND(A2<>"",B2<>""),UPPER(A2),""))]
MERGE
[=IF(AND(A2<>"",B2<>"",F2<>"",G2<>"",AND(C2<>"",C2<>0),D2<>""),"X","")]

I have these validations/formats in rows 2-101, with row 1 the header.

I tried to set this up via ODBC, but ODBC won't pick up sheet 1 (because
the
formulas maybe?)

I can set this up in a word doc using the mail merge wizard and it works
fine. I can go in and set the Merge field to only show the fields with
an
'X' in it and that also works.

When I close the word doc and re-open, it gives me a message:
"Opening this document will run the following SQL command:
SELECT * FROM 'barcodegeneragor$' WHERE 'Merge' = 'X' ORDER BY 'Merge'
DESC
Data from your database will be placed in the document. Do you want to
continue?"

This makes me think that at least initially, word 'remembers' my link
criteria, so I click Yes.

I then get this message:
"OGS_CORR_COVER_MM.doc is a mail merge main document. Word cannot find
it's
data source, C:\...\OGS Image Bar Code Generator.xls."

I have to then click Find Data Source, select my file and table, then go
back in and re-do the 'merge = x' bit and it works.

Now, the source .xls file is here:
"C:\OGS_IMAGING\OGS Image Bar Code Generator.xls"
and my word doc us here:
"C:\Documents and Settings\RRoss\Desktop\OGS_CORR_COVER_MM.doc"
so it shouldn't be a 'permissions' issue or anything like that.

Can anyone explain why Word can't keep the data source?

Thanks!