Error handling that captures name and path details in a log without ending the macro

[marts2003]

I've developed a macro that crawls through a directory (every folder, sub-folder, sub-sub-folder etc), and deletes personal data from any Excel file that it finds - and this works well, so long as I ignore any errors along the way.

What I would really like is for some form of error handling that captures the name and path details of the Excel files that cause an error (because they can't be opened) and then outputs them to a log file, so that I can know how many there are - and can then visit, assess (and possibly fix) each file that's causing an error.

I'm new to this, so any suggestions will be gratefully received!

 

[Joe4]

Welcome to the Board!

If you post your code, we may be able to help you add error handling to your code to do what you want.

 

[marts2003]

Hi Joe,

Thanks for responding!

I won't post the entire macro (hope that's ok) - but I believe this is the relevant section:


Sub All_You_Need_To_Do_Is_Run_This_Macro()


Dim colFiles As New Collection


'Important - Need to update the directory address


RecursiveDir colFiles, "C:\Test Folder", "*.xls*", True
Dim vFile As Variant
Dim wbkOpen As Workbook


For Each vFile In colFiles
On Error Resume Next
Set wkbOpen = Workbooks.Open(filename:=vFile, UpdateLinks:=0)

Call This_Focuses_On_Workbooks_And_Worksheets
wkbOpen.Close savechanges:=True
Next vFile


End Sub


Without the "On Error Resume Next", the code hangs at "Set wkbOpen = Workbooks.Open" as soon as it encounters an Excel file it can't immediately open.

Is this sufficient info?

Many thanks!

 

[Joe4]

Well, I cannot really do a good test without all the code, but here is some untested code that might help.
They key is I added a sheet to the file with the macro named "Errors", where it will write the name of the error files.

Sub All_You_Need_To_Do_Is_Run_This_Macro()

Dim vFile As Variant
Dim wbkOpen As Workbook
Dim colFiles As New Collection
Dim numErrs As Long

'Important - Need to update the directory address

    RecursiveDir colFiles, "C:\Test Folder", "*.xls*", True
    
    On Error GoTo err_chk
    For Each vFile In colFiles
        On Error GoTo err_chk
        Set wkbOpen = Workbooks.Open(Filename:=vFile, UpdateLinks:=0)
        
        Call This_Focuses_On_Workbooks_And_Worksheets
        wkbOpen.Close savechanges:=True
    Next vFile
    On Error GoTo 0

Exit Sub


err_chk:
    numErrs = numErrs + 1
    Sheets("Errors").Cells(numErrs, "A") = vFile
    Err.Clear
    Resume Next

End Sub

 

[marts2003]

Thanks Joe,

I appreciate the suggestion and I'll definitely give it a go (probably tomorrow), and will let you know.

Just so you know, I'm a bit nervous of posting the entire code - mainly because it has the potential to do a lot of damage in the wrong hands (as it's currently able to crawl through a whole directory deleting important chunks out of any Excel file it happens upon) - and presumably it could easily be modified to do far worse than that!

 

[Joe4]

Just so you know, I'm a bit nervous of posting the entire code - mainly because it has the potential to do a lot of damage in the wrong hands (as it's currently able to crawl through a whole directory deleting important chunks out of any Excel file it happens upon) - and presumably it could easily be modified to do far worse than that!

Understood. It just means that you may have to do some manipulation to the code I provided instead of using it straight up as I have written it.

 

[marts2003]

Hi again,

Gave this a go - and here's what I got: Run-time error '9': Subscript out of range

Clicking on Debug takes me to the following (highlighted in yellow): Sheets("Errors").Cells(numErrs, "A") = vFile


I'm fairly sure it's fallen over when it's reached the first 'corrupted' file, as it stops near the start of the run - but it doesn't seem to have added a sheet to the macro.

If I try to open the file itself, I get the following message: Excel found unreadable content in 'file.xls'. Do you want to recover the contents of this workbook? If you trust the source of this workbook, click Yes.

And, yes, it's completely fine to click Yes.

I know it's a lot to ask - but it's not obvious (to me) what to try next. Do you have any suggestions, please?

 

[Joe4]

I am trying to set up a simple test, to test out your code, but it does not recognize "RecursiveDir".
What is this? Is this some User Defined Function you created, or is it part of a certain VBA Library?

 

[Fluff]

@Joe4
Looks like it might be a UDF
http://www.ammara.com/access_image_faq/recursive_folder_search.html

 

[Joe4]

See if Rory's response here might help: https://windowssecrets.com/forums/s...l-Workbooks-Open-fails-when-file-needs-repair