COM connection unable to list Excel workbooks when cell is edited

[newsman220 0]

I've written a script to list all my open Excel workbooks, so I can switch to them easily. (I run with a lot of workbooks open) The script pops the workbook to the front with a left click. Right-click opens a dialog of all that workbook's sheets, so I can go directly to that sheet.

The script checks every 5 seconds to see if the open list has changed.

One thing I've noticed - if I click into a worksheet cell so the cursor is there, the script blanks the list. The same is true if I'm clicked into the edit box at the top of the worksheet. Basically, any kind of cell editing.

This (the blanking) is the expected behavior when A) there are no worksheets open; the Excel object in the script isn't an object when the purge-workbook-list function runs as part of the list update.

When I leave the cell, the list is back within 5 seconds, as I would expect.

I've noticed Excel won't open or close a workbook when a cell is in the same condition. Click out of the cell, and the actions happen.

This is Excel 2007. I'm wondering if anyone else has noticed this. If so, has anyone programmed around it?

A lot of what I'm doing is with COM directly, e.g. $objExcel.Workbooks.Count, although I'll use a function when convenient.

Thanks

[water 1,847]

You can suppress cell editing by setting

$oExcel.Interactive = False

http://msdn.microsoft.com/en-us/library/ff841248%28v=office.14%29.aspx

[newsman220 0]

Handy, that. Unfortunately, I'm using the script to pop them up so I can edit them. It's just an odd situation, and I'm wondering if it's a problem with the Excel COM model.

Thanks for the pointer, though. I'll use that at some point.

[water 1,847]

I'm sure your script gets a COM error code when you edit a cell. Your script just has to be able to handle to handle this error and not blank out the list.

If you post your code we can have a look.

BTW: This example shows that it is a bad idea to let user and script work with Excel at the same time.

[newsman220 0]

water, you nailed the issue. I was using a blank error-handling function to simply absorb the problem. When I changed it to give me a debug message it flagged an error when the cell was open.

I added a global $COMError variable; the error-handler sets it to true. When the 5-second workbook comparison cycle comes around again, the list-comparison function checks for a COM connection by attempting to enumerate the workbooks. It sets $COMError to False if the count is > 0.

While $COMError is True the purge function won't run at all.

The script also ignores left and right-clicks whlie $COMError = True.

There is an edge condition where the COM error can exist, but the script has not reported it yet. To handle that, the function which enumerates a workbook's worksheets tests for the connection before building the dialog box, by trying to count the selected workbook's worksheets. Any result < 1 and the function returns silently.

In this case, I'm not trying to automate the Excel worksheets. I'm working around the clumsy Windows 7 method of grouping them all into a single taskbar item. I'm paid to manage projects; I have about 30 running right now, with many-paged Excel spreadsheets as project logs for each one. I can have a dozen open at a time. So I really just wanted to make a quick-picker that sits on the side of my second monitor.

This method should be adaptable for people with lots of Word files open, too.

Possibly the next extension to this would be to have it monitor my Projects folder & subfolders for Excel workbooks with "project log" in the name and present those, too, but in a different color in the listview. That should be a nice challenge for an evening.

Thanks for the help!

[water 1,847]

Glad you got your problem solved