Problem with file search wild card
I am searching a directory for a filename using a wild card (*) in the file name. The problem is that the filesearch is picking up 2 files instead of one. for example, let's say there are 2 files in the folder named xFile.xls and File.xls. If I search for "File*.xls" it returns BOTH files when it should only be returning the File.xls. Below is an example of the code. Any ideas why the wildcard isn't working like it should?
Code:
Dim FsSearch As Office.FileSearch
Set FsSearch = Application.FileSearch
TempName = "File"
With FsSearch
.NewSearch
.LookIn = ThisWorkbook.Path + "\"
.FileName = TempName & "*.xls"
.SearchSubFolders = True
.FileType = msoFileTypeExcelWorkbooks
.LastModified = msoLastModifiedAnyTime
.Execute 'Count of how many records are found
For Each vaFile In .FoundFiles
Workbooks.Open vaFile, False, False
Next vaFile
End With
Re: Problem with file search wild card
Would this help?
vb Code:
Sub ListAllFiles()
Dim sNextFile As String, MyPath As String, MyWildCard As String
'~~> Path
MyPath = "C:\temp\"
'~~> File Name
MyWildCard = "File"
'~~> Developing the String
sNextFile = Dir$(MyPath & MyWildCard & "*.xls")
'~~> Search files
While sNextFile <> ""
'~~> Display all names of file matching criteria
MsgBox sNextFile
sNextFile = Dir$
Wend
End Sub
Re: Problem with file search wild card
I will have to try it and get back to you... a lot of strange things have been happening lately with my macros and it doesn't make sense. This code ran fine the other day but today it is a problem. I also had an Excel macro that had a masked edit control on it... it worked fine for the past few months, then one day when we opened the file the masked edit control had a big red "X" through it and it wouldn't work. I had to replace it with a text box. I'm just wondering if these problems are related to a bigger issue going on...