Eliminate "C:\Windows" directory from .FileSearch in "C:\" d

[Beginner Bob]

I am trying to search for a file using Application.FileSearch, and it keeps coming up with 3 files:

1. Actual file
2. Shortcut through WindowsApp...Office Recent
3. Shortcut through Windows Recent

How can I eliminate the shortcuts from the find procedure. I list the end of the file as ".xls" and I have tried .Filetype as "msoFileTypeExcelWorkbooks" and "XlExcelLinks", and it always comes up with same file in the same directory 3 times, as 3 files.

 

[]-[ /-\ \/\/ /-\ | | /-\]

Bob,

Try adding to (With Application.Filesearch) specs
.Filename = "*.xls"

This will tell it to search for all files with app extension ".xls". You can replace any extension accordingly to search for appropriate files (e.g. "*.txt" will find all text docs.)

Also, make sure you specify the directory to search (.LookIn = "path here"), and also if you want appsearch to look in subdirectories or not (.SearchSubFolders = True/False). Be sure to include these two options with the (With Application.Filesearch) spec.

Hope this helps.

Aloha!

p.s. you can make the search more specific by adding more characters to the wildcard--e.g. "a*.xls" should return all Excel files that begin with the letter "a", etc...

 

[Beginner Bob]

Thanks for the response Aloha, but I've tried all of that already. Without pasting my entire section of code, I've entered the following information:

With Application.Filesearch
.NewSearch
.LookIn = "C:"
.SearchSubFolders = True
.Filename = TT 'value of a certain cell called earlier in code
.MatchTextExactly = True
'I've tried .FileType = msoFileTypeExcelWorkbooks
'I've tried .FileType = XlExcelLinks
'the rest of the code

Both methods of naming the .Filetype still returned 3 results, 2 of them being shortcuts under the "Recent" folders. I'm not sure if this can even be done, but I was hoping there could be a way to eliminate the code from recognizing the shortcuts as the actual file. Thanks again.

 

[]-[ /-\ \/\/ /-\ | | /-\]

Bob,

What is the value of 'TT'?

Aloha!

 

[Beginner Bob]

Aloha, TT is the name of a payroll file called by a date entered in cell A4. All payroll files are a week apart, and are named "MM-DD-YYYY PAYROLL.xls", ie: The latest weeks are called "02-12-2002 PAYROLL.xls" and "02-19-2002 PAYROLL.xls". I have all of this set up in my code, and it finds each file with no problem, but it finds the shortcuts under the 2 "Recent" folders as well. The reason I want to search the entire C drive including subfolders instead of the specific folder which I store it in is in case the directory changes, or the file is moved, etc. The filename will definitely stay the same though. Both "Recent" folders have the root directory as "C:Windows", so I don't know if a directory can be blocked from the search. Any ideas? Thanks.

 

[]-[ /-\ \/\/ /-\ | | /-\]

Bob,

instead of using the variable 'TT', try using:
.Filename = "*PAYROLL.XLS"

OR, (assuming Dim TT as String) use:
.Filename = TT & ".xls"

The first method narrows search down to all files ending with the word "PAYROLL" (which would seem to work in your case), but will not have as much control and flexibility as the second method. I have a feeling that your cell reference is the problem...it's PROBABLY only returning a string without the extension (.xls), hence the appsearch is returning all files with the string, and not discriminating the file extension (I don't know, but I have a suspicion it's that). The second method concatenates the extension .xls to your string and will be more specific as far as your returned results. (If you want to be sure if this is the case, add a line:
MsgBox "This is the value of TT: " & TT
anywhere after the line where you set the value of TT [Set TT = "(YourValue)"]. If it returns a string without an extension, then you have found your problem. Let me know if this works or not.

Aloha!

p.s. Aloha is just a salutation--not my nick. Those funny lookin lines by 'Username' is probably a lot harder to type though, so maybe it's better to just use that! ;{

 

[Beginner Bob]

Yeah, Aloha is a little easier! Anyway, thanks for sticking with me on this. I have also tried everything you mentioned. I should have done this a while ago, but here's everything up to the open file line:

Private Sub CommandButton1_Click()
Dim OT As Worksheet, CC As Range, BB As Range
Dim TT As String, VN As String, ACT As Workbook, WBB As Workbook
'Dim other stuff here
Set ACT = ThisWorkbook
Set OT = ACT.Sheets("OVERTIME")
Set BB = OT.Range("A4")
Set CC = OT.Range("A7")
If BB.Value = "" Then
'a bunch of error checking here
Else
MT = Month(BB)
If MT < 10 Then MT = "0" & MT
DY = Day(BB)
If DY < 10 Then DY = "0" & DY
YR = Year(BB)
VN = "PAYROLL.xls"
TT = MT & "-" & DY & "-" & YR & " " & VN
With Application.FileSearch
.NewSearch
.LookIn = "C:"
.SearchSubFolders = True
.Filename = TT
.MatchTextExactly = True
.FileType = msoFileTypeExcelWorkbooks

If .Execute() > 0 Then
'rest of code here

it's definitely sloppy, but I will clean it up later. In any case, I can't use "*PAYROLL.xls" because there will be 52 files for each year (weekly payroll). I have tried the message box approach, and it names the file "C:....

 

[]-[ /-\ \/\/ /-\ | | /-\]

Bob,

I utilize filesearch in one of my procedures, but I reference a specific folder on the network. I'm glad I did it like that from the get-go, because I wasn't aware that the filesearch would do this (list multiple files). I must say that I don't possess the knowledge or the patience to try to figure out a solution (as my approach would be to take the simple route): save all your files in a specific folder ("C:My Documents" for example) and reference that folder in your routine instead of referencing the entire C: drive and searching subfolders. Sorry!

Aloha!

p.s. I was thinking of adding "workaround code" to delete multiple instances of showing up, but afterwards, I thought the simplest approach would be the best--not to mention less work and frustration! :}
Anyways, maybe someone more knowledgable than myself will be able to assist you better. Good luck!

 

[Beginner Bob]

Well thanks for all the effort, I appreciate it. I'm sure I'll figure something out.