Word 2007 mail merge with Excel for more users at same time

[PeterHS]

We are using a Excel file with clients as data source for Word. With word 97
this was no problem, more people could access the excel file at the same time.

Now I converted all to office 2007 and it is no longer possible. When one
user opens a mail merge document with the Excel file as a data source and a
other user tries to use the file as a data source a message will appear
saying the file is exclusive in use .

At first I wanted to make the excel file a template, but it isn’t possible
to connect word to a excel template (I asked this question at this forum).

I was suggested to make the Excel file read only but this won’t work either.

I tested many ways for 2 days now like:

- Making the connection “by hand†and test different settings like “share
deny noneâ€, “read onlyâ€, “open exclusive false†and so on. Even when I see
that the file is marked “read only†on the server for one user the other user
still can’t connect.

- I tried saving the excel file with different settings for sharing and
password protection; no luck.

- I tried different types of connections (mostly read only) I get the
connection but still it is locked for other users.

It’s planed that all users start working with 2007 on Monday, so I need to
have it working then. Any solution is welcome.

Many thanks.

Peter

 

[Doug Robbins - Word MVP]

In Word, click on the Office button and then on the Word Options item in the
bottom border of the dialog and then on Advanced and scroll down to the
General section of the dialog and check the box for "Confirm File Format
Conversion at Open". Then when you connect the mail merge main document to
the data source, try the different methods of connection that are available.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[PeterHS]

Thanks for your quick respons,

I didn't tought of this. I tried it but it is no real solution:
- Converter -> says it couldn't read the data.
- ODBC -> the file opens, but I don't see tabels or ranges to connect to.
- DDE seems to work but gives problems (opens Excel, needs respons to use
read only in Excel and so on).

I tought of some trick myself, I saved the Excel file as .XLS (Excel 97
2003), that does the trick. i have no clue what converter or method for
opening is used (not DDE Excel isn't open), but it works.

I had some problems when I want to update the file. When the file is open
for update by one user and a other user want's to use it as datasource he
gets a massage "cann't open used exclusive". I solved this problem by making
the excel file "shared" now its usable as datasource without problems.

It works now there are only two snags left:
When the file is used as datasource in a open Word document;
- The Excel file cann't be opened in Excel for editing.
- When the Excel file is open for editing befor it is opened as datasource
it cann't be closed in Excel (Excel say's exclusive use try later). It even
isn't posible to close the file without saving, but very strange it is
possible to close Excel and the file wil be closed also without saving
offcourse ?????????.

Now I have something that works, I'm not happy with it it's not standard
and looks fuzzy for the users. I hope that Microsoft wil solve this problem
so its possible to use a XLSX, XLSM, XLTX and XLTM file as datacourse read
only and it can be edit at the same time.

For Someone with the same problem this is at the and how it works.
- Save the Excel file as excel 97 -2003 (xls) and as "shared"
- Use as datasource is possible without restrictions.
- Open for editing in Excel is not possible when open as datasource.
- Save after editing is not posible when it is opened as datasoure while
editing (you have to wait). Closing without saving isn't posible either,
Closing Excel is possible (changes are not saved).

Extra:
My mailmerge document is made from template, here you got the problem that
the datasource is opend twice when you make a document (once for the
template, once for the new document). One solution by Mirosoft is "change the
registry so it wil not check "select"". I don't want this solution because I
want to stay out the registry in our terminnal server setting.

My solution:
- Make the template with the datasource and mergefields.
- Make from the temlete a "normal document" the link to the datasource is
broken, but the mergefields stay.
- In the Template for the event "new document' the folowing macro is
executed wich wil make the link with the datasource for the document.

The macro will Make a mailmerge document, then conect to the file
"ond_lev.xls", to the sheet `Ond_lev_lijst$` (I think it's possible to give
here a rangename also). At last it is set to show the results instead off the
fieldnames.

Hope others benefit from my 15 hour searching and trying.

Peter


Comments with the macro are in Dutch sorry, it discribes the problem and the
solution wich mentioned before.

Sub koppel_data()
'
' Deze macro wordt aangeroepen door de tyrigger "new document" zoals staat
in "thisdocument"
'
' Macro om een koppeling te maken met een Excelbestand
' Als er op basis van deze sjabloon een nieuw document wordt gemaakt,
' wordt aan dit nieuwe document een excel bestand gekoppeld.
' deze manier van werken omdat als ik de datasouce aan de template hang en ik
' het gemaakte document opsla ik een probleem krijg als ik het document open
' De datasource van de template wordt nu ook geopend en dan de datasource van
' het document, dit geeft een probleem (gebruiker moet datasource selecteren
en bug)
'
' Aanmaak sjabloon -> gekoppeld en alle velden plaatsen vervolgens er een
"gewoon" document
' van maken (velden blijven koppeling weg) en dit opslaan
'
' Vast hier naar OND_LEV en hele eerste werkblad.
'

' Maak van document een primair bestand
ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
' Kopel datasource
ActiveDocument.MailMerge.OpenDataSource
Name:="G:\home\FORMUL\Alg_2007\Stand_Documenten\00 Algemeen\OND_LEV.xls", _
ReadOnly:=True, Openexclusive:=False, SQLStatement:="SELECT * FROM
`Ond_lev_lijst$`"

' Stel beeld in op waarden tonen i.p.v. veldnamen
ActiveDocument.MailMerge.ViewMailMergeFieldCodes = False

End Sub