1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. 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...

  3. 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"?

Discussion in 'VBA Macros' started by Winston618, Aug 12, 2017.

  1. Winston618

    Winston618 New Member

    Messages:
    26
    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 (vb):

    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
     
  2. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,069
    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
  3. Winston618

    Winston618 New Member

    Messages:
    26
    It works! Thank you so much!
  4. SirJB7

    SirJB7 Excel R┼Źnin

    Messages:
    8,890
    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 (vb):
    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!

Share This Page