I have used late binding to access excel workbooks from my word macro. The
problem I appear to be having is that when I run the macro the first time it
runs smoothly. However if I run it again some of my files (ones for the mail
merge) are locked and won't open when I am trying to open them in the macro.
I do have it in my code to close those workbooks and quit the application.
Is there any way to test for the workbook.open command failing when it
doesn't open a workbook for this reason?
Is there a way of opening a workbook so that you would not get the locked
for editing by another user and notification of when it's available message?
Also when the macro bombs all the files and application are left open
causing errors when I am trying to debug it again, is there any quick fix to
clean up the files and close the application if the macro bombs before it has
a chance to do those things?
Any help is appreciated.
Thanks,
Heather

Go into the task manager. There are probably many instances of Excel still
running. You code is probably not releasing Excel because it has created
ghost references which can't be released or you just plain old haven't put
in the code to release the references. (setting variables to nothing in
reverse order to the way they were created and quiting the Excel
application.)

This can be subtle.

xlApp.ActiveSheet.Range("A1").Sort Key1:=Range("A1")

would create a ghost reference because the Range("A1") is not qualified all
the way back to a releasable reference

xlApp.Activesheet.Range("A1").Sort Key1:=xlApp.Activesheet.Range("A1")

would be the fix.

--
Regards,
Tom Ogilvy

"HeatherO" wrote in message
...
I have used late binding to access excel workbooks from my word macro.
The
problem I appear to be having is that when I run the macro the first time
it
runs smoothly. However if I run it again some of my files (ones for the
mail
merge) are locked and won't open when I am trying to open them in the
macro.
I do have it in my code to close those workbooks and quit the application.
Is there any way to test for the workbook.open command failing when it
doesn't open a workbook for this reason?
Is there a way of opening a workbook so that you would not get the
locked
for editing by another user and notification of when it's available
message?
Also when the macro bombs all the files and application are left open
causing errors when I am trying to debug it again, is there any quick fix
to
clean up the files and close the application if the macro bombs before it
has
a chance to do those things?
Any help is appreciated.
Thanks,
Heather

Does the 2000 version have a task manager. I've just been restarting my
computer as for the code I will have to go through and check it carefully. I
did close the workbooks and reset the objects to nothing but I will recheck.
Can you think of any reason though why sometimes when I tried to open a
certain workbook it failed to open it. Yet if I copied the data to a new
workbook and saved it under a new name it opened it no problem?
Thanks for your help
Heather

"Tom Ogilvy" wrote:

Go into the task manager. There are probably many instances of Excel still
running. You code is probably not releasing Excel because it has created
ghost references which can't be released or you just plain old haven't put
in the code to release the references. (setting variables to nothing in
reverse order to the way they were created and quiting the Excel
application.)

This can be subtle.

xlApp.ActiveSheet.Range("A1").Sort Key1:=Range("A1")

would create a ghost reference because the Range("A1") is not qualified all
the way back to a releasable reference

xlApp.Activesheet.Range("A1").Sort Key1:=xlApp.Activesheet.Range("A1")

would be the fix.

--
Regards,
Tom Ogilvy

"HeatherO" wrote in message
...
I have used late binding to access excel workbooks from my word macro.
The
problem I appear to be having is that when I run the macro the first time
it
runs smoothly. However if I run it again some of my files (ones for the
mail
merge) are locked and won't open when I am trying to open them in the
macro.
I do have it in my code to close those workbooks and quit the application.
Is there any way to test for the workbook.open command failing when it
doesn't open a workbook for this reason?
Is there a way of opening a workbook so that you would not get the
locked
for editing by another user and notification of when it's available
message?
Also when the macro bombs all the files and application are left open
causing errors when I am trying to debug it again, is there any quick fix
to
clean up the files and close the application if the macro bombs before it
has
a chance to do those things?
Any help is appreciated.
Thanks,
Heather

Can you think of any reason though why sometimes when I tried to open a
certain workbook it failed to open it.

I already gave you the answer.

Adjust your code to properly handle excel and your problem will go away.

--
Regards,
Tom Ogilvy


"HeatherO" wrote in message
...
Does the 2000 version have a task manager. I've just been restarting my
computer as for the code I will have to go through and check it carefully.
I
did close the workbooks and reset the objects to nothing but I will
recheck.
Can you think of any reason though why sometimes when I tried to open a
certain workbook it failed to open it. Yet if I copied the data to a new
workbook and saved it under a new name it opened it no problem?
Thanks for your help
Heather

"Tom Ogilvy" wrote:

Go into the task manager. There are probably many instances of Excel
still
running. You code is probably not releasing Excel because it has
created
ghost references which can't be released or you just plain old haven't
put
in the code to release the references. (setting variables to nothing in
reverse order to the way they were created and quiting the Excel
application.)

This can be subtle.

xlApp.ActiveSheet.Range("A1").Sort Key1:=Range("A1")

would create a ghost reference because the Range("A1") is not qualified
all
the way back to a releasable reference

xlApp.Activesheet.Range("A1").Sort Key1:=xlApp.Activesheet.Range("A1")

would be the fix.

--
Regards,
Tom Ogilvy

"HeatherO" wrote in message
...
I have used late binding to access excel workbooks from my word macro.
The
problem I appear to be having is that when I run the macro the first
time
it
runs smoothly. However if I run it again some of my files (ones for
the
mail
merge) are locked and won't open when I am trying to open them in the
macro.
I do have it in my code to close those workbooks and quit the
application.
Is there any way to test for the workbook.open command failing when
it
doesn't open a workbook for this reason?
Is there a way of opening a workbook so that you would not get the
locked
for editing by another user and notification of when it's available
message?
Also when the macro bombs all the files and application are left
open
causing errors when I am trying to debug it again, is there any quick
fix
to
clean up the files and close the application if the macro bombs before
it
has
a chance to do those things?
Any help is appreciated.
Thanks,
Heather

"Tom Ogilvy" wrote in message
...

This can be subtle.

xlApp.ActiveSheet.Range("A1").Sort Key1:=Range("A1")

That gives a 'Not Defined' error when called in a Word macro.


"HeatherO" wrote in message
...
I have used late binding to access excel workbooks from my word macro.

Since Word has a range object, let's adjust it to

xlApp.ActiveSheet.Range("A1").Sort Key1:=ActiveSheet.Range("A1")

--
Regards,
Tom Ogilvy


"Bob Phillips" wrote in message
...

"Tom Ogilvy" wrote in message
...

This can be subtle.

xlApp.ActiveSheet.Range("A1").Sort Key1:=Range("A1")

That gives a 'Not Defined' error when called in a Word macro.


"HeatherO" wrote in message
...
I have used late binding to access excel workbooks from my word macro.

"Tom Ogilvy" wrote in message
...
Since Word has a range object, let's adjust it to

But it doesn't have a Range(cell) object, so it bombs as you wrote it.

I was trying to illustrate a concept. If you have an example that actually
causes the problem, post away.

--
Regards,
Tom Ogilvy

"Bob Phillips" wrote in message
...

"Tom Ogilvy" wrote in message
...
Since Word has a range object, let's adjust it to

But it doesn't have a Range(cell) object, so it bombs as you wrote it.