A Microsoft Word forum. Microsoft Office Word Forum - WordBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » Microsoft Office Word Forum - WordBanter forum » Microsoft Word Newsgroups » Mailmerge
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

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



 
 
Thread Tools Display Modes
  #1  
Old February 21st 07, 05:56 PM posted to microsoft.public.word.mailmerge.fields
Robert_L_Ross
external usenet poster
 
Posts: 15
Default MailMerge Issues - doc 'forgets' source data, other issues

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,"0000000 00"),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 he
"C:\OGS_IMAGING\OGS Image Bar Code Generator.xls"
and my word doc us he
"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!
Ads
  #2  
Old February 22nd 07, 11:17 AM posted to microsoft.public.word.mailmerge.fields
Peter Jamieson
external usenet poster
 
Posts: 4,583
Default MailMerge Issues - doc 'forgets' source data, other issues

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

"Robert_L_Ross" wrote in message
...
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,"0000000 00"),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 he
"C:\OGS_IMAGING\OGS Image Bar Code Generator.xls"
and my word doc us he
"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!



  #3  
Old February 22nd 07, 11:38 AM posted to microsoft.public.word.mailmerge.fields
Peter Jamieson
external usenet poster
 
Posts: 4,583
Default MailMerge Issues - doc 'forgets' source data, other issues

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

"Peter Jamieson" wrote in message
...
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

"Robert_L_Ross" wrote in message
...
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,"0000000 00"),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 he
"C:\OGS_IMAGING\OGS Image Bar Code Generator.xls"
and my word doc us he
"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!





 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
How to link a different Mailmerge header source and data source? Eleni_Malli Mailmerge 1 May 3rd 06 02:08 PM
Why will mailmerge not use all the fields from data source? Jenny H Mailmerge 3 February 20th 06 06:03 PM
.doc data field limitation issues Alicia Tables 1 August 6th 05 03:16 AM
HOW LARGE CAN A MAILMERGE DATA SOURCE BE? Cynthia Pryor Mailmerge 1 May 7th 05 11:00 PM
Detaching a MailMerge Data Source Meena Bhashyam Mailmerge 1 April 20th 05 05:47 PM


All times are GMT +1. The time now is 11:21 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 Microsoft Office Word Forum - WordBanter.
The comments are property of their posters.