none
VB.Net VSTO for Excel Timer Event locks up Excel RRS feed

  • Question

  • I have a VB.Net addin for Excel that let's the user download data files from an ftp location based on an ip address, and import those data files into a spreadsheet. In fully manual mode - ie user selects the files in a dialog and then the work is done - everything works fine.

    But my client wants the process to be automated. This means by pushing a ribbon button, an ftpmonitor object continually scans the ftp site for new files, based on a timer event. When it finds one, it downloads it and imports the file to a spreadsheet. Then the user can push another button in the ribbon to cancel monitoring the ftp. This works, but it takes about 50 times longer for the data to populated in the sheet than it does in manual mode. I learned that because Excel is not a multitasking environment, having another thread ( the timer activated ftpmonitor ) interact with Excel slows everything down.

    So I tried simulating a timer event with an endless loop of DoEvents() in a while loop. That made the data import to the sheet just like in manual mode, but now we can't interact with the ribbon to cancel monitoring. Excel is held captive by the addin. The only way to end the monitoring is to close Excel, which is very bad.

    Is there a way to have the ftpmonitor running constantly, but also allow the user to hit the cancel button?

    Thanks for any information that can help me.

    Tom

    Thursday, April 17, 2014 5:57 PM

Answers

  • Hello Tom,

    You can run another thread for checking the ftp site and downloading the required data. Then, if you need to access the Excel object model, you can signal to the main thread of your add-in that the data is arrived and process it on the UI thread.

    Sunday, April 20, 2014 8:31 PM