MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel2000 crashes when searching a networked drive


Posted by Matt on January 24, 2002 1:34 AM

I pinched this from the Xl help files and modified it to run wit a small user form, but if I run it against 2 mapped drives to 2 other machines XL crashes almost imediately and goes white. Can anyone help on this one? It's a little project to make my life a little bit easier, and it isn't.

Thanks

Sub GetAllFilesInDirectory()

Dim i
Dim SearchString As String
Dim LookIn As String
Dim Delimiter As String

SearchString = ufrmSearch.txtSearch.Text
LookIn = ufrmSearch.txtLookIn.Text
Delimiter = ufrmSearch.txtDelimit.Text

With Application.FileSearch
.NewSearch
.FileType = msoFileTypeAllFiles ' msoFileTypeExcelWorkbooks
.LookIn = LookIn
.Filename = SearchString
.SearchSubFolders = True 'true searches subfolders, false does not
.Execute

For i = 1 To .FoundFiles.Count
Range("A" & i) = .FoundFiles(i)
MyLen = Len(Range("A" & i))
Next i

End With

Columns("A:A").Select
'Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="\", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1))

Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:=Delimiter, FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1))

'Columns("A:B").Select
'Selection.Delete Shift:=xlToLeft
'Columns("A:A").EntireColumn.AutoFit

End Sub


Posted by JohnG on January 29, 2002 8:17 AM

It may be because it doesnt see the directory's
Try this

Function DirOnDisk(dName As String) As Boolean
'Checks if a directory exists. Return "True if directory is found.

On Error GoTo BadDir
If (Dir(dName, vbDirectory) <> "") Then
DirOnDisk = True
Else
DirOnDisk = False
End If
Exit Function

BadDir:
MsgBox "Trouble searching for ' " & dName & " '." & Chr(13) & Chr(13) & _
"Check for missing ':' or '/' and typing errors.", 32, ThisWorkbook.Name
DirOnDisk = False
On Error GoTo 0
End Function