0

I have a method in my VBA code

Sub SaveCopyOfFile(sourcePath As String, destinationPath As String)

    Set FSO = New fileSystemObject
    FSO.CopyFile sourcePath, destinationPath
    
End Sub

which will take a long time if the user select a location on the network. This causes Excel to think that its not responding even if it's working fine.

I know that this process will take a very long time and I don't want users to panic and force close the application in the middle of this. How can I make sure that Excel does not change its display to "Excel is not responding".

If someone needs to know why this would take a long time:

  1. Report template file could be located on the network drive.
  2. The macro creates an excel report and the user can choose its destination to be on the network
  3. The macro downloads images from the network and inserts them into the newly created excel file.

I've added a lot of code to help mitigate this such as doing everything local and only transferring the completed report to the desired destination after everything is done. The last step causes the Excel application to think that it has stopped responding.

Note if left alone the task will complete and Excel will revert back to normal from its "Not Responding" state.

Mandelbrotter
  • 2,216
  • 2
  • 11
  • 28
  • 1
    @braX Not entirely true but certainly not trivial. There are a number of questions on this site concerning how to do [multithreading](https://stackoverflow.com/q/5721564/5162073). – Brian M Stafford Sep 21 '20 at 20:52
  • I guess I'll have to popup a message to the user to let them know that the next execution might take a long time and to disregard the "Not Responding" state. – Mandelbrotter Sep 21 '20 at 20:52
  • @Brian M Stafford any way of applying a multi-threading solution for this problem? – Mandelbrotter Sep 21 '20 at 20:53
  • @Mandelbrotter Sure, that's what the link covers fairly well. In general, you need to pick an approach and then move the CopyFile logic into a separate thread. – Brian M Stafford Sep 21 '20 at 21:15

0 Answers0