Excel throws exception if cell being edited

I'm at my wits end with this bug.. I'm hoping this is so common, that there is a very simple fix.

I have a C++ app that does COM based import/export of data directly from Excel, but I have to think that anyone doing VBA would see this all the time..

All of my calls to Excel to determine current workbook, how many workbooks, current worksheet, how many worksheets, getting/setting cell data all work wonderfully.. unless the user was editing a cell in the worksheet..

If the worksheet is left in 'editing mode', almost all of the COM calls will thow an exception, and not return successfully.

To get out of 'editing mode', I have tried everything from SendKeys("ENTER") to closing the damn Workbook & reopening it (and switching workbooks, activating, etc etc)

The machine either locks up, or just throws more exceptions.

I've meticulously gone through excel9.tli and excel9.tlh, looking for some method remotely pertainant, such as a 'deactive' or 'end edit' or anything that would allow the COM automation calls to succeed..

This is so very frustrating, because one very common user action in Excel can bring (the functionality of) my whole app to its knees..

Any help/pointers/direction/solution would be greatly appreciated..

Thank You.
[email protected]

I haven't tried to do this with automation, but all of us in the VBA sphere
just know that if a cell is being edited then VBA will not function.

In most VBA situations, it's hard to get into this position. If VBA is
running you can't edit, if editing VBA can't run, so the most typical time
that we see this is when scheduling routines to run at a future time.

There is no method that I know of that will get you out of edit mode
unfortunately. I'd guess you are looking at some kind of control loop in VB
that creates a delay and a retry if your call fails.

Alternatively, can you do this with another hidden instance of Excel rather
than the instance that can be locked up by your users? Or, can you have a
launch routine that asks your user if it is ok to run, and an exit message
when your code has run. This way if the launch routine fails you don't do
anything. I know I'd be annoyed if Excel got hijacked while I was working on
something.

Robin Hammond
www.enhanceddatasystems.com

"Pixeled" <[email protected]> wrote in
message news:[email protected]...
>
> I'm at my wits end with this bug.. I'm hoping this is so common, that
> there is a very simple fix.
>
> I have a C++ app that does COM based import/export of data directly
> from Excel, but I have to think that anyone doing VBA would see this
> all the time..
>
> All of my calls to Excel to determine current workbook, how many
> workbooks, current worksheet, how many worksheets, getting/setting cell
> data all work wonderfully.. *unless* the user was editing a cell in the
> worksheet..
>
> If the worksheet is left in 'editing mode', almost all of the COM calls
> will thow an exception, and not return successfully.
>
> To get out of 'editing mode', I have tried everything from
> SendKeys("ENTER") to closing the damn Workbook & reopening it (and
> switching workbooks, activating, etc etc)
>
> The machine either locks up, or just throws more exceptions.
>
> I've meticulously gone through excel9.tli and excel9.tlh, looking for
> some method remotely pertainant, such as a 'deactive' or 'end edit' or
> anything that would allow the COM automation calls to succeed..
>
> This is so very frustrating, because one very common user action in
> Excel can bring (the functionality of) my whole app to its knees..
>
> Any help/pointers/direction/solution would be greatly appreciated..
>
> Thank You.
> [email protected]
>
>
> --
> Pixeled
> ------------------------------------------------------------------------
> Pixeled's Profile:
> http://www.excelforum.com/member.php...o&userid=26932
> View this thread: http://www.excelforum.com/showthread...hreadid=401558
>

Not surprising. My guess is that it's an artifact of XL's long life.
XL doesn't allow any macros to run when it is in edit mode. Which was
just fine, I assume, when the only way to run code was by asking XL to
do so. However, over time MS has come up with other ways to run code
that interacts with XL. And, XL hasn't been updated to deal with such
demands. Consequently, it crashes.

Another way to cause an XL crash is to schedule VBA code with the
SetTimer/KillTimer APIs. [On the other hand, if one uses XL's own
OnTime method to run code, the operation is delayed until XL exits edit
mode.]

My suggestion would be assume that if you are using the same instance
of XL as a human, you need to coordinate your actions with that person.
Alternatively, instantiate your own copy of XL (keep it hidden) and do
your thing with that instance.

Not to mention that forcibly exiting edit mode (assuming you find a way
to do that) is extremely customer-hostile. Do that to me once and I
trash your software.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <[email protected]>,
[email protected] says...
>
> I'm at my wits end with this bug.. I'm hoping this is so common, that
> there is a very simple fix.
>
> I have a C++ app that does COM based import/export of data directly
> from Excel, but I have to think that anyone doing VBA would see this
> all the time..
>
> All of my calls to Excel to determine current workbook, how many
> workbooks, current worksheet, how many worksheets, getting/setting cell
> data all work wonderfully.. *unless* the user was editing a cell in the
> worksheet..
>
> If the worksheet is left in 'editing mode', almost all of the COM calls
> will thow an exception, and not return successfully.
>
> To get out of 'editing mode', I have tried everything from
> SendKeys("ENTER") to closing the damn Workbook & reopening it (and
> switching workbooks, activating, etc etc)
>
> The machine either locks up, or just throws more exceptions.
>
> I've meticulously gone through excel9.tli and excel9.tlh, looking for
> some method remotely pertainant, such as a 'deactive' or 'end edit' or
> anything that would allow the COM automation calls to succeed..
>
> This is so very frustrating, because one very common user action in
> Excel can bring (the functionality of) my whole app to its knees..
>
> Any help/pointers/direction/solution would be greatly appreciated..
>
> Thank You.
> [email protected]
>
>
> --
> Pixeled
> ------------------------------------------------------------------------
> Pixeled's Profile: http://www.excelforum.com/member.php...o&userid=26932
> View this thread: http://www.excelforum.com/showthread...hreadid=401558
>
>