Filecopy For transfering files

[bradyboyy88]

Hi,

I was wondering if anyone knows if the macro continues when you use filecopy or does the macro wait for the file to finish copying before moving on to the next line of code?

 

[Jaafar Tribak]

AFAIK, the vb(a) as well as the FileSystemObject FileCopying operations are both Synchronous so calling code will wait if a large file is being copied.

If you want the code not to wait, you could performe the copying from the commandline via the use of the Shell Function.. Something along these lines :

Sub test()

    Dim sSourceFile As String, sDestFile As String
    
    sSourceFile = "C:\Users\File.mp4"
    sDestFile = "C:\Users\Test\File.mp4"
    
[COLOR=#008000][B]    'Asynch File Copy[/B][/COLOR]
    Shell "xcopy " & sSourceFile & " " & sDestFile & "*", vbHide
    
[B][COLOR=#008000]    ' Other code resumes here immediatly without waiting for the file copy operation to finish.[/COLOR][/B]
    
    Debug.Print "resuming code"


End Sub

 

[bradyboyy88]

For some reason this is not doing anything for me. Its not drawing any errors just not copying the file. Any ideas?

 

[Jaafar Tribak]

Did a quick MrExcel search on executing DOS commands like xcopy and found a nice explanation by our member Rick Rothstein here: shell commands in excel

Based on that, I gave the following a shot and successfully copied the file MyVideo.MP4 from the RAX10 folder onto the Test folder ... change the paths as required.

Sub Test()
    Dim source As String, Destination As String
    
    source = "C:\Users\Info-Hp\Desktop\RAX10\MyVideo.MP4"
    Destination = "C:\Users\Info-Hp\Desktop\Test\"
    Shell Environ$("comspec") & " /c xcopy """ & source & """ """ & Destination & """ ", vbHide
End Sub

 

[bradyboyy88]

Works like a charm so thanks, does xcode prevent from being able to rename it on transfer similar to filecopy?

Basically what my code is doing with filecopy is taking the file and copying it to a new directory but renaming it with a standard convention (which we track in a database basicallly). So renaming it on transfer is kind of important. I am looking to see if this is possible with filesystemobject but it looks like you still have to wait till it transfers.

 

[Jaafar Tribak]

I couldn't make it rename the copied file .. so I am not sure really.

I guess you could use Dir inside a loop to keep checking until the file is finally copied and then you can rename it using Name Src As Des ..

 

[bradyboyy88]

Yea so adding * to the destination name allows you to rename it. Your code works great with that addition!