Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Trapping Problem
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 -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Trapping Problem
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Trapping Problem
On Thu, 24 Jun 2010 01:55:58 -0400, GS wrote:
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 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Trapping Problem
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. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Trapping Problem
On Thu, 24 Jun 2010 12:22:00 -0400, GS wrote:
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. -- Garry 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 ============================= |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Trapping Problem
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 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. <IMOThe 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... -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Trapping Problem
On Thu, 24 Jun 2010 13:28:28 -0400, GS wrote:
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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Error Trapping Problem
Ron Rosenfeld explained on 6/24/2010 :
On Thu, 24 Jun 2010 13:28:28 -0400, GS wrote: 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. In this case, why can't these folders be read? Or does the error result from trying to iterate an empty folder? -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Trapping #VALUE! error | Excel Discussion (Misc queries) | |||
while deleting rows it finds an error - error trapping | Excel Programming | |||
Problem with error trapping | Excel Programming | |||
error trapping | Excel Programming | |||
Problem trapping an error | Excel Programming |