• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Where should I put the "if error" code to skip error 58 "File already exists"?

The following code intends to rename a file name based on certain prefix of the file, however, when another file with the intended name already exist in the folder, the macro will stop.

I tried to insert "if error" command to skip to the next file but it doesn't work. Can someone help? Thanks!

Code:
Sub ReNameFiles_A_(ByVal myDir As String)
    Dim sfo As Object, MyFile As Object, myFolder As Object, temp As String, newName, i As Long
    Set sfo = CreateObject("Scripting.FileSystemObject")
    For Each MyFile In sfo.GetFolder(myDir).Files
        If MyFile.Name Like "*NCIR0*_A_*" Then
            For i = 4 To 5
                newName = ExecuteExcel4Macro("right(substitute('" & myDir & "\[" & MyFile.Name & "]sheet1'!r" & i & "c1,""/"",""""),8)")
                If (Not IsError(newName)) Then
                    If (newName <> vbNullString) * (IsNumeric(newName)) Then Exit For
                End If
            Next
            If IsError(newName) Then newName = GetDate(myDir & "\" & MyFile.Name)
            If newName <> "" Then
                newName = Replace(newName, "/", "")
                temp = Left$(sfo.GetBaseName(MyFile.Name), 10) & newName & _
                                "." & sfo.GetExtensionName(MyFile.Name)
>>>>> Error stop here     Name myDir & "\" & MyFile.Name As myDir & "\" & temp   
            End If
            newName = ""
        End If
    Next
    For Each myFolder In sfo.GetFolder(myDir).subfolders
        ReNameFiles_A_ myFolder.Path
    Next
End Sub
 
Hi ,

The easiest way to work around the problem of such harmless run time errors is to have the following statement :

On Error Resume Next

just before the following line of code in your procedure :

For Each MyFile In sfo.GetFolder(myDir).Files

Narayan
 
Hi ,

The easiest way to work around the problem of such harmless run time errors is to have the following statement :

On Error Resume Next

just before the following line of code in your procedure :

For Each MyFile In sfo.GetFolder(myDir).Files

Narayan

It works! Thank you so much!
 
Hi, Winston618!

Please do take care of resetting "On Error Resume Next" sentences with "On Error Goto 0" since any other error (not only that which you want to trap) that happens after executing that statement will be omitted and code flow may go thru unwanted instructions.

Remember that is part of the best practices to trap any probable error with constructions like:
Code:
Option Explicit

Sub X()
    '... some code
    On Error GoTo X_Error_Handler
    '... some code
    GoTo No_Error
aaa_Resume:
    '... some code
    GoTo aaa_GoOn
bbb_Resume:
    '... some code
    GoTo bbb_GoOn
aaa_GoOn:
    '... some code (or not)
bbb_GoOn:
    '... some code (or not)
No_Error:
    '... some code
    Exit Sub
X_Error_Handler:
    Select Case Err.Number
        Case aaa
            ' trap error for aaa error code
            '... some code
            Err.Clear
            On Error GoTo 0
            Resume aaa_Resume
        Case bbb
            ' trab error for bbb error code
            '... some code
            Err.Clear
            On Error GoTo 0
            Resume aaa_Resume
        '... more specific cases
        Case Else
            ' untrapped/untrappable error
            MsgBox "Tell user about it & cancel processing"
    End Select
End Sub
So unless at short snippets or specific controlled codes, never leave a "On Error Resume Next" instruction open and enabled.

Regards!
 
Back
Top