Monday, July 20, 2009

Automating Excel from VB.Net - Link to Excel Lost


I've been maintaining some software of years that i wrote that compares inventory data with GIS data. It uses some of Excel's spreadsheet function to pull this off. I use Excel's object library to get the effect. It's worked great until recently and ironically the problem shows up most consistently on the fastest computer I've ever used at work up until now. In the middle of processing, I've been getting an error message that says that the "link to the spreadsheet has been lost" and then the program crashes. For days I could not find the problem. But then when I stepped through through the VB.Net code stey-by-step the program worked then I realized it was because I allowing the steps in Excel to finish before the rest tof the program continued. It turns out that because my program and Excel are being processed in different threads, the Excel thread could be lagging behind my code. I have had to put code into a function in VB that tells the processor to pause for a certain number of milliseconds. 1000 milliseconds is 1 second. Therefore

System.Threading.Thread.Sleep(10000)


makes the program pause for 10 seconds which would give the excel thread 10 more seconds to complete what it is doing. It's my theory.

No comments: