1

Actually I have coded the following macro based on FileCopy method.

'MyObject copy function:
Public Function OutputFile(ByVal SrcPath As String, ByVal TrgPath As String) As Boolean

    If pDoesFileExist(SrcPath) Then   '<= Internal private function to check if file exists
        FileCopy SrcPath, TrgPath
        OutputFile = True
    Else
        OutputFile = False
    End If

End Function

Invoked from:

'Called subroutine from main program:
Sub OutputFiles(ByRef MyCollection As Collection, SourcePath As String, TargetPath As String)

    Dim Obj As MyObject

    With MyForm
        .Show "Modeless"
        For Each Obj In MyCollection
            If Obj.OutputFile(SourcePath, TargetPath) Then
                .TextBoxResult.Text = .TextBoxResult.Text & "File copied." & vbNewLine
            Else
                .TextBoxResult.Text = .TextBoxResult.Text & "File not copied!" & vbNewLine
            End if
        Next Obj
    End With

End Sub

The macro works perfectly when I make it run from/to my local computer folders, regardless of the file size (from a few KB to 20MB more or less).

But when I make it run using a work-domain source path (which is obviously slower than my computer), the instruction line FileCopy "freezes" on large files. The program is still running in background and the files are getting copied succesfully, however MyForm will go stucked [No Response] until the end of the execution.

Debugging step-by-step works "fine", I just have to wait that FileCopy instruction returns (10 seconds aprox), then keep moving forward.

I would like to know if there is a possible way to force this "wait until FileCopy returns", or to grant an immunity to the rest of my code against these mini freezes?

Oriol Jurado Paliès
  • 63
  • 7
  • 1
    VBA is single threaded, that means the thread responsible to update the UI (the form in this case) is busy copying the files. As for the "Not responding" message, this comes from windows when a UI is not responding after x number of seconds. You can try to add `DoEvents` before calling the next iterator but don't expect to see a huge difference. – Kostas K. Apr 20 '20 at 13:12
  • Alternatively, you could use a `Shell` command to do the copying, but then there is no way to know if it failed, or when it's complete. – braX Apr 20 '20 at 13:32
  • Dear @Koastas K. , I tried the DoEvents as you commented, I also moved a 'MyForm.Repaint' instruction from my subroutine iterator to a 'TextBoxResult Change' event. Now MyForm doesn't get stucked until the end. Sometimes it stop responds (when I click the form) or it hides behind the Excel application. – Oriol Jurado Paliès Apr 20 '20 at 14:18
  • @braX, thanks. I like the idea to make the OS work on the copy files process while the program is running. But I have been googling for some examples with no success... most of the users code doesn't work properly using Shell way, and the final solution is revert to the VBA native FileCopy, as I am doing... Could you please provide a code example? Also, I was thinking to add a Do..Until loop to check if the file exists in the target folder, with some kind of "timeout"(???) if I keep moving forward this solution. – Oriol Jurado Paliès Apr 20 '20 at 15:08
  • That's the effect of giving a command to the UI but it's unable to process it because it's busy. This command will get in a queue and `DoEvents` will simply execute those queued messages before it starts another run in the loop. – Kostas K. Apr 20 '20 at 16:04

0 Answers0