Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
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! |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
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
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
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! |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to link a different Mailmerge header source and data source? | Mailmerge | |||
Why will mailmerge not use all the fields from data source? | Mailmerge | |||
.doc data field limitation issues | Tables | |||
HOW LARGE CAN A MAILMERGE DATA SOURCE BE? | Mailmerge | |||
Detaching a MailMerge Data Source | Mailmerge |