Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why Isn't This Working?
Snippet of code from my routine to check file permissions is below.
Represents my crude attempt to check whether or not I have permission to access a file. For Each oFile In Dir.Files fileIsOkay = False On Error GoTo Bad_Permission Set ts = oFile.OpenAsTextStream '<<== Blows up here on second bad file ts.Close Set ts = Nothing On Error GoTo 0 '<<== This should reset the error, right? fileIsOkay = True ' If we got here, I have access to the file Bad_Permission: On Error GoTo 0 tName = oFile.Name If (Not fileIsOkay) Then tName = tName & ": PERMISSION PROBLEM" Else tName = tName & ": OKAY" End If Debug.Print tName Next oFile I loop through all files in the given (test) folder. I know there are a couple of files that I don't have permissions to view. When the first one is checked using the code above, it triggers the On Error and gives the appropriate message. When I hit the second "bad" file, I get "Run-time error '70': Permission denied" instead. The On Error code no longer works. What am I doing wrong? Thanks in advance, Eric |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why Isn't This Working?
Thanks Jim!
I did a small test - I looked at the Err object while I ran my code. When it hit the first bad file, its value became 70. When the code ran "On Error Goto 0", Err reset to 0. That seems to indicate that it does clear the error. However, my code still didn't work... I made one small change to yours - at the end I had to add "Set ts = Nothing", but otherwise your code worked and fills my need, which is to find all the files that have permission issues. Eric |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why Isn't This Working?
Hi
On Error Goto 0 does not clear the error, it clears previous instructions on what to do when errors happen. Clear with Err.Clear like this: Sub test() On Error Resume Next MsgBox 4 / 0 MsgBox "Error is: " & Error Err.Clear MsgBox "Error is: " & Error End Sub HTH. Best wishes Harald "Eric G" wrote in message ... Snippet of code from my routine to check file permissions is below. Represents my crude attempt to check whether or not I have permission to access a file. For Each oFile In Dir.Files fileIsOkay = False On Error GoTo Bad_Permission Set ts = oFile.OpenAsTextStream '<<== Blows up here on second bad file ts.Close Set ts = Nothing On Error GoTo 0 '<<== This should reset the error, right? fileIsOkay = True ' If we got here, I have access to the file Bad_Permission: On Error GoTo 0 tName = oFile.Name If (Not fileIsOkay) Then tName = tName & ": PERMISSION PROBLEM" Else tName = tName & ": OKAY" End If Debug.Print tName Next oFile I loop through all files in the given (test) folder. I know there are a couple of files that I don't have permissions to view. When the first one is checked using the code above, it triggers the On Error and gives the appropriate message. When I hit the second "bad" file, I get "Run-time error '70': Permission denied" instead. The On Error code no longer works. What am I doing wrong? Thanks in advance, Eric |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculate working days but change working week | Excel Discussion (Misc queries) | |||
Making weekend days working days - the system cuts the working tim | Excel Discussion (Misc queries) | |||
Newly created Get Function is not working when I copied the syntax from a working function | Excel Programming | |||
Macro working in Excel 2003; not working in Excel 2000 | Excel Programming | |||
Adding sales from a non working day to the previous working day | Excel Programming |