Waiting for Filecopy process to finish

[VBAguy22]

Hello there,
I have a program that copies an Excel file to a temporary location, modifies and then prints it.
I am using FileCopy to copy the file to C:\TempSometimes, depends on how many programs I have loaded and how long ago i restarted my computer, I get "Permission Denied" error because the modification begins before the file finished copying.
How do I make my program wait for Filecopy to finish before opening the file?
Right now I am using Sleep 10000 (to be sure that it's done -- worst case scenario 10 seconds) but it's taking way too long because I am repeating the process for dozens of files

 

[PHV]

Why not open the file, modify it, SaveAs it and then print it ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[VBAguy22]

Because I copy it once then modify it many times.
Imagine keep SavingAs every time I modify the original
I only save the last changes anyways

 

[PHV]

So, why not opening the original, SaveAs it and Close it instead of FileCopy that seems unreliable for you.

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[TonyJollans]

Hi VBAguy22,

Don't know if it will help you, but take a look at thread702-58410

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[IvanMoala]

What version of Excel and OS ??

Try this ... worked for me (Xl2000 / WinXP)
Using the powerful Shell32.dll

Just amend to suit your situation .... Note I have included the Progress Dialog just to show what is happening
You can of course change this by using the other Enumerated const.

Dim strDestinationFolder As String
Dim strCopyFromFolder As String
Dim objShell As Object
Dim objFolder As Object

Enum F
    FOF_CREATEPROGRESSDLG = &H0&
    FOF_NOCREATEPROGRESSDLG = &H4&
    FOF_CREATENEWFLDERIFEXISTS = &H8&
    FOF_COPYALLNOPROMPT = &H10&
    FOF_ALLOWUNDO = &H40&
    FOF_CREATEPROGRESSDLGNOTEXT = &H100&
End Enum

Sub ShellCopy()

'//The destination Folder to CopyTo:
strDestinationFolder = "C:\B"
Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.NameSpace(strDestinationFolder)

'//The source Folder to CopyFrom:
strCopyFromFolder = "C:\A"
objFolder.CopyHere strCopyFromFolder, F.FOF_CREATEPROGRESSDLG Or F.FOF_COPYALLNOPROMPT

MsgBox "The rest of my code is running NOW!!"


End Sub

Ivan F Moala

http://www.xcelfiles.com