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.