Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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???? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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???? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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???? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"Search for files and folders" option doesnot work for 100% | Excel Discussion (Misc queries) | |||
Search for the word "continued", if found, delete that row + 10 rows above | Excel Programming | |||
Search for the word "continued", if found, delete that row + 10 rows above | Excel Programming | |||
Getting "compile error" "method or data member not found" on reinstall | Excel Programming |