Word Mail Merge and Personal.xls problem
 

Hi,

I've build a macro in Excel to perform a Mail Merge in Word with a
small Printfile.xls. The macro itself is stored in a personal.xls.
Everytime the macro performs the "OpenDataSource" function in Word, the
whole system comes to a halt and I have to wait for another
excel-instance to start (I guess from within Word) which will always
show me a messagebox to tell me the personal.xls workbook is locked.

How can I evade this? I don't need excel to open the personal.xls for
me again when opening the printfile.xls.

I'm rather stuck here and any help will be very appreciated.
Thanks in advance,

Dockum

Word Mail Merge and Personal.xls problem
 

More info please; The VBA code is in Personal.XLS;
where is the data source?




"Dockum" wrote in message
oups.com...
Hi,

I've build a macro in Excel to perform a Mail Merge in Word with a
small Printfile.xls. The macro itself is stored in a personal.xls.
Everytime the macro performs the "OpenDataSource" function in Word, the
whole system comes to a halt and I have to wait for another
excel-instance to start (I guess from within Word) which will always
show me a messagebox to tell me the personal.xls workbook is locked.

How can I evade this? I don't need excel to open the personal.xls for
me again when opening the printfile.xls.

I'm rather stuck here and any help will be very appreciated.
Thanks in advance,

Dockum

Word Mail Merge and Personal.xls problem
 

The VBA code is in Personal.xls
The data for the mailmerge is in Printfile.xls on a network drive.

The process is as follows:
From the workbook Production.xls a call is made to the VBA code in
Personal.xls. This macro first copies selected data from Production.xls
to a Printfile.xls. It then saves and closes the Printfile.xls. After
that the macro makes a call to the Word.application object and opens a
known file (readonly) with mergefields. Until this point, everything is
smooth. Then a wordfile.mailmerge.opendatasource
filename:=Printfile.xls, ReadOnly:=True is started. This seems to last
forever and after a while there is a messagebox, saying "Personal.xls
is locked by user [me] and cannot be accessed, blabla". When I choose
"readonly" everything is ok again and the mailmerge goes on.
I'm not sure what is taking Word so long with this kind of mailmerge
(DDE I presume) and why it is important to open the personal.xls in
this case. And I'm stuck on how to prevent Word from opening this
personal.xls.

Thanks for looking in to this, hope this helps and if you need more
information please let me know.



Roger Whitehead schreef:
More info please; The VBA code is in Personal.XLS;
where is the data source?




"Dockum" wrote in message
oups.com...
Hi,

I've build a macro in Excel to perform a Mail Merge in Word with a
small Printfile.xls. The macro itself is stored in a personal.xls.
Everytime the macro performs the "OpenDataSource" function in Word, the
whole system comes to a halt and I have to wait for another
excel-instance to start (I guess from within Word) which will always
show me a messagebox to tell me the personal.xls workbook is locked.

How can I evade this? I don't need excel to open the personal.xls for
me again when opening the printfile.xls.

I'm rather stuck here and any help will be very appreciated.
Thanks in advance,

Dockum

Word Mail Merge and Personal.xls problem
 

If Production.xls establishes an Object Variable to reference Personal.xls,
is the Object Variable terminated (= nothing) it's business is complete?
Same for the Object Variable that calls PrintFile.xls.


--
HTH
Roger
Shaftesbury (UK)






"Dockum" wrote in message
oups.com...
The VBA code is in Personal.xls
The data for the mailmerge is in Printfile.xls on a network drive.

The process is as follows:
From the workbook Production.xls a call is made to the VBA code in
Personal.xls. This macro first copies selected data from Production.xls
to a Printfile.xls. It then saves and closes the Printfile.xls. After
that the macro makes a call to the Word.application object and opens a
known file (readonly) with mergefields. Until this point, everything is
smooth. Then a wordfile.mailmerge.opendatasource
filename:=Printfile.xls, ReadOnly:=True is started. This seems to last
forever and after a while there is a messagebox, saying "Personal.xls
is locked by user [me] and cannot be accessed, blabla". When I choose
"readonly" everything is ok again and the mailmerge goes on.
I'm not sure what is taking Word so long with this kind of mailmerge
(DDE I presume) and why it is important to open the personal.xls in
this case. And I'm stuck on how to prevent Word from opening this
personal.xls.

Thanks for looking in to this, hope this helps and if you need more
information please let me know.



Roger Whitehead schreef:
More info please; The VBA code is in Personal.XLS;
where is the data source?




"Dockum" wrote in message
oups.com...
Hi,

I've build a macro in Excel to perform a Mail Merge in Word with a
small Printfile.xls. The macro itself is stored in a personal.xls.
Everytime the macro performs the "OpenDataSource" function in Word, the
whole system comes to a halt and I have to wait for another
excel-instance to start (I guess from within Word) which will always
show me a messagebox to tell me the personal.xls workbook is locked.

How can I evade this? I don't need excel to open the personal.xls for
me again when opening the printfile.xls.

I'm rather stuck here and any help will be very appreciated.
Thanks in advance,

Dockum

Word Mail Merge and Personal.xls problem
 

Hi,
I've tinkered with creating the object variables printfile.xls and
mailmerge.doc but I'm not sure how to establish a reference to
personal.xls; maybe I need to tell some more about the process I wish
to automate..

There is this Production.xls workbook opened in Excel with client data:
one client in each row. When a client needs to be notified of
something, we select the row with wanted data. Open another workbook
(Printfile.xls) and copy-paste this row in the printfile.xls workbook.
Then we open Word, select the letter we need to send and make a
mailmerge with the printfile.xls.
In the meantime the production.xls workbook is open. After making a
letter we go on making changes to the data in the sheets.

So, the macro is called from production.xls. Within production.xls
there is a reference (automatically it seems) to VBAproject Persnlk.xls
(personal.xls in english) with macro's and other objects. When the
macro opens the printfile.xls there is no problem with the reference to
Persnlk.xls. It can copy-paste the selected rows in a flash and saves
the Printfile.xls
Only when the macro starts a worddoc.mailmerge.opendatasource
Filename:=Printfile.xls there is this message of not being able to open
the Persnlk.xls.

I believe this is the weirdest part of it: when called from a different
office.application the vbaproject Persnlk.xls is locked, but when
called from the same application (excel) there is no problem. Any
ideas?

Thanks in advance,
Dockum

Word Mail Merge and Personal.xls problem
 

Hi,
I've tinkered with creating the object variables printfile.xls and
mailmerge.doc but I'm not sure how to establish a reference to
personal.xls; maybe I need to tell some more about the process I wish
to automate..

There is this Production.xls workbook opened in Excel with client data:
one client in each row. When a client needs to be notified of
something, we select the row with wanted data. Open another workbook
(Printfile.xls) and copy-paste this row in the printfile.xls workbook.
Then we open Word, select the letter we need to send and make a
mailmerge with the printfile.xls.
In the meantime the production.xls workbook is open. After making a
letter we go on making changes to the data in the sheets.

So, the macro is called from production.xls. Within production.xls
there is a reference (automatically it seems) to VBAproject Persnlk.xls
(personal.xls in english) with macro's and other objects. When the
macro opens the printfile.xls there is no problem with the reference to
Persnlk.xls. It can copy-paste the selected rows in a flash and saves
the Printfile.xls
Only when the macro starts a worddoc.mailmerge.opendatasource
Filename:=Printfile.xls there is this message of not being able to open
the Persnlk.xls.

I believe this is the weirdest part of it: when called from a different
office.application the vbaproject Persnlk.xls is locked, but when
called from the same application (excel) there is no problem. Any
ideas?

Thanks in advance,
Dockum

Word Mail Merge and Personal.xls problem
 

Hi,

Ok (feeling like a novice dummy) I believe it's important to tell how I
solved this and close this discussion.

After an extensive search in more newsgroups, I decided to take the
code to an xla-file. I added code to close the Personal.xls before
starting the mailmerge. And took all code out of Personal.xls.

I have no experience whatsoever with xla, addin or anything, so I'm
quite content with this solution, which enables me to learn more on
excel (office?) programming.

Right now, I'm searching for a way to have the menubuttons I made,
always call the macro's in the local workbook; opening several
workbooks in the same instance of Excel seems to confuse the macro
call; already closed workbooks are opened again to make calls to
macro's which I know are also available in the current workbook.
I will search for the solution on this one first... I know it must be
'out there'.

Thanks for all the help.

Greetings, Dockum

[Thread closed]

Word Mail Merge and Personal.xls problem
 

Hi Dockum,

I don't use a macro for MailMerge but I know that I will have trouble if the sheet I want to use is
not the first worksheet in the workbook.

Word Mail Merge and Personal.xls problem
 

Hi Dockum,

If the personal.xls file appeared to be the active file that might confuse
MailMerge so .... Was your personal.xls file hidden. Window (menu), hide, personal.xls

---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Dockum" wrote in message oups.com...
After an extensive search in more newsgroups, I decided to take the
code to an xla-file. I added code to close the Personal.xls before
starting the mailmerge. And took all code out of Personal.xls.