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!