Avatar of Dale James
Dale James
 asked on

Excel VBA: Runtime Error 70 'Permission Denied' for FSO.MoveFile

Hello Team


Until recently the following code has been executing without any issues.


Sub MoveFiles()




 Dim FSO As Object
    Dim FromPath As String
    Dim ToPath As String
    Dim FileExt As String
    Dim fNames As String
    
    
    
Application.DisplayAlerts = False

 ActiveWorkbook.SaveAs Filename:= _
        "K:\Bus\Plan\Daily MailBox Count\MailCount Peer.xlsm", FileFormat:= _
        xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

Application.DisplayAlerts = True

    FromPath = "K:\Bus\Plan\Daily MailBox Count\"
    ToPath = "K:\Bus\Plan\Daily MailBox Count\Current Year Mail Count Archive\"

    FileExt = "*Mailbox Count Report*"

    fNames = Dir(FromPath & FileExt)
    If Len(fNames) = 0 Then
        MsgBox "No files in " & FromPath
        Exit Sub
    End If

    Set FSO = CreateObject("scripting.filesystemobject")

    FSO.MoveFile Source:=FromPath & FileExt, Destination:=ToPath
    
   ' MsgBox "You can find the files from " & FromPath & " in " & ToPath


End Sub

Open in new window


 Now for some reason the following code line produces a runtime error 70 'Permission Denied'


 FSO.MoveFile Source:=FromPath & FileExt, Destination:=ToPath

Open in new window

The referenced path and folders are located on a network and I continue to have full permission to access and work within the referenced path and folders.


What I have discovered is that even though when the FSO.MoveFile code line is producing the runtime error, before it displays the error the file is actually moved to the destination path and folder.


Can anyone please shed some light on why the the file would be successfully moved but then be followed up with a 'Permission Denied' runtime error ?


Thank you in advance.

Sincerely

Dale

VBANetworkingMicrosoft ExcelMicrosoft Office

Avatar of undefined
Last Comment
Robert Berke

8/22/2022 - Mon
ste5an

The first question is who is raising that error?

You need to clean up your method and add proper error handling. Especially as you've multiple sources for that error, namely the Dir() and FSO. E.g.

Sub MoveFiles()

  Const SOURCE_PATH As String = "K:\Bus\Plan\Daily MailBox Count\"
  Const DESTINATION_PATH As String = SOURCE_PATH & "\Current Year Mail Count Archive\"
  Const FILE_NAME As String = SOURCE_PATH & "\MailCount Peer.xlsm"
  Const FILE_PATTERN As String = "*Mailbox Count Report*"

  On Local Error GoTo LocalError

  Dim FSO As Object

10  Application.DisplayAlerts = False
11  ActiveWorkbook.SaveAs FILE_NAME:= FILE_NAME, FileFormat:= xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
12  Application.DisplayAlerts = True

20  If Len(Dir(SOURCE_PATH & FILE_PATTERN)) = 0 Then
21    MsgBox "No files in " & SOURCE_PATH
22    Exit Sub
23  End If

30  Set FSO = CreateObject("Scripting.FileSystemObject")
31  FSO.MoveFile Source:=SOURCE_PATH & FILE_PATTERN, Destination:=DESTINATION_PATH
32  Exit Sub

LocalError:
  MsgBox _
    "Error: " & Err.Description & vbCrLf & _
    "Line: " & Err.Line, _
    vbExclamation

End Sub

Open in new window

I would expect the Dir() to fail due to the use of a mapped drive and some saved credential issues with a delayed network reaction.
Robert Berke

hold on a minute
Robert Berke

Your question really helped me out. I had completely forgotten that movefile allowed wildcards, so thanks for the reminder.
 
Anyway, your code should work.
Your code used to work without error.
Now your code continues  to work but now throws an unexplained error 70 'Permission Denied'
 
Perhaps most of the wildcard files were moved, but one files was left behind? And maybe that is a hidden system file with a tilde like " ~$ ssss.xlsm"
 
Try replacing the dir command with these 4 lines,
 
fNames = Dir(strFolder & "*~*", vbHidden + vbSystem)
static cntr as long
cntr = cntr + 1
debug.print  "cntr= " & cntr & " first file= <" & fnames & ">"

Open in new window

 
 
also, add the following after fso.movefile
    fso.MoveFile Source:=FromPath & fileext, Destination:=ToPath '
    fNames = Dir(FromPath & fileext, vbHidden + vbSystem)
    If fNames = "" Then
        MsgBox "Case 1: All files were moved, and err = " & Err
    Else
        MsgBox "Case 2: Some files were not moved, so err = " & Err
    End If

Open in new window

 
If  "Case 1" shows error 70 then I am perplexed.
 
 
When I hit perplexing  problems  I usually have two approaches.
#1 If I am in a hurry and there is an easy workaround, I take the workaround and move on.
#2 If I have time, I try to find the root cause of the problem.
 
Dale, if you just want a workaround, you could do the following.
 
        On Error Resume Next
        fso.MoveFile Source:=FromPath & fileext, Destination:=ToPath
        if err <> 70 and err <> 0 then
            msgbox err & error
         end if
      On Error GoTo 0

Open in new window

 
But, if you want the root cause I would try to ruthlessly simplify the code as much as possible until you have the very simplest code that replicates the problem.
 
 
For example I expect the following might replicate your error
 
Sub MoveBugs()
Const FromPath = "c:\Bus\Plan\Daily MailBox Count\"
Const ToPath = "c:\Bus\Plan\Daily MailBox Count\Current Year Mail Count Archive\"
Const fileext = "*Mailbox Count Report*"
Dim fNames
  
Dim fso As Object
    Set fso = CreateObject("scripting.filesystemobject")
    
    fso.MoveFile Source:=FromPath & fileext, Destination:=ToPath '
    fNames = Dir(FromPath & fileext, vbHidden + vbSystem)
    If fNames = "" Then
        MsgBox "All files were moved, and err = " & Err
    Else
        MsgBox "Some files were not moved, so err = " & Err
    End If
 
End Sub
 

Open in new window

 
 
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Dale James

ASKER
Hello Ste5an and rberke

Thank you very much for your quick responses and for the advice provided.

rberke:  Thank you for your code examples and explanations.  I will added these code examples to my module and feedback to you later today.

Thanks again.
Dale


Dale James

ASKER
Hello rberke

I have applied all the code suggestions provided but as with the original code, runtime error 70 'Permission Denied' is still being generated but with the file being also being moved from the source path to the destination path. There is an exception with the LocalError error handler is applied, this generates the following runtime error:

Runtime Error 438
Object doesn't support this property or object.

Of the code provided, the quick workaround is the only code that is allowing fso.MoveFile to execute without the runtime error being displayed and halting the flow.

I am, as you say, 'perplexed' with this sudden error display particularly with the move action actually occurring at the same time.  And as initially stated, I do not have any issues accessing or working within the source path and destination path folders.

The only thing that I can think of is that the dreaded MS patch updates occurred just before I began to experience this issue and like many times before,  previous patch updates have been an underlying cause.

Not sure what other options are available to address this matter as you have been very thorough with what you have provided.




ASKER CERTIFIED SOLUTION
Robert Berke

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Dale James

ASKER
Hello rberke

Please accept my apology for this delayed response as I was not aware that any further posts had been submitted to this question until a few moments ago.

Thank you for posting your code suggestion, I will apply this coding later this morning and provide feedback to you ASAP.

Thank you once again.
Sincerely

Dale
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Robert Berke

Please close this question
Dale James

ASKER
Hello rberke

There seems to have been a issue with my last comment...just noticed that it hasn't been published.
In the last comment I attempted to publish,  I was thanking you for the code you provided and to confirm that it worked as it notified me of a couple of files that have been incorrectly posted to the network folder and and once removed enabled the FSO.movefile to work correctly.

Thank you for your all your assistance with this issue...you have taught some important elements.

Sincerely

Dale


Robert Berke

I am glad things worked out and thanks for the testimonial

Bob

Your help has saved me hundreds of hours of internet surfing.
fblack61