Open mail merge source wookbook when document opens

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

BMC - 01 Feb 2006 11:00 GMT

I have users who have a Word doc that does a mail merge from an Excel
workbook and want the workbook to open for on the fly editing as the doc
opens.

Now whilst the code to fire up Excel and open the document is trivial, e.g.

Sub AutoOpen()
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlWB = xlApp.Workbooks.Open("c:\mail.xls")
End Sub

the workbook opens as "read only". If opened manually after the doc opens,
it opens normally. If the same macro is used where mail merge is not set up
the document is not read only.

Any ideas how to make the workbook open editable?

Cheers

Signature

BMC
Delete delete and remove remove to email

Reply to this Message

Chuck Henrich - 01 Feb 2006 14:15 GMT

Hmm. I tried your code and it works fine. My test workbook opened as fully
editable and not read only. If you want to ensure that it opens read/write
and editable, you can set the ReadOnly and Editable paramaters (check the
Excel VBE help for details):

Set xlWB = xlApp.Workbooks.Open("c:\mail.xls", , False, , , , , , , True)

There may be other reasons why your workbook is opening read only, eg, if
it's already open (or flagged incorrectly as open), or the file itself has
the readonly attribute on, or there's something funky about opening it in an
AutoOpen macro in your circumstances.

Signature

Chuck Henrich
www.ProductivityApps.com
Stylist Style Generator - automatically create and define sets of paragraph
numbering and heading styles

> I have users who have a Word doc that does a mail merge from an Excel
> workbook and want the workbook to open for on the fly editing as the doc
[quoted text clipped - 15 lines]
>
> Cheers

Reply to this Message

BMC - 07 Feb 2006 10:26 GMT

> Hmm. I tried your code and it works fine. My test workbook opened as fully
> editable and not read only. If you want to ensure that it opens read/write
[quoted text clipped - 7 lines]
> the readonly attribute on, or there's something funky about opening it in an
> AutoOpen macro in your circumstances.

Even when setting these attributes the Excel worksheet opens as read only
(becasue Word has already opened it for the mail merge.

Maybe the way to go is to somehow force Word to open the spreadsheet
read-only?

Reply to this Message

Chuck Henrich - 07 Feb 2006 11:42 GMT

Ah, well, if the spreadsheet is already engaged as a data source by an open
mail merge form, then it makes sense it would be read only because it's in
use. There's no way around that except to edit it as a merge data source or
to close the mail merge form (and disconnect the data source) before opening
the spreadsheet, AFIK.

Signature

Chuck Henrich
www.ProductivityApps.com
Stylist Style Generator - automatically create and define sets of paragraph
numbering and heading styles

> > Hmm. I tried your code and it works fine. My test workbook opened as fully
> > editable and not read only. If you want to ensure that it opens read/write
[quoted text clipped - 13 lines]
> Maybe the way to go is to somehow force Word to open the spreadsheet
> read-only?

Reply to this Message

Cindy M -WordMVP- - 08 Feb 2006 14:39 GMT

Hi =?Utf-8?B?Qk1D?=,

You need to tell us which version of Word is involved with the mail merge, and
which connection method has been used to connect the excel data in as the data
source. There may be a way this can be done, but without this basic
information there's no way to have a productive discussion.

> I have users who have a Word doc that does a mail merge from an Excel
> workbook and want the workbook to open for on the fly editing as the doc
[quoted text clipped - 11 lines]
> it opens normally. If the same macro is used where mail merge is not set up
> the document is not read only.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail :-)

Reply to this Message