Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old April 9th, 2004, 02:04 PM
Registered User
 
Join Date: Apr 2004
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Permission Denied Error in VBA

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!

  #2 (permalink)  
Old April 9th, 2004, 03:50 PM
Authorized User
 
Join Date: Feb 2004
Location: Edmonton, Alberta, Canada.
Posts: 98
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
jwelz@hotmail.com
  #3 (permalink)  
Old April 12th, 2004, 03:36 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Melbourne, Vic, Australia.
Posts: 308
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Have you tried using FileCopy rather than FSO to copy the file?
ie:
Code:
For Each fil In fso.GetFolder(sourceFld).Files
            If UCase$(fso.GetExtensionName(fil)) = "XLS" Then
                    FileCopy fil, destination
            End IF
Next
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
  #4 (permalink)  
Old April 12th, 2004, 06:51 PM
Registered User
 
Join Date: Apr 2004
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Thanks very much for your suggestions!

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

ActiveWorkbook.SaveCopyAs FileString



Similar Threads
Thread Thread Starter Forum Replies Last Post
Getting a permission denied error in IE 7 disto Javascript 1 September 20th, 2007 08:26 AM
Error no 70 Permission Denied rupakroy5 Pro VB 6 5 October 7th, 2006 01:26 AM
Permission Denied Error interrupt Javascript How-To 6 October 15th, 2004 05:26 AM
IE - Runtime error - Permission Denied bstone VB How-To 2 September 11th, 2004 02:21 AM
Error 70: Permission denied junglem@n VB How-To 2 April 3rd, 2004 03:42 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.