Ignore error 58 when renaming files

Question

I've got a small Access program that looks up files names from a query ("qryImagesToRename"), goes through a loop and renames them. However, if an image already exists with the same name Access wants to rename it to, I receive

error 58 - File Already Exists

How do I ignore this error and continue with the loop? This my code:

Private Sub Command10_Click()
On Error GoTo Command10_Click_Error

Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim strSQL As String

DoCmd.Hourglass True


 Set db = CurrentDb

 strSQL = "select * from qryImagesToRename"

 Set rs = db.OpenRecordset(strSQL)

 Do While Not rs.EOF

    Name rs.Fields("From").Value As rs.Fields("To").Value

    rs.MoveNext
 Loop

DoCmd.Hourglass False

MsgBox "All matching files renamed"

 On Error GoTo 0
  Exit Sub

Command10_Click_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Command10_Click of VBA Document Form_frmRename - Please take a screenshot and email xxxxxx@xxxxxxx.com"
End Sub

Don't ignore it, that is horrible practice. Instead, use the `Dir(filename)` function to check if it exists first, and if it exists, modify the new name in some way. — braX, Dec 22 '17 at 12:12

score 4 · Accepted Answer · answered Dec 22 '17 at 11:48

If you are certain that you can ignore the error then you could use On Error Resume Next to ignore it and continue processing. Ensure that you add On Error Goto 0 as soon as you can, to reinstate the normal error processing.

On Error Resume Next

Do While Not rs.EOF

    Name rs.Fields("From").Value As rs.Fields("To").Value

    rs.MoveNext
 Loop

 On Error GoTo 0

This is most often a poor practice, but can be used if there is certainty about behaviour.

A better practice would be to check if the file already exists using Dir (or FileSystemObject) and skip it. Discussed here

score 0 · Answer 2 · answered Dec 25 '17 at 05:12

Two particular solutions come to mind. The first, is in-line logic to check for the existing file, and skip that item, and the second is to put a case statement in the error handler. I have outlined the code below to have both options. I hope it helps.

Private Sub Command10_Click()
    On Error GoTo Command10_Click_Error
    Dim rs As DAO.Recordset
    Dim db As DAO.Database
    Dim strSQL As String
    Dim fso as New FileSystemObject

    DoCmd.Hourglass True

    Set db = CurrentDb
    strSQL = "select * from qryImagesToRename"
    Set rs = db.OpenRecordset(strSQL)
    Do While Not rs.EOF      'if you want to use the logic inline, use the check below
        If fso.fileexists(rs.Fields("To").value) = false Then
            Name rs.Fields("From").Value As rs.Fields("To").Value
        End If
    NextRecord:              'if you want to use the goto statement, use this
    rs.MoveNext
    Loop

    DoCmd.Hourglass False
    MsgBox "All matching files renamed"

    On Error GoTo 0
    Exit Sub
Command10_Click_Error:
    Select case Err.number
        Case 58
            GoTo NextRecord
        Case Else
            MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure     Command10_Click of VBA Document Form_frmRename - Please take a screenshot and email xxxxxx@xxxxxxx.com"
    End select
End Sub

Ignore error 58 when renaming files

2 Answers2