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!

 

[marts2003]

Hi again,

I'm not sure of the formal definition (sorry), and it could be a udf - but it does describe itself as a "public function". Obviously I'm not the creator, and am not trying to take any credit for it - but I have managed to use it in what's seems to be a very powerful way.

I've had a look at Rory's response ( Workbooks.Open(filename:=PATH AND FILENAME,corruptload:=xlrepairfile) ) and had a go with the following adjustment to the code:

Set wkbOpen = Workbooks.Open(Filename:=vFile, corruptload:=xlRepairFile, UpdateLinks:=0)

Unfortunately I get exactly the same error messages as before

So I guess I need to share some more of the code... which I've done below. Sorry about the bits I've replaced with $$$ (I still don't wish to publish this in its entirety), but hopefully I've left enough lines in for you to see what the recursive part is up to?


Public Function RecursiveDir(colFiles As Collection, _
strFolder As String, _
strFileSpec As String, _
bIncludeSubfolders As Boolean)


Dim strTemp As String
Dim colFolders As New Collection
Dim vFolderName As Variant


'Add files in strFolder matching strFileSpec to colFiles
$$$
strTemp = Dir
Loop


If bIncludeSubfolders Then
'Fill colFolders with list of subdirectories of strFolder
strTemp = Dir(strFolder, vbDirectory)
$$$
End If
End If
strTemp = Dir
Loop


'Call RecursiveDir for each subfolder in colFolders
For Each vFolderName In colFolders
Call RecursiveDir(colFiles, strFolder & vFolderName, strFileSpec, True)
Next vFolderName
End If


End Function

 

[Joe4]

Unfortunately, I do not have a corrupt workbook that I can try this out on. So I cannot put the error debugging to the test (I thought that it would work).
And I don't know how you can tell if a workbook is corrupt without attempting to open it up.

The one thing you will need to account for, is if it cannot open it up, it needs to skip all code below it that would work on the opened workbook and then close it.
Perhaps you can use an IF or another GoTo statement to do that.

 

[marts2003]

Thanks for trying - I appreciate it.

The good news is that I found another post (876366) "Runtime 1004 error" by Hermones (who was trying to do something similar) which was answered by Domski, and I've been able to use a version of that to do what I wanted to do.

So I can now set it crawling through a directory, happily deleting out any personal data from the Excel files it finds (so that we can be legally compliant with the forthcoming GDPR regulations) - and I get a report of any Excel files that it can't open (so that I can inspect them too). That's pretty good isn't it? Especially when you consider that I'd never even used a macro before the start of this week...!

 

[Joe4]

Glad that you got it figured out.

If it is not too much trouble, could you post a link to the other thread that you found that helped you out? It might help others in the future who have the same issue and stumble across this thread.
Or, maybe post your code with the changes here.

 

[marts2003]

Here's where I found the error-handling approach that I needed to capture name and path details in a log without ending the macro:

www.mrexcel.com/forum/excel-questions/876366-runtime-1004-error.html

Hope this helps someone!