CODEWITHSUNDEEP
vbaexcel
NPoorbaugh
NPoorbaugh

Reputation: 15

How can I keep a VBA script referencing file names running if it cannot locate one?

I am running the code below as a means to change large batches of image names.

However, it errors out whenever an image name in the file is not located (could be due to duplicates, an accidental removal or fat-finger-disease).

Is there a way to make the script keep running when this happens or to make it highlight the culprit so it can be removed (or both haha)?

Thanks in advance!! And thanks for the original code, got it from here :)

Code is below.

Sub RenameImages()
'
' RenameImages Macro
'
' Keyboard Shortcut: Ctrl+Shift+I
'
Dim Source As Range
Dim OldFile As String
Dim NewFile As String

Set Source = Cells(1, 1).CurrentRegion

For Row = 1 To Source.Rows.Count
    OldFile = ActiveSheet.Cells(Row, 1)
    NewFile = ActiveSheet.Cells(Row, 2)

    ' rename files
    Name OldFile As NewFile

Next
End Sub

Upvotes: 0

Views: 137

Answers (2)

dan
dan

Reputation: 3519

You have multiple ways to handle this, you could use the FileExists() function like this:

Dim fso As Object
Dim MyPath As String

Set fso = CreateObject("Scripting.FileSystemObject")

MyPath = "C:\myFile.txt"

If fso.FileExists(MyPath) = False Then
    MsgBox MyPath & " doesn't exist"
Else
    'Rename your file
End If

The other way to handle this would be to use an ErrorHandler:

Sub RenameImages()
On Error Goto ErrorHandling
    '
    ' RenameImages Macro
    '
    ' Keyboard Shortcut: Ctrl+Shift+I
    '
    Dim Source As Range
    Dim OldFile As String
    Dim NewFile As String

    Set Source = Cells(1, 1).CurrentRegion

    For Row = 1 To Source.Rows.Count
        OldFile = ActiveSheet.Cells(Row, 1)
        NewFile = ActiveSheet.Cells(Row, 2)

        ' rename files
        Name OldFile As NewFile
    Next
Exit Sub

ErrorHandling:
    Debug.Print "File doesn't exist: " & OldFile
    Resume Next
End Sub

Upvotes: 1

iDevlop
iDevlop

Reputation: 25262

Before the FOR Row = 1... line, a line On error resume next should do the trick.
On the line after the Next, turn this off with On error goto 0

Upvotes: 0

Related Questions