Having problems trapping error 76

[davery83]

Hi
I've registered here hoping someone can help me with error trapping, my code is below:

wsdocs = "\\" & Sheets(b).Range(currentname) & "\c$\docume~1\"

Set fold = fso.GetFolder(wsdocs)

For Each Folder In fold.SubFolders

If Folder.Name = "All Users" Then GoTo nextfolder
If Folder.Name = "RM Default User" Then GoTo nextfolder
If Folder.Name = "Default User" Then GoTo nextfolder
If Folder.Name = "LocalService" Then GoTo nextfolder
If Folder.Name = "NetworkService" Then GoTo nextfolder
If Folder.Name = "Administrator" Then GoTo nextfolder
UserForm2.ListBox1.AddItem Sheets(b).Range(currentname) & "\" & Folder.Name

Basically, the entire code looks up a list of computers from a spreadsheet, and then adds the user folders from documents and settings to a listbox, however this works fine if the machine(s) is turned on.
I need some kind of error trapping for if the machine is off - this doesn't seem to work. It just hangs, and then eventually comes up with error 76.
If it does manage to find a computer thats on, for example if the list is:

station1
station2
station3

and station 2 is turned off, but 1 and 3 are on, then it will add station1's folders fine, but then it will add the same folders but say they are on station2 also. It will then pick up the correct folders on station3 (as its on).
Please can I have some advice as to where to put the error handling?
Thanks!

[Bob Phillips]

Try something like this

[vba]

On Error GoTo Err_Handler

For Each folder In fold.SubFolders

If folder.Name = "All Users" Then GoTo nextfolder
If folder.Name = "RM Default User" Then GoTo nextfolder
If folder.Name = "Default User" Then GoTo nextfolder
If folder.Name = "LocalService" Then GoTo nextfolder
If folder.Name = "NetworkService" Then GoTo nextfolder
If folder.Name = "Administrator" Then GoTo nextfolder
UserForm2.ListBox1.AddItem Sheets(b).Range(currentname) & "\" & folder.Name
Next_Folder:
Next folder

Exit Sub 'so as not to drop into error handler

Err_Handler:

If Err.Number = 76 Then

MsgBox folder.Name & " problem"
Resume Next_Folder
End If
[/vba]

[RolfJ]

If I correctly understand your problem and am not missing the point the error occurs when you invoke the GetFolder method in
[vba]
Set fold = fso.GetFolder(wsdocs)

[/vba]
If that's the case you could try the following error trapping:

[vba]
On Error Resume Next
Set fold = fso.GetFolder(wsdocs)
If Not fold Is Nothing Then
For Each Folder In fold.SubFolders
'etc
Next Folder
End If
[/vba]

[davery83]

Sorry, yes the problem occurs with [VBA]Set fold = fso.GetFolder(wsdocs)
[/VBA]
I've tried your suggestion, however it doesn't produce the error and asking me to debug, it just takes forever.
If all the machines in the list is on, then the results are quickly produced, however if one machine is off, it takes over twice as long - I would have thought the path not found would have been quickly reported and I need to skip this machine.

[davery83]

Ok, I'll past all my code here to see if its something else, and hopefully I can explain better what it does.

[VBA]

Public Sub checkforprofiles(sheetnumbers, nosheets)
UserForm2.ListBox1.Clear

If nosheets = "Y" Then
Call nosheetsselected
GoTo stoploop
End If

Dim fold As Scripting.Folder
Dim fso As New FileSystemObject
Dim currentname As String
Dim stationcount As Integer
Dim wsdocs As String

'check sheets to see which ones have been ticked 1=ticked 0=unticked
For b = 1 To 16
If sheetnumbers(b) = 1 Then
'if the sheet is ticked count the workstations on the sheet
stationcount = WorksheetFunction.CountA(Sheets(b).Range("A2:A50"))
'if it is unticked, goto try the next sheet
If sheetnumbers(b) = 0 Then
GoTo nextsheet
End If
'get workstation name from the sheet
For i = 2 To stationcount + 1
currentname = "A" & i
'set documents and settings path on the workstation
wsdocs = "\\" & Sheets(b).Range(currentname) & "\c$\docume~1\"

'set the scripting folder to the current workstation area
'create an error trap if the workstation is off, to skip and folders and not add anything to the listbox
On Error Resume Next
Set fold = fso.GetFolder(wsdocs)
If Not fold Is Nothing Then
For Each Folder In fold.SubFolders
'skip important folders
If Folder.Name = "All Users" Then GoTo nextfolder
If Folder.Name = "RM Default User" Then GoTo nextfolder
If Folder.Name = "Default User" Then GoTo nextfolder
If Folder.Name = "LocalService" Then GoTo nextfolder
If Folder.Name = "NetworkService" Then GoTo nextfolder
If Folder.Name = "Administrator" Then GoTo nextfolder
'add folders to listbox
UserForm2.ListBox1.AddItem Sheets(b).Range(currentname) & "\" & Folder.Name
'move on to next folder
Next Folder
End If
'move on to next workstation
nextpc:
Next i

End If
'move on to next sheet
nextsheet:
Next b
'if no sheets are selected, go here
stoploop:

End Sub
[/VBA]

[GTO]

Greetings,

In post#1, I am guessing that you must have had an On Error Resume Next some place not shown, as at least if my thinker is working, it would have jammed on the first bad folder (ie - station not on/available).

Anyways, whether using 'Resume Next' as Rolf showed, or including a handler as Bob showed, as you noted, the last good folder's info will just keep getting added.

This is because after we get a valid folder, 'fold' will remain set to that folder if the next attempted Set fold=... (ie the next loop) fails.

I think that if we Set fold to Nothing in the error handler, this would then solve the problem of returning the last good folder's info/properties repeatedly.

Not tested, so please in a junk copy of your wb:
[vba]
Public Sub checkforprofiles(sheetnumbers, nosheets)
Dim fso As New FileSystemObject
Dim fold As Scripting.Folder
Dim currentname As String
Dim stationcount As Integer
Dim wsdocs As String

UserForm2.ListBox1.Clear

If nosheets = "Y" Then
Call nosheetsselected
GoTo stoploop
End If

'check sheets to see which ones have been ticked 1=ticked 0=unticked
For b = 1 To 16
If sheetnumbers(b) = 1 Then
'if the sheet is ticked count the workstations on the sheet
stationcount = WorksheetFunction.CountA(Sheets(b).Range("A2:A50"))
'if it is unticked, goto try the next sheet
If sheetnumbers(b) = 0 Then
GoTo nextsheet
End If
'get workstation name from the sheet
For i = 2 To stationcount + 1
currentname = "A" & i
'set documents and settings path on the workstation
wsdocs = "\\" & Sheets(b).Range(currentname) & "\c$\docume~1\"

'set the scripting folder to the current workstation area
'//Failing should set fold to nothing, so that the last good folder Set//
'// does not continue to be returned.
On Error GoTo err_hnd
Set fold = fso.GetFolder(wsdocs)
If Not fold Is Nothing Then
For Each Folder In fold.SubFolders
'skip important folders
If Folder.Name = "All Users" Then GoTo nextfolder
If Folder.Name = "RM Default User" Then GoTo nextfolder
If Folder.Name = "Default User" Then GoTo nextfolder
If Folder.Name = "LocalService" Then GoTo nextfolder
If Folder.Name = "NetworkService" Then GoTo nextfolder
If Folder.Name = "Administrator" Then GoTo nextfolder
'add folders to listbox
UserForm2.ListBox1.AddItem Sheets(b).Range(currentname) & "\" & Folder.Name
'move on to next folder
Next Folder
End If
'move on to next workstation
nextpc:
Next i

End If
'move on to next sheet
nextsheet:
Next b
'if no sheets are selected, go here
stoploop:
Exit Sub
err_hnd:
If Err.Number = 76 Then
Set fold = Nothing
Resume nextpc
Else
MsgBox "unk error"
End If
End Sub
[/vba]

Now regardless of whether the above solves the current issues, as an alternate suggestion, how about we skip producing the error in the first place, by using FSO's .FolderExists Method?
[vba]
Public Sub checkforprofiles(sheetnumbers, nosheets)
Dim fso As New FileSystemObject
Dim fold As Scripting.Folder
Dim currentname As String
Dim stationcount As Integer
Dim wsdocs As String

UserForm2.ListBox1.Clear

If nosheets = "Y" Then
Call nosheetsselected
GoTo stoploop
End If

'check sheets to see which ones have been ticked 1=ticked 0=unticked
For b = 1 To 16
If sheetnumbers(b) = 1 Then
'if the sheet is ticked count the workstations on the sheet
stationcount = WorksheetFunction.CountA(Sheets(b).Range("A2:A50"))
'if it is unticked, goto try the next sheet
If sheetnumbers(b) = 0 Then
GoTo nextsheet
End If
'get workstation name from the sheet
For i = 2 To stationcount + 1
currentname = "A" & i
'set documents and settings path on the workstation
wsdocs = "\\" & Sheets(b).Range(currentname) & "\c$\docume~1\"

'set the scripting folder to the current workstation area

'// Test for folder first//
If fso.FolderExists(wsdocs) Then
Set fold = fso.GetFolder(wsdocs)
For Each Folder In fold.SubFolders
'skip important folders
If Folder.Name = "All Users" Then GoTo nextfolder
If Folder.Name = "RM Default User" Then GoTo nextfolder
If Folder.Name = "Default User" Then GoTo nextfolder
If Folder.Name = "LocalService" Then GoTo nextfolder
If Folder.Name = "NetworkService" Then GoTo nextfolder
If Folder.Name = "Administrator" Then GoTo nextfolder
'add folders to listbox
UserForm2.ListBox1.AddItem Sheets(b).Range(currentname) & "\" & Folder.Name
'move on to next folder
Next Folder
End If
'move on to next workstation
nextpc:
Next i
End If
'move on to next sheet
nextsheet:
Next b
'if no sheets are selected, go here
stoploop:
End Sub
[/vba]

Hope that works/helps,

Mark

[davery83]

Thats great, thanks! I've used a combination of things to get it working but the most important part was adding the if....folderexists before set getfolder etc
Sorry for taking so long to reply! I'm having another problem but I'll start a new thread for that, thanks for your help.