Automating Excel, stalls.

Targets Windows
1

I’m delving into new territory here. Office Automation.

Ok, I’ve done a couple of these where I read data from an Excel spreadsheet and later create a new spreadsheet and populate it with results. It works fine. For 10,000 to 20,000 rows.

This new project takes data from three cells in a spreadsheet, performs a query and populates the result into a cell in the same spreadsheet. There can be a few hundred to a few thousand rows. Right now I’m testing with one spreadsheet with just under a thousand rows.

My problem with the new project is it’s stalling. Not crashing, not having an exception, just stops executing the loop. It will quit working at random points in the spreadsheet and random points in the routine. So it’s not having trouble with the content of a specific cell, it can stall at the read cell, or the query or the write cell, it can be anywhere in the loop. Additionally, it gets upset when you close Excel before it’s done. If it stalls, you can close Excel and it doesn’t get upset, it doesn’t cause an exception, it just sits there staring at you.

Has anyone had experience with automating Excel? Have you had stalls like this?

My fear is this will become useless if it keeps stalling and the chances of stalling in a 5000 row sheet and not being able to ever complete it will cause a severe waste of time.

It works pretty well on my machine, only stalling a few times out of a hundred (i’ve been sowing this off, it’s a real time saver for a group of people) but it was way worse when I gave the finished app to the woman who will run it for real. She has a laptop, not old, pretty new actually.

This is Windows 7, Office 2007 and 2010, MSSQL database, happened with native client, switched to ODBC (for another reason) still does it. Xojo 2015r1. I thought I was sure I was using the correct version of the Office Automation plug-in but when I checked the one in the plugin folder is a few days newer than the one in the Office Automation Extras folder.

2

Hi Zane,

Could I ask you to check available memory, CPU, Disk, and Network load in the Task Manager (Ctrl-Alt-Del) when the program is running and when it stalls. I am curious if there is a lot of memory being used by either the program or by Excel.

Just guessing.

3

I had watched CPU use and there’s barely any activity during a normal run or at stall. It drops to zero on a stall.

I had to run it a couple times but it did stall, there was a memory spike. Not a bad spike. It goes from 2.48Gb to 2.58Gb. After 2 or 3 seconds it slowly released back to about half the spike. Quitting Excel didn’t change it, quitting the app released the remainder. No abnormal network activity but I wouldn’t expect any.

4

I would too…

I recall seeing people running into trouble with automating Excel because the commands were too literal a recording of what had been done by hand.
Lots of ‘change focus to next cell’ or ‘move down a row’ things.
Moving about makes Excel refresh and redraw, which is slow.

When I automate Excel, I usually launch Excel but then hide it.
And refer to cells specfically instead of moving about.
Obviously, if you arent doing any of that, ignore me…

5

Yeah, I actually forgot you could hide it. I did try turning screen updating off but that just makes it open a outline of the window and look like it’s not responding. Yay Windows!

I’m looping through reading the cells I need and acting on them. Incidentally, Is there a way to ask for a range of cells (like B1 to D1) and get an array back? Would that be more efficient than reading B1, C1 and D1 individually? My efforts to accomplish this have failed so far.

I am indulging in one frivolous feature, and that’s making it follow along with what row it’s on. I don’t think I’m going crazy with it too much, just a .show(cell) when it reads. It does slow it down a bit but not much. There really should only be a few hundred rows to work with anyway. Right now it’s just a small window that’s a drop target. No controls, no options.

Now that I’ve had a chance to run it a few more times it seems to be stalling on the query. It reads the cells just fine, sets up the query and begins to daydream. This is even more weird for me because I’ve written many apps that have database calls, including lots of calls to stored procedures and never run into this. There’s usually an error to work with.

There’s no error, no crash, just la-la land.

So far it hasn’t stalled when interacting with Excel like it did Friday.