I'm trying to copy an Excel File from one location to another using FileSystemObject:

For Each fil In fso.GetFolder(sourceFld).Files
            If UCase$(fso.GetExtensionName(fil)) = "XLS" Then
                    fso.CopyFile fil, destination 'error occurs here
            End IF
Next

I get a permission denied error on the line noted above. I see that this error is very common with FileSystemObjects, usually related to permissions on IUSR_machinename - however, this doesn't seem to apply to what I'm doing...

Any suggestions would be great!

Does a file of the same name already exist? Does the logged in user have rights to the destination folder?

I've never used the FileSystemObject because it requires a reference to the Scripting runtime library and many network admins don't allow it on systems. If you need an overwrite parameter, you can use the CopyFileA Win32 api call. Declare it at the top of a standard module:

Private Declare Function CopyFileA Lib "kernel32" (ByVal ExistingFileName As String, _
  ByVal NewFileName As String, ByVal FailIfExists As Long) As Long

Then use a wrapper function that provides an optional OverWrite parameter. If you don't provide a parameter or set it True, the procedure will not overwrite the target and the function returns False indicating that the copy did not succeed. Pass the function a False in the NoOverWrite parameter and it will simply overwrite any file in the target location if it exists and simply copy if it doesn't. The function returns True any time it succeeds in copying to the target destination.

Public Function Copy(FileSrc As String, FileDst As String, Optional NoOverWrite As Boolean = True) As Boolean
    On Error GoTo ErrorHandler

    Copy = CopyFileA(FileSrc, FileDst, NoOverWrite) = 1

ExitRoutine:
    On Error Resume Next
    Exit Function
ErrorHandler:
    With Err
        Select Case .Number
            Case Else
                MsgBox .Number & vbCrLf & .Description, vbInformation, "Error - Copy"
        End Select
    End With
    'Resume 0
    Resume ExitRoutine
End Function

Personally, I would dump any reference to the Scripting runtime. You can get the list of files with the 'xls' extension calling the Dir function in a loop.

Ciao
Jürgen Welz
Edmonton AB Canada
[email protected]

Have you tried using FileCopy rather than FSO to copy the file?
ie:
Also, what is the "destination" variable, I'm assuming it's not just a folder name


I am a loud man with a very large hat. This means I am in charge

Thanks very much for your suggestions!

I got the same error using FileCopy, but here is what I ended up using:

ActiveWorkbook.SaveCopyAs FileString