Error Trapping Problem

[Ron Rosenfeld]

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.

 

[GS]

Ron Rosenfeld used his keyboard to write :

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.

Hi Ron,
I assume you're running without an 'Option Explicit' statement OR you
have dimmed the other vars at the module level or higher, or in a
class. Not that it matters, just making note of it<g>. There's too much
missing to test this properly, so here's what I did:

Sub ShowSubFolders(sFldr As String)
Dim subFldrs As Object, subFldr As Object, F As Variant
Dim FSO As Object, FLS As Object
Set FSO = CreateObject("Scripting.FileSystemObject")
Set subFldrs = FSO.GetFolder(sFldr).subFolders
For Each subFldr In subFldrs
' If Not subFldr.Attributes And Alias Then
If Not subFldr.Attributes Then
Application.StatusBar = "Processing folder: " & subFldr
' If Not subFldr.Attributes And System Then
If Not subFldr.Attributes Then
On Error GoTo PermissionDenied
Set FLS = subFldr.Files
Dim i As Integer
For Each F In FLS
' If re.Test(F.Name) = True Then
Cells(i + 1, 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

This work nicely on XL12 XP SP3, so I tried it on Win7x64 XL12. Here's
what I got passing "C:\":

C:\MSOCache Run-time error '70': Permission denied
C:\Perflogs Run-time error '70': Permission denied
C:\ProgramData\Desktop Run-time error '70': Permission denied
C:\ProgramData\Documents Run-time error '70': Permission denied
C:\Recovery Run-time error '70': Permission denied

All others listed without error. Not sure what your 'Alias' and
'System' vars are.

HTH

 

[norie]

Why not just use Dir to list the files?

Might seem archaic but you never know it might work, a lot of people
are using it know that FileSearch is gone.

 

[Ron Rosenfeld]

Ron Rosenfeld used his keyboard to write :

Hi Ron,
I assume you're running without an 'Option Explicit' statement OR you
have dimmed the other vars at the module level or higher, or in a
class. Not that it matters, just making note of it<g>. There's too much
missing to test this properly, so here's what I did:

Sub ShowSubFolders(sFldr As String)
Dim subFldrs As Object, subFldr As Object, F As Variant
Dim FSO As Object, FLS As Object
Set FSO = CreateObject("Scripting.FileSystemObject")
Set subFldrs = FSO.GetFolder(sFldr).subFolders
For Each subFldr In subFldrs
' If Not subFldr.Attributes And Alias Then
If Not subFldr.Attributes Then
Application.StatusBar = "Processing folder: " & subFldr
' If Not subFldr.Attributes And System Then
If Not subFldr.Attributes Then
On Error GoTo PermissionDenied
Set FLS = subFldr.Files
Dim i As Integer
For Each F In FLS
' If re.Test(F.Name) = True Then
Cells(i + 1, 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

This work nicely on XL12 XP SP3, so I tried it on Win7x64 XL12. Here's
what I got passing "C:\":

C:\MSOCache Run-time error '70': Permission denied
C:\Perflogs Run-time error '70': Permission denied
C:\ProgramData\Desktop Run-time error '70': Permission denied
C:\ProgramData\Documents Run-time error '70': Permission denied
C:\Recovery Run-time error '70': Permission denied

All others listed without error. Not sure what your 'Alias' and
'System' vars are.

HTH

Thanks for your suggestions.

Do you mean, in what you wrote, that NOTHING was listed in the
Immediate Window? If so, then it seems the error routine was never
triggered on your machine, even though there clearly were run-time
errors.

I do use option explicit and the variables are Dim'd at a different
level. The "re" code is regular expression stuff to filter to the
appropriate name, and that works OK.

Alias and System are system variables that indicate those particular
attributes. System = 4 and Alias = 1024. Probably they aren't
available with late binding.

If subfldr is a Windows 7 junction, then the Alias attribute will be
set.

I was using those to filter out some of the folders that did not need
to be checked.

I, too, had no problem with similar code running under XP SP3. But
that OS did not have this UAC.

I changed my code to remove the "and System" and "and Alias"
comparisons as you did; and the only change was I got my failure at a
different point:

Immediate Window:
Permission denied C:\$Recycle.Bin\S-1-5-20
Permission denied C:\$Recycle.Bin

then got run-time error while processing

C:\MSOCache

I also changed my code to use late binding as you have above, and that
did not make any difference.

In my original code, the line in the immediate window:
Permission denied C:\MSOCache

and I then get the run-time error while processing

C:\PerfLogs

If I start Excel running "As Administrator", then the code seems to
complete; and there are no messages written in the Immediate window.

Here's the entire code:
=====================================
'Need to set reference to
' Microsoft VBScript Regular Expressions 5.5
' Microsoft Scripting Runtime
Option Explicit
Dim FSO As FileSystemObject
Dim Fldrs As Folders, Fld As Folder
Dim FLS As Files, F As File
Dim re As RegExp
Dim i As Long
Const sPat As String = "^.*\.[^.~]+~[^.~]+$"

Sub RemoveTildeFiles()
i = 1
Set FSO = New FileSystemObject
Set Fld = FSO.GetFolder("C:\")
Set FLS = Fld.Files
Cells.ClearContents
Set re = New RegExp
re.Pattern = sPat
ShowSubFolders (Fld)
Application.StatusBar = False
End Sub

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
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
===============================

If I copy/paste your code into my Excel, and add a calling sub:
====================
Sub ssf()
ShowSubFolders ("C:\")
End Sub
==================

I get a permission denied Run-time error '70' while processing
C:\Documents and Settings\All Users\Desktop

which is a junction.

If I uncomment the line that checks for that, and change Alias to
1024, I get the run-time permission denied error while processing
C:\MSOcache.

==========================

So, neither your code nor mine seems to work on my machine. And it
seems to have something to do with the VBA On Error routine not
trapping all of the Permission Denied errors.

However, and based in particular that you took the trouble to test
very similar code on your W7 machine, and it worked as designed, I
changed my error handling routine to put it outside the main body of
the Sub:

=========================
..
..
..
ShowSubFolders (subFldr)
End If
End If
NextSubFolder: Next subFldr
Exit Sub

PermissionDenied:
Debug.Print Error, subFldr
Resume NextSubFolder
End Sub
=============================

This seems to work OK, with a long list of files listed in the
Immediate Window (most from C:\Windows.old\Windows)

So I've got this routine working at this level.

But I wonder why the On Error routine seems to behave differently on
your machine than mine; and why having the On Error routine in the
body of the Sub seems to disable the error handler after the first or
second trap, whereas it does not when entered in the more usual
fashion.

But thank you very much for trying things on your machine. It was very
helpful.

 

[GS]

Thanks for your suggestions.

Do you mean, in what you wrote, that NOTHING was listed in the
Immediate Window? If so, then it seems the error routine was never
triggered on your machine, even though there clearly were run-time
errors.

While nothing was listed in the immediate window, stepping through the
code did trap errors that occured but your error handler dismissed
them:

[If Error < ""] Not sure what that means, exactly!

The folders I listed in my post were what was displayed in StatusBar.

I do use option explicit and the variables are Dim'd at a different
level. The "re" code is regular expression stuff to filter to the
appropriate name, and that works OK.

Alias and System are system variables that indicate those particular
attributes. System = 4 and Alias = 1024. Probably they aren't
available with late binding.

If subfldr is a Windows 7 junction, then the Alias attribute will be
set.

I was using those to filter out some of the folders that did not need
to be checked.

I, too, had no problem with similar code running under XP SP3. But
that OS did not have this UAC.

I changed my code to remove the "and System" and "and Alias"
comparisons as you did; and the only change was I got my failure at a
different point:

Immediate Window:
Permission denied C:\$Recycle.Bin\S-1-5-20
Permission denied C:\$Recycle.Bin

then got run-time error while processing

C:\MSOCache

I also changed my code to use late binding as you have above, and that
did not make any difference.

In my original code, the line in the immediate window:
Permission denied C:\MSOCache

and I then get the run-time error while processing

C:\PerfLogs

If I start Excel running "As Administrator", then the code seems to
complete; and there are no messages written in the Immediate window.

Here's the entire code:
=====================================
'Need to set reference to
' Microsoft VBScript Regular Expressions 5.5
' Microsoft Scripting Runtime
Option Explicit
Dim FSO As FileSystemObject
Dim Fldrs As Folders, Fld As Folder
Dim FLS As Files, F As File
Dim re As RegExp
Dim i As Long
Const sPat As String = "^.*\.[^.~]+~[^.~]+$"

Sub RemoveTildeFiles()
i = 1
Set FSO = New FileSystemObject
Set Fld = FSO.GetFolder("C:\")
Set FLS = Fld.Files
Cells.ClearContents
Set re = New RegExp
re.Pattern = sPat
ShowSubFolders (Fld)
Application.StatusBar = False
End Sub

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
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
===============================

If I copy/paste your code into my Excel, and add a calling sub:
====================
Sub ssf()
ShowSubFolders ("C:\")
End Sub
==================

I get a permission denied Run-time error '70' while processing
C:\Documents and Settings\All Users\Desktop

which is a junction.

If I uncomment the line that checks for that, and change Alias to
1024, I get the run-time permission denied error while processing
C:\MSOcache.

==========================

So, neither your code nor mine seems to work on my machine. And it
seems to have something to do with the VBA On Error routine not
trapping all of the Permission Denied errors.

In reviewing what I tested, I may have over stepped the last error,
which ended the proc prematurely. I did get a rather substantial
listing on the spreadsheet, but due to recursive activity the list did
not clear before the counter was re-initialized. This caused previous
entries to be overwritten. I wonder if the counter should be declared
Static so the list on the spreadsheet is contiguous and correctly
reflects the results in the same order as executed.

However, and based in particular that you took the trouble to test
very similar code on your W7 machine, and it worked as designed, I
changed my error handling routine to put it outside the main body of
the Sub:

=========================
.
.
.
ShowSubFolders (subFldr)
End If
End If
NextSubFolder: Next subFldr
Exit Sub

PermissionDenied:
Debug.Print Error, subFldr
Resume NextSubFolder
End Sub
=============================

This seems to work OK, with a long list of files listed in the
Immediate Window (most from C:\Windows.old\Windows)

So I've got this routine working at this level.

But I wonder why the On Error routine seems to behave differently on
your machine than mine;

This may not be the case as per my overstep mentioned.

and why having the On Error routine in the
body of the Sub seems to disable the error handler after the first or
second trap, whereas it does not when entered in the more usual
fashion.

But thank you very much for trying things on your machine. It was very
helpful.

No problem! I don't mind spending the time because I find this
interesting; ..I'm hoping to learn more about how to construct
recursive procs. Now that you've posted the entire code, I can re-test
this and report back.

 

[Ron Rosenfeld]

In reviewing what I tested, I may have over stepped the last error,
which ended the proc prematurely. I did get a rather substantial
listing on the spreadsheet, but due to recursive activity the list did
not clear before the counter was re-initialized. This caused previous
entries to be overwritten. I wonder if the counter should be declared
Static so the list on the spreadsheet is contiguous and correctly
reflects the results in the same order as executed.

This may not be the case as per my overstep mentioned.


No problem! I don't mind spending the time because I find this
interesting; ..I'm hoping to learn more about how to construct
recursive procs. Now that you've posted the entire code, I can re-test
this and report back.

With regard to the "overstepping" problem, I'm not sure exactly what
you mean. But you'll note in the full code that the calling Sub
initializes the row counter and also clears the worksheet.

If your routine ran to completion, using the original code, then it
is, indeed, behaving differently than mine.

The
If error = "" ...

line skips over writing the error entry if there is no error since, in
that configuration, the error handler will always be executed.

I've made a few more changes so the errors are written to the
worksheet (in column 3), instead of the immediate area.

Of course as written, the only non-error files that will get written
in column A will be those that match the regex pattern. That pattern
is looking for files whose file type extension as ben appended with a
tilde followed by some sequence of characters. e.g.:

abc.xls~ABC1234F9

These are files that seem to get left over if my Acronis file backup
process terminates abnormally.

I look forward to any insights you might have. In particular, I'd
like to be able to check the "Permission Denied" folders without
running as administrator; but that's not entirely necessary.

So far as the recursive process, it has always given me a headache
)

=============================================
'Need to set reference to
' Microsoft VBScript Regular Expressions 5.5
' Microsoft Scripting Runtime
Option Explicit
Dim FSO As FileSystemObject
Dim Fldrs As Folders, Fld As Folder
Dim FLS As Files, F As File
Dim re As RegExp
Dim i As Long, j As Long
Const sPat As String = "^.*\.[^.~]+~[^.~]+$"
Sub RemoveTildeFiles()
i = 1: j = 1
Set FSO = New FileSystemObject
Set Fld = FSO.GetFolder("C:\")
Set FLS = Fld.Files
Cells.ClearContents
Set re = New RegExp
re.Pattern = sPat
ShowSubFolders (Fld)
Application.StatusBar = False
End Sub
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
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
NextSubFolder: Next subFldr
Exit Sub

PermissionDenied:
Cells(j, 3).Value = Error & " " & subFldr
On Error GoTo 0
j = j + 1
Resume NextSubFolder
End Sub
=============================

 

[GS]

Ron,
I'd be happy to re-test this for you as I'm curious why we aren't
getting the same results. I suspect I did not run the code to
completion because I overstepped (Ctrl+F9) to the wrong place after the
error at C:\Recovery.

As for the UAC issue regarding Admin rights goes, are you suggesting
that your 'Acronis file backup' process has write access to these
folders? If not then why not skip over those folders entirely?

<FWIW>Re: placement of the error handler
I've only used error handling as explained/demonstrated in Rob Bovey's
books. This recent code you posted seems to adhere to that same
convention. Mind you, I've seen numerous other ways to do this but
haven't been interested in implementing anything other than what I've
learned from Rob's book topics on error handling.

<IMO>The error handler should be the last label in a proc, and
redirection should be done there either back to running code or to a
'normalexit:' label immediately above the error handler code.

I'll try to post the results of my test later today...

 

[Ron Rosenfeld]

Ron,
I'd be happy to re-test this for you as I'm curious why we aren't
getting the same results. I suspect I did not run the code to
completion because I overstepped (Ctrl+F9) to the wrong place after the
error at C:\Recovery.

Well, I never got more than one or two errors, written to the
immediate area, before the code halted with a Permission Denied
run-time error. You got a number of those errors.

As for the UAC issue regarding Admin rights goes, are you suggesting
that your 'Acronis file backup' process has write access to these
folders?

Yes it does.

 

[GS]

Ron Rosenfeld explained on 6/24/2010 :

Well, I never got more than one or two errors, written to the
immediate area, before the code halted with a Permission Denied
run-time error. You got a number of those errors.


Yes it does.

In this case, why can't these folders be read? Or does the error result
from trying to iterate an empty folder?