Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
MS Word&Excel07 Mail Merge - New Data Issues
We have recently converted from MS Office 97 to Office 2007 and appear to
have lost much of our core functionality. The system we had was: a) export a selected contact from MS Access to a read only MS Excel file that manipulated data, join or deleted fields, made calcs and added live data to new fields etc. (I know Access could probably do most of that, but I get on better with Excel for number crunching and formulas and am time poor) b) A specific tab served as a datasource for our MS Word docs. The problem is that MS Word no longer recognises the updated data from the Read Only Excel file. It stores the last saved data before the MS Excel was converted to read only. Removing the spreadsheet Read Only is not a solution for several reasons including: a) Risk of inadvertent changes by multiple uses b) Read only allows multiple uses to uses the same spreadsheet, but with data unique to that user. c) No conflict issues (We have already experimented with this idea) I just want MS Word 07 to source data from the Excel07 file like it has for the last 10 years. Please Help! -- MundooTriker |
#2
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
MS Word&Excel07 Mail Merge - New Data Issues
So far, I haven't been able to replicate this here, but...
a. there are certainly two significant differences between Word 97 and Word 2007 in this area. One is that Word 97/2000 would, by default, have used DDE to connect to your spreadsheet, whereas Word 2002/3/7 use OLE DB. Another is that Word 97 tends to make copies of certain types of data source, particularly if it thinks they are "on the Internet", which in some cases can happen even when you (correctly) think they are "on a Windows network drive". Either as a consequence of this change (which I believe happened in Word 2007) or in addition, Word does not seem to notice changes in open data sources in quite the same way as previous versions. In other words, you may find that you either have to close the data source, and then the mail merge main document, then re-open one or both to see any changes in the data source, or you may have to go into Word Mailings tab-Edit Recipient List, then select the data source name in the box at the lower left and click Refresh. b. It is also possible that you opened your data source in Word 97 using ODBC or the Excel converter. Do you know what method you used? In any case, a significant difference between DDE and the others is that Word would start Excel if it was not already running, and would cause Excel to open the spreadsheet if it was not already open. In Word 2007 it is more difficult to choose DDE, particularly with a a .xlsx or .xlsm rather than a .xls; more difficult to make ODBC work, and the converter is no longer provided or supported (although it may still function to an extent if it is on your system). So OLE DB is almost the only game in town. c. Can you describe in a bit more detail what your workflow is, e.g. 1. are you creating the Excel file, then closing it, then opening the Word document, then connecting to it, closing the Word document, creating the next version of the Excel file from Access, closing it, re-opening the Word file... or what? 2. how exactly are you making your spreadsheet read-only (e.g. are you doing it by setting the read-only attribute in the Windows file system, or using something more like the "read-only recommended" facility in Excel?) Without that, it's difficult to know whether we are replicating what you are doing or doing something else entirely. Peter Jamieson http://tips.pjmsn.me.uk Visit Londinium at http://www.ralphwatson.tv MundooTriker wrote: We have recently converted from MS Office 97 to Office 2007 and appear to have lost much of our core functionality. The system we had was: a) export a selected contact from MS Access to a read only MS Excel file that manipulated data, join or deleted fields, made calcs and added live data to new fields etc. (I know Access could probably do most of that, but I get on better with Excel for number crunching and formulas and am time poor) b) A specific tab served as a datasource for our MS Word docs. The problem is that MS Word no longer recognises the updated data from the Read Only Excel file. It stores the last saved data before the MS Excel was converted to read only. Removing the spreadsheet Read Only is not a solution for several reasons including: a) Risk of inadvertent changes by multiple uses b) Read only allows multiple uses to uses the same spreadsheet, but with data unique to that user. c) No conflict issues (We have already experimented with this idea) I just want MS Word 07 to source data from the Excel07 file like it has for the last 10 years. Please Help! |
#3
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
MS Word&Excel07 Mail Merge - New Data Issues
Hello Peter,
Thanks for the detailed response. a) Prior your response I have tried unsuccessfully to re-establish a DDE link. At present the only option is to open & file save the datasource, and then opensave the merge doc. (Too clumsy & tedious in our office situation). I was right onto "Edit Recipents/Datasource/Refresh. It doesn't work basically. b) DDE was the data link for Word97. OLE DB Database files is the default option that comes up in Word07. Also tried "ShowAll" and had a crack at the other links. ODBC just doesn't appear to work. OLE DB does appear to the be only "game in town" as you say. c) There was a common Excel file working with the imported data. (Populated a selected record pasted via VB script from MSAcess97.) The imported data was manipulated in worksheet in Excel file. The adjusted data linked to another worksheet that was the datasource for MS Word mailmerge docs. The Excel file was had numerous macros and formulas specifically for data manipulation. It was not necessary to close to facilitate the mailmerge. Every time the Word doc reopened, it picked up the refreshed data. d) Readonly was applied to Excel by Rightclick on closed file/properties/ Attributes. Thanks -- MundooTriker "Peter Jamieson" wrote: So far, I haven't been able to replicate this here, but... a. there are certainly two significant differences between Word 97 and Word 2007 in this area. One is that Word 97/2000 would, by default, have used DDE to connect to your spreadsheet, whereas Word 2002/3/7 use OLE DB. Another is that Word 97 tends to make copies of certain types of data source, particularly if it thinks they are "on the Internet", which in some cases can happen even when you (correctly) think they are "on a Windows network drive". Either as a consequence of this change (which I believe happened in Word 2007) or in addition, Word does not seem to notice changes in open data sources in quite the same way as previous versions. In other words, you may find that you either have to close the data source, and then the mail merge main document, then re-open one or both to see any changes in the data source, or you may have to go into Word Mailings tab-Edit Recipient List, then select the data source name in the box at the lower left and click Refresh. b. It is also possible that you opened your data source in Word 97 using ODBC or the Excel converter. Do you know what method you used? In any case, a significant difference between DDE and the others is that Word would start Excel if it was not already running, and would cause Excel to open the spreadsheet if it was not already open. In Word 2007 it is more difficult to choose DDE, particularly with a a .xlsx or .xlsm rather than a .xls; more difficult to make ODBC work, and the converter is no longer provided or supported (although it may still function to an extent if it is on your system). So OLE DB is almost the only game in town. c. Can you describe in a bit more detail what your workflow is, e.g. 1. are you creating the Excel file, then closing it, then opening the Word document, then connecting to it, closing the Word document, creating the next version of the Excel file from Access, closing it, re-opening the Word file... or what? 2. how exactly are you making your spreadsheet read-only (e.g. are you doing it by setting the read-only attribute in the Windows file system, or using something more like the "read-only recommended" facility in Excel?) Without that, it's difficult to know whether we are replicating what you are doing or doing something else entirely. Peter Jamieson http://tips.pjmsn.me.uk Visit Londinium at http://www.ralphwatson.tv MundooTriker wrote: We have recently converted from MS Office 97 to Office 2007 and appear to have lost much of our core functionality. The system we had was: a) export a selected contact from MS Access to a read only MS Excel file that manipulated data, join or deleted fields, made calcs and added live data to new fields etc. (I know Access could probably do most of that, but I get on better with Excel for number crunching and formulas and am time poor) b) A specific tab served as a datasource for our MS Word docs. The problem is that MS Word no longer recognises the updated data from the Read Only Excel file. It stores the last saved data before the MS Excel was converted to read only. Removing the spreadsheet Read Only is not a solution for several reasons including: a) Risk of inadvertent changes by multiple uses b) Read only allows multiple uses to uses the same spreadsheet, but with data unique to that user. c) No conflict issues (We have already experimented with this idea) I just want MS Word 07 to source data from the Excel07 file like it has for the last 10 years. Please Help! |
#4
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
MS Word&Excel07 Mail Merge - New Data Issues
c) There was a common Excel file working with the imported data.
(Populated a selected record pasted via VB script from MSAcess97.) The imported data was manipulated in worksheet in Excel file. The adjusted data linked to another worksheet that was the datasource for MS Word mailmerge docs. Just to check, are these two sheets in the same workbook? Are you still using a .xls format for the mailmerge datasource workbook, or is it now .xlsx/.xlsm? If you have moved on to .xlsx/.xlsm but .xls would still do everything you need as far as the workbook is concerned, it may be worth reverting to .xls and trying the DDE approach again (although you are still likely to encounter problems with it). I'll try to have another look based on what you've said, but the unfortunate truth is that things have changed a lot. Peter Jamieson http://tips.pjmsn.me.uk Visit Londinium at http://www.ralphwatson.tv MundooTriker wrote: Hello Peter, Thanks for the detailed response. a) Prior your response I have tried unsuccessfully to re-establish a DDE link. At present the only option is to open & file save the datasource, and then opensave the merge doc. (Too clumsy & tedious in our office situation). I was right onto "Edit Recipents/Datasource/Refresh. It doesn't work basically. b) DDE was the data link for Word97. OLE DB Database files is the default option that comes up in Word07. Also tried "ShowAll" and had a crack at the other links. ODBC just doesn't appear to work. OLE DB does appear to the be only "game in town" as you say. c) There was a common Excel file working with the imported data. (Populated a selected record pasted via VB script from MSAcess97.) The imported data was manipulated in worksheet in Excel file. The adjusted data linked to another worksheet that was the datasource for MS Word mailmerge docs. The Excel file was had numerous macros and formulas specifically for data manipulation. It was not necessary to close to facilitate the mailmerge. Every time the Word doc reopened, it picked up the refreshed data. d) Readonly was applied to Excel by Rightclick on closed file/properties/ Attributes. Thanks |
#5
Posted to microsoft.public.word.mailmerge.fields
|
|||
|
|||
MS Word&Excel07 Mail Merge - New Data Issues
Hello Peter,
All associated worksheets were in the same workbook. I have tried different Excel formats (reverting to xls. and xlsx). Word just won't accept the DDE link. The option appears by checking "show all" (as recommended by the standard help text). It appears, unfortunately, the 'world has changed'. In the interim, I have: Decoupled the Word docs from the standard datasource. (The trade off is that the datasouce is not automatically connected. Merging requires clicking "Edit existing list" and following two links back to the data source. Probably about 5 extra clicks, and seconds) This appears to work, but I am going to try and refine the process. Thanks - Andrew created another macro to export the data each time in to another -- MundooTriker "Peter Jamieson" wrote: c) There was a common Excel file working with the imported data. (Populated a selected record pasted via VB script from MSAcess97.) The imported data was manipulated in worksheet in Excel file. The adjusted data linked to another worksheet that was the datasource for MS Word mailmerge docs. Just to check, are these two sheets in the same workbook? Are you still using a .xls format for the mailmerge datasource workbook, or is it now .xlsx/.xlsm? If you have moved on to .xlsx/.xlsm but .xls would still do everything you need as far as the workbook is concerned, it may be worth reverting to .xls and trying the DDE approach again (although you are still likely to encounter problems with it). I'll try to have another look based on what you've said, but the unfortunate truth is that things have changed a lot. Peter Jamieson http://tips.pjmsn.me.uk Visit Londinium at http://www.ralphwatson.tv MundooTriker wrote: Hello Peter, Thanks for the detailed response. a) Prior your response I have tried unsuccessfully to re-establish a DDE link. At present the only option is to open & file save the datasource, and then opensave the merge doc. (Too clumsy & tedious in our office situation). I was right onto "Edit Recipents/Datasource/Refresh. It doesn't work basically. b) DDE was the data link for Word97. OLE DB Database files is the default option that comes up in Word07. Also tried "ShowAll" and had a crack at the other links. ODBC just doesn't appear to work. OLE DB does appear to the be only "game in town" as you say. c) There was a common Excel file working with the imported data. (Populated a selected record pasted via VB script from MSAcess97.) The imported data was manipulated in worksheet in Excel file. The adjusted data linked to another worksheet that was the datasource for MS Word mailmerge docs. The Excel file was had numerous macros and formulas specifically for data manipulation. It was not necessary to close to facilitate the mailmerge. Every time the Word doc reopened, it picked up the refreshed data. d) Readonly was applied to Excel by Rightclick on closed file/properties/ Attributes. Thanks |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Mail Merge Issues | Microsoft Word Help | |||
Word 2007 printing issues with large mail merge document | Mailmerge | |||
MailMerge Issues - doc 'forgets' source data, other issues | Mailmerge | |||
Word 2003 Mail Merge Issues - Help Needed | Mailmerge | |||
Mail Merge Issues from Access to Word | Mailmerge |