Citrix Excel VBA Search "There were no files found"
 

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????

Citrix Excel VBA Search "There were no files found"
 

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
wrote in message oups.com...
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????

Citrix Excel VBA Search "There were no files found"
 

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

NickHK

wrote in message
oups.com...
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????

Citrix Excel VBA Search "There were no files found"
 

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

Citrix Excel VBA Search "There were no files found"
 

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

wrote in message
ups.com...
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