Help with filename search

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jimbo1 - 28 Mar 2006 13:32 GMT

I don't know if anyone could help with this. The idea is fairly simple.
At present I have a macro that searches for a file. Each file name is
47 char long and is found using

Set fs = Application.FileSearch
With fs
LookIn = "G:\PUB"
SearchSubFolders = False
Filename =
Out_TkbAwMegVLHRkMfximTWZRvqohpTQOjKNqXN.finish
etc...

Is there a way of of searching using a 'like' statement eg,

filename like Out_TkbAw ??

Signature

Jimbo1

Reply to this Message

Snake Plissken - 28 Mar 2006 14:39 GMT

try this code - maybe it would be helpful...

Sub ff()

Dim fs As Object
Dim temp_name As String
Dim i As Long

temp_name = "Out_TkbAw"

Set fs = Application.FileSearch
With fs
.NewSearch
.LookIn = G:\PUB"
.SearchSubFolders = False
.MatchTextExactly = False
.Filename = temp_name
.Execute

For i = 1 To .FoundFiles.Count
MsgBox .FoundFiles(i)
Next i

End With

End Sub

Reply to this Message

Snake Plissken - 28 Mar 2006 14:46 GMT

'maybe we could add an additional loop:

if .FoundFiles.Count > 0 then

For i = 1 To .FoundFiles.Count
MsgBox .FoundFiles(i)
Next i

else

MsgBox "No files !"

end if

Reply to this Message

Jimbo1 - 28 Mar 2006 15:13 GMT

thank for reply. Haven't had time to test it yet but it looks as though
I can use it. cheers

Signature

Jimbo1

Reply to this Message

GregR - 28 Mar 2006 19:13 GMT

Snake, how do I take your code and go one step further. That is, if
there are found files, I would like to open each file, copy sheet(1) of
each WB to a summary WB, close the found file without saving and print
the summary WB. TIA

Greg

Reply to this Message

Snake Plissken - 28 Mar 2006 22:40 GMT

try to use smtg like that... the code is not optimized i suppose - I
didnt check it coz it's late :-)

Sub ff()

Dim fs As Object
Dim temp_name, folder_name, base_file, temp_file As String
Dim i, j As Long

temp_name = ".xls"
folder_name = "C:\"
base_file = Application.ActiveWorkbook.Name

Set fs = Application.FileSearch
With fs
.NewSearch
.LookIn = folder_name
.SearchSubFolders = False
.MatchTextExactly = False
.Filename = temp_name
.Execute

If .FoundFiles.Count > 0 Then

For i = 1 To .FoundFiles.Count

Workbooks.Open Filename:=.FoundFiles(i)
temp_file = Application.ActiveWorkbook.Name

Sheets(1).Copy
After:=Workbooks(base_file).Sheets(Application.Workbooks(base_file).Sheets.C
ount)
Workbooks(temp_file).Close SaveChanges:=False

Next i

For j = 1 To Workbooks(base_file).Sheets.Count

Sheets(j).Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1

Next j

Else

MsgBox "No files to perform!"

End If

End With

End Sub

Reply to this Message