VB.Net VSTO for Excel Timer Event locks up Excel

[taxidev]

I have a VB.Net addin for Excel that let's the user download data files from a 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. 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 the sheet than 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 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. I'm beginning to think the only solution is to change from an addin to a true .Net application with Excel automation instead, but that's not what my client wants.

Does anyone know a solution, or know where I need to look to get one? I've searched the web for weeks, and read through an abundance of forums, but nothing really handles this kind of situation.

Thanks for any information that can help me.

[3com]

Why not ...?

From vb.net.
1 - Run the ftp monitoring.
2 - If a new file exist, save the url of the file in an array.
3 - Close monitoring.
4 - Run the macro in excel and you pass the array to the procedure, which takes care of downloading the files.
5- Run the download process, without monitoring runing.

In other words, the two processes run separately.
What you want to do is best done from VB.Net that from Excel VBA.

HTH

[taxidev]

The whole thing is done from within the vb.net addin, no code is in Excel because the user can open any sheet. What your explanation has left out is how the monitoring thread activates the import once the monitoring is closed. Plus we don't want to close monitoring until the user chooses to do that. Activating the import from the ftpmonitor makes everything run from within that separate thread, which was my original issue.
But thanks for the attempt.

[3com]

pseudo-code (not real code, only for illustrate idea purpose)

Dim arrDL() as variant ' Public array variable to store found files (if any), thus it can be accessed by any procedure.

private sub commandButton1_click()
  openMonitoring 	
end sub

Sub openMonitoring
  'code start monitoring here
  if file found then arrDL= item.Found ' populate arrDL array with url files. 
end sub
 
priate sub commandButton2_click()
 'User choose close monitoring
  CloseMonitoring 'call CloseMonitoring and return, and call StartDownload procedure
  StartDownload		
end sub  

sub StartDownload()
  if ubound(arrDL)<=0 then exit sub ' If not file found exit sub
   for i = 0 to ubound(arrDL)-1 ' loop through array files
      Download(arrDL(i))' change for the process you'are using to download file
   next i	
end sub