multiple err handling

[remeng]

Hi All;

I am running into a situation where I get a runtime 53 error that I am not able to handle correctly. The error occurs on the second occurrence of an error in the DO UNTIL loop when processing the ted.txt to TED FIVE.txt file name conversion. I expect the error to occur, but it should jump to the Skip_record: section of code.

I think it is occurring because it isn't clearing the first error and resetting the error handling process on the second loop.

I've tried to add / remove the following which didn't resolve it.

I've tried:

On Error GoTo -1
On error GoTo 0
err.clear
err.raise



Macro Name = change_file_names ()

The macro general order of operation:

worksheet Menu

- User selects the root folder pathway where files to be renamed are located
- Select if sub folders should be check if the file is not found in the root folder
- Click on the File Name List icon to go to the File Names worksheet

worksheet File Names

- Enter the list of original file names into the Original Names column
- Enter the corresponding new file names into the Corrected Names column
- Go to the Menu worksheet and click the RUN FILE NAME CHANGE button (button calls Sub change_file_names macro)

Code where error 53 occurs

    ' Changes the file name from the origional to the new // corrected name

    Name pathway & origional_name_value As pathway & corrected_name_value

Full Code:

Sub change_file_names()


'root folder pathway

pathway = ThisWorkbook.Sheets("Menu").folder_pathway_field.Value & "\"

'Index file_name_table

origional_name_index = ThisWorkbook.Sheets("File Names").ListObjects("file_name_table").ListColumns("Original Name").Index
corrected_name_index = ThisWorkbook.Sheets("File Names").ListObjects("file_name_table").ListColumns("Corrected Name").Index
change_status_index = ThisWorkbook.Sheets("File Names").ListObjects("file_name_table").ListColumns("Change Status").Index


'clears the change status column values


ThisWorkbook.Sheets("File Names").ListObjects("file_name_table").ListColumns(change_status_index).DataBodyRange.ClearContents

ThisWorkbook.Sheets("File Names").ListObjects("file_name_table").ListColumns(change_status_index).DataBodyRange.Value = "Not Changed"


'Count the total number of records in the file_name_table

file_name_total_count = ThisWorkbook.Sheets("File Names").Range("file_name_table").ListObject.ListRows.Count

current_record_row = 1
err_count = 0

start_change:

Do Until current_record_row = file_name_total_count + 1

    'record values
    
    origional_name_value = ThisWorkbook.Sheets("File Names").ListObjects("file_name_table").DataBodyRange(current_record_row, origional_name_index).Value
    corrected_name_value = ThisWorkbook.Sheets("File Names").ListObjects("file_name_table").DataBodyRange(current_record_row, corrected_name_index).Value
    
    
    
'-------------------------- change file name from origional_name_value to corrected_name_value
    

On Error GoTo check_sub_folder



    ' Changes the file name from the origional to the new // corrected name

    Name pathway & origional_name_value As pathway & corrected_name_value
    

    'record file name change satus
    
    
    ThisWorkbook.Sheets("File Names").ListObjects("file_name_table").DataBodyRange(current_record_row, change_status_index).Value = "Changed"
   
    
    GoTo next_record
    

check_sub_folder:

    On Error GoTo -1

    On Error GoTo Skip_record
    
    If ThisWorkbook.Sheets("Menu").sub_file_name_verification_check_box.Value = True Then

    'first two characters of origional file name to use for sub-folder find.  Sets to Upper Case for folder name lookup
    
    first_two_characters = UCase(Left(origional_name_value, 2))


    sub_folder_path = pathway & "\" & first_two_characters & "\"


    Name sub_folder_path & origional_name_value As sub_folder_path & corrected_name_value
    
    
    'record file name change satus
    
    
    ThisWorkbook.Sheets("File Names").ListObjects("file_name_table").DataBodyRange(current_record_row, change_status_index).Value = "Changed"
    
    
    GoTo next_record
    

Else
    
Skip_record:
    
    err_count = err_count + 1
    
    ThisWorkbook.Sheets("File Names").ListObjects("file_name_table").DataBodyRange(current_record_row, change_status_index).Value = "Error"


End If


next_record:
    
    
    
    'Sets current_record_row to the next row

    current_record_row = current_record_row + 1
    



Loop




GoTo end_macro

change_status_to_error:
    
        ThisWorkbook.Sheets("File Names").ListObjects("file_name_table").DataBodyRange(current_record_row, change_status_index).Value = "Error"
        
        If MsgBox("Error on record: " & current_record_row & " - " & origional_name_value & Chr(10) & Chr(10) & "Do you want to go to the next record?", vbCritical + vbYesNo, "ERROR: FILE NAME CHANGE") = vbYes Then
        
        
        GoTo start_change
    
    
        Else
        
           
    
        End If


end_macro:



End Sub

Here is the zip file with the test files and folders.

Link



Thanks for the help!

Mike

 

[Andrzejek]

I get a runtime 53 error" - is it File not found (Error 53)?
If so, you are trying to rename the file that does not exist (any more?). Maybe you already renamed that file?
I would check:

...
Debug.Print "Org file: " & pathway & origional_name_value
Debug.Print "New file: " & pathway & corrected_name_value
Name pathway & origional_name_value As pathway & corrected_name_value
...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[combo]

It seems that you have not cleared previous error handling:

[pre]On Error GoTo -1 ' add to reset error and set new On Error
On Error GoTo check_sub_folder[/pre]

An article about error handling here.

combo

 

[Andrzejek]

Without going too deep into your code, I would have a simple structure of my error handling.
Something like this:

Option Explicit[green]
'...[/green]
Sub change_file_names()[blue]
On Error GoTo MyErrHandlr[/blue]
[green]'root folder pathway[/green]
pathway = ThisWorkbook.Sheets("Menu").folder_pathway_field.Value & "\"[green]
'...[/green]
Exit Sub
[blue]
MyErrHandlr:[/blue]
Select Case Err.Number
    Case 53   [green]'File not found
        'Handle this error and keep going[/green]
        Resume Next
    Case 52   [green]'Bad file name
        'Fix bad file name and[/green]
        Resume   [green]'try again[/green]
    Case Else[green]
        'Deal with all other errors here[/green]
        
End Select

End Sub

But, that's me...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[strongm]

[tt]On Error GoTo -1[/tt] Frankly, don't do this. It has never ended well for me.

[tt]On Error GoTo Skip_record[/tt] - And do not try to put a new error handler into an error handler. Yes, some documentation will certainly suggest that On Error Goto -1 makes this a good idea. Frankly, I disagree. Things will not go the way you expect ...

 

[mintjulep]

On Error GoTo -1 doesn't handle the error.

It tries to convince VBA that the error didn't really happen. But it did, so who knows what's going to happen next.

Follow Andy's advice.

 

[remeng]

It turns out to be that my error clearing script just was in the wrong area in the loop. I moved it and it didn't have any issues afterwards.