Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking for Errors
I am sorry to admit that I have done very little with error checking in my
macros, so I need some help in structuring here. Our network drives need cleaning up really badly, and I have created a macro that reads through the directory and creates a worksheet of all folders, subfolders, subsubfolders, etc. I use nested For Each...Next loops to pull out the data. I have access to most everything on the network, but a couple of the big dogs have their folders secured, and when I hit one of those, I get an access denied error when I attempt to go into the folder to pull file and subfolder names. The error occurs on the 'For Each' statement. In front of the 'For Each' statement I put an 'On Error Resume Next' statement, but the statement for 'resuming next' is the 'Next' for the 'For Each', and when it tries to resume there, it says 'For loop not initialized', which of course is logical since it never got into the loop. Could I get some quick idea on how to branch somewhere, check the error number, reset the error checking, and return to the statement after the 'Next' statement on that error? I would certainly appreciate it. -- Bill @ UAMS |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking for Errors
Could you post the code that's giving the error?
"BillCPA" wrote: I am sorry to admit that I have done very little with error checking in my macros, so I need some help in structuring here. Our network drives need cleaning up really badly, and I have created a macro that reads through the directory and creates a worksheet of all folders, subfolders, subsubfolders, etc. I use nested For Each...Next loops to pull out the data. I have access to most everything on the network, but a couple of the big dogs have their folders secured, and when I hit one of those, I get an access denied error when I attempt to go into the folder to pull file and subfolder names. The error occurs on the 'For Each' statement. In front of the 'For Each' statement I put an 'On Error Resume Next' statement, but the statement for 'resuming next' is the 'Next' for the 'For Each', and when it tries to resume there, it says 'For loop not initialized', which of course is logical since it never got into the loop. Could I get some quick idea on how to branch somewhere, check the error number, reset the error checking, and return to the statement after the 'Next' statement on that error? I would certainly appreciate it. -- Bill @ UAMS |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking for Errors
Set Fldr0 = Folder0.subfolders
For Each f0 In Fldr0 sFolder0 = BaseFldr & "\" & f0.Name If Right(sFolder0, 5) < "xxxxx" Then Set Folder0 = FSO.GetFolder(sFolder0) If sFolder0 < "" Then -- SelectFilesList7 FolderName, sFolder0 End If Set Fldr1 = Folder0.subfolders On Error Resume Next -- For Each f1 In Fldr1 sFolder1 = Folder0 & "\" & f1.Name Set Folder1 = FSO.GetFolder(sFolder1) If sFolder1 < "" Then SelectFilesList7 FolderName, sFolder1 End If Set Fldr2 = Folder1.subfolders .... .... .... .... Next On Error GoTo 0 Next End If On Error GoTo 0 Next The '...' is where the code is repeated down to six levels of folders. The '--' lines are where the error message pops up (SelectFilesList7 checks for files in the folder, then the rest processes subfolders in the folder). As you can see, I'm checking manually for the one folder I know will be locked, but I'd like to have it work automatically. I suppose I could check to see if the folder is locked (not sure how to do that), but I need the error checking experience. -- Bill @ UAMS "Barb Reinhardt" wrote: Could you post the code that's giving the error? "BillCPA" wrote: I am sorry to admit that I have done very little with error checking in my macros, so I need some help in structuring here. Our network drives need cleaning up really badly, and I have created a macro that reads through the directory and creates a worksheet of all folders, subfolders, subsubfolders, etc. I use nested For Each...Next loops to pull out the data. I have access to most everything on the network, but a couple of the big dogs have their folders secured, and when I hit one of those, I get an access denied error when I attempt to go into the folder to pull file and subfolder names. The error occurs on the 'For Each' statement. In front of the 'For Each' statement I put an 'On Error Resume Next' statement, but the statement for 'resuming next' is the 'Next' for the 'For Each', and when it tries to resume there, it says 'For loop not initialized', which of course is logical since it never got into the loop. Could I get some quick idea on how to branch somewhere, check the error number, reset the error checking, and return to the statement after the 'Next' statement on that error? I would certainly appreciate it. -- Bill @ UAMS |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking for Errors
Chip Pearson's info here might help you a bit.
http://www.cpearson.com/excel/RecursionAndFSO.htm "BillCPA" wrote: Set Fldr0 = Folder0.subfolders For Each f0 In Fldr0 sFolder0 = BaseFldr & "\" & f0.Name If Right(sFolder0, 5) < "xxxxx" Then Set Folder0 = FSO.GetFolder(sFolder0) If sFolder0 < "" Then -- SelectFilesList7 FolderName, sFolder0 End If Set Fldr1 = Folder0.subfolders On Error Resume Next -- For Each f1 In Fldr1 sFolder1 = Folder0 & "\" & f1.Name Set Folder1 = FSO.GetFolder(sFolder1) If sFolder1 < "" Then SelectFilesList7 FolderName, sFolder1 End If Set Fldr2 = Folder1.subfolders ... ... ... ... Next On Error GoTo 0 Next End If On Error GoTo 0 Next The '...' is where the code is repeated down to six levels of folders. The '--' lines are where the error message pops up (SelectFilesList7 checks for files in the folder, then the rest processes subfolders in the folder). As you can see, I'm checking manually for the one folder I know will be locked, but I'd like to have it work automatically. I suppose I could check to see if the folder is locked (not sure how to do that), but I need the error checking experience. -- Bill @ UAMS "Barb Reinhardt" wrote: Could you post the code that's giving the error? "BillCPA" wrote: I am sorry to admit that I have done very little with error checking in my macros, so I need some help in structuring here. Our network drives need cleaning up really badly, and I have created a macro that reads through the directory and creates a worksheet of all folders, subfolders, subsubfolders, etc. I use nested For Each...Next loops to pull out the data. I have access to most everything on the network, but a couple of the big dogs have their folders secured, and when I hit one of those, I get an access denied error when I attempt to go into the folder to pull file and subfolder names. The error occurs on the 'For Each' statement. In front of the 'For Each' statement I put an 'On Error Resume Next' statement, but the statement for 'resuming next' is the 'Next' for the 'For Each', and when it tries to resume there, it says 'For loop not initialized', which of course is logical since it never got into the loop. Could I get some quick idea on how to branch somewhere, check the error number, reset the error checking, and return to the statement after the 'Next' statement on that error? I would certainly appreciate it. -- Bill @ UAMS |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I remove multiple 'errors' in Excel error checking, at the same time??? | Excel Discussion (Misc queries) | |||
Macro's checking for errors | Excel Programming | |||
Checking excel for errors/inconsistencies | Excel Worksheet Functions | |||
checking errors in the formulas | Excel Programming | |||
checking for errors in combobox's | Excel Programming |