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

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!

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!

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

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