Microsoft Excel - VBA - CopyFile Run-time error 70 Permission Denied

Asked By Dwane Crist on 12-Aug-09 02:05 PM

I am using the following code in an attempt to copy a file from one folder to another, but I get the above referenced error.  Any suggestions?

    Sub CopyFilesWithInput()
'Get Source Folder
  Source_Folder = Application.InputBox("Enter Source Folder Name", _
                                       "Source Folder Name", Type:=2)
   If Source_Folder = "False" Then Exit Sub
'Get Destination Folder
  Destination_Folder = Application.InputBox("Enter Destination Folder Name", _
                                            "Destination Folder Name", Type:=2)
   If Destination_Folder = "False" Then Exit Sub
'Build Source and Destination Variables
    sfol = "f:\dept\acctg\acct year\2009\month end\" & Source_Folder
    dfol = "f:\dept\acctg\acct year\2009\month end\" & Destination_Folder
'Perform Copy
     Set fso = CreateObject("Scripting.FileSystemObject")
         fso.CopyFile (sfol & "\kronos wo aas.xlsx"), dfol

Thanks,

Dwane


Do this. - [)ia6l0 iii replied to Dwane Crist on 12-Aug-09 10:18 PM

My first guess would be that the destination folder for file copy does not have a "\" at the end. So please add one. 
Like,
dfol = "f:\dept\acctg\acct year\2009\month end\" & Destination_Folder & "\"

Number two, make sure that the file you are trying to copy does not have read-only attribute set. The CopyFile method of the filescriptingobject has a limitation and more info on that can be found at this KB article.

And i think you need to create the destination folder if it not exists. Like,
If Not fso.FolderExists(dfol) Then
fso.CreateFolder (dfol)

VBA - CopyFile Run-time error 70 Permission Denied - Matt Lesniewski replied to Dwane Crist on 12-Aug-09 10:40 PM

This error occurs when you are trying to copy a file that is open.

Are you running the code in the "kronos wo aas.xlsx" file by any chance?

Try a Save As function instead if this is the case.

Something along the lines of:

  ActiveWorkbook.SaveAs Filename:= dfol & "\kronos wo aas.xlsx"