View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
Ron Rosenfeld[_2_] Ron Rosenfeld[_2_] is offline
external usenet poster
 
Posts: 1,045
Default Error Trapping Problem

Windows 7 Prof 64-bit
Office 2007

I am trying to list all files on my computer that meet a certain
criteria. I will later be deleting them.

I am using VBA from Excel.

I have been running into issues with the W7 security scheme and will
occasionally get a "Run-time error '70': Permission Denied

I don't seem to be able to always "trap" this with an On Error
statement.

Sometimes the trap works, and sometimes it doesn't. In the code
below, it has been the case that at least one "error message" will be
written by the line at PermissionDenied:, but I will subsequently get
the run-time error at the line marked.

What am I doing incorrectly?

Here is the Sub that crashes:

========================
Sub ShowSubFolders(sFldr As String)
Dim subFldrs As Folders, subFldr As Folder
Set subFldrs = FSO.GetFolder(sFldr).subFolders
For Each subFldr In subFldrs
If Not subFldr.Attributes And Alias Then
Application.StatusBar = "Processing folder: " & subFldr
If Not subFldr.Attributes And System Then
On Error GoTo PermissionDenied
Set FLS = subFldr.Files
ERROR HERE -- For Each F In FLS
If re.Test(F.Name) = True Then
Cells(i, 1).Value = F.Path
i = i + 1
End If
Next F
ShowSubFolders (subFldr)
End If
End If
PermissionDenied: If Error < "" Then Debug.Print Error, subFldr
On Error GoTo 0
Next subFldr
End Sub
======================

I would prefer not to run Excel as administrator to accomplish this
task.

Thanks.