Open mail merge source wookbook when document opens

[BMC]

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

 

[Chuck Henrich]

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.

 

[BMC]

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.

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?

 

[Chuck Henrich]

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.

 

[Cindy M -WordMVP-]

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
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.

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