Citrix Excel VBA Search "There were no files found"

[myworld726]

Hi,

I use a folders seach with Macros. The Search works fine on my
computer or a share drive. But the problem is it won't work on a Citrix
Share Drive?

When you run the code you get the answer "There were no files found"
there are files in the folder it's searching in.

The Code is:

Sub FindFilesXX()
Dim myPath As String
Dim lLen As Long, i As Long
Dim parts

myPath = ThisWorkbook.Path & "\Files"
With Application.FileSearch
.NewSearch
.LookIn = myPath
.SearchSubFolders = True
.Filename = "*.*"
.FileType = msoFileTypeExcelWorkbooks
ActiveSheet.Unprotect Password:="XXXXXXXXXXX"
Range("H2:I5000").Select
Selection.ClearContents
If .Execute() > 0 Then
For i = 2 To .FoundFiles.Count
parts = Split(Trim(Replace(Dir(.FoundFiles(i)), ".xls", "")))
Cells(i, 8) = parts(UBound(parts))
Cells(i, 9).FormulaR1C1 = "=Hyperlink(" & Chr(34) &
..FoundFiles(i) _
& Chr(34) & ",R[0]C[-1])"
Next i
Range("i2:i2005").Select
With Selection.Font
.Name = "Arial"
.Size = 14
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
End With
Columns("h:j").Select
Selection.Sort Key1:=Range("j2"), Order1:=xlDescending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("c11").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True, Password:="XXXXXXXXXXX"
Range("C11:E11").Select
Else
Range("C11:E11").Select
MsgBox "There were no files found."
End If
End With
End Sub


Can anyone help here????

 

[Mark Ivey]

If it is anything like the Citrix network I am used to working with, it is a pretty slow connection. You might consider putting a wait function inline with our code just before these files should be accessed to see if it can update it first and then see what you are looking for.

--
Mark Ivey
Hi,

I use a folders seach with Macros. The Search works fine on my
computer or a share drive. But the problem is it won't work on a Citrix
Share Drive?

When you run the code you get the answer "There were no files found"
there are files in the folder it's searching in.

The Code is:

Sub FindFilesXX()
Dim myPath As String
Dim lLen As Long, i As Long
Dim parts

myPath = ThisWorkbook.Path & "\Files"
With Application.FileSearch
.NewSearch
.LookIn = myPath
.SearchSubFolders = True
.Filename = "*.*"
.FileType = msoFileTypeExcelWorkbooks
ActiveSheet.Unprotect Password:="XXXXXXXXXXX"
Range("H2:I5000").Select
Selection.ClearContents
If .Execute() > 0 Then
For i = 2 To .FoundFiles.Count
parts = Split(Trim(Replace(Dir(.FoundFiles(i)), ".xls", "")))
Cells(i, 8) = parts(UBound(parts))
Cells(i, 9).FormulaR1C1 = "=Hyperlink(" & Chr(34) &
.FoundFiles(i) _
& Chr(34) & ",R[0]C[-1])"
Next i
Range("i2:i2005").Select
With Selection.Font
.Name = "Arial"
.Size = 14
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
End With
Columns("h:j").Select
Selection.Sort Key1:=Range("j2"), Order1:=xlDescending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("c11").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True, Password:="XXXXXXXXXXX"
Range("C11:E11").Select
Else
Range("C11:E11").Select
MsgBox "There were no files found."
End If
End With
End Sub


Can anyone help here????

 

[NickHK]

What do you get with the native VBA functions like Dir() ?
..FileSearch always seems flakey.

NickHK

 

[myworld726]

Thanks for your help.

I worked it out


it worked by deleting the

..FileType = msoFileTypeExcelWorkbooks

just posting here so if some poor preson like me ever goes searching
for this answer

thanks bye

 

[NickHK]

To me the answer is do not use .FileSearch, as it plainly do not work as
expected.
You can do it yourself in a few lines of VBA code with guaranteed results.
And no, there no need for the FSO.

NickHK