Problem with Application.filesearch

[David Howlett]

I have some code that is supposed to copy files from one folder to another.
However, on different days the files change. For example:

On day 1, the inbox folder contains Analyst3.mdb, Analyst5.mdb and
Analyst9.mdb

On day 2, the inbox folder contains Analyst4.mdb, Analyst7.mdb and
Analyst11.mdb

Here is the code:

Sub CopyNetworkInBox(InboxPath, outboxPath)
Dim x
Dim sourcefile

x = 1
notification = False
Do While x <= 20
sourcefile = "analyst" & x & ".mdb"
With Application.FileSearch
.NewSearch
.LookIn = InboxPath
.SearchSubFolders = False
.FileName = sourcefile
.MatchTextExactly = True
If .Execute > 0 Then
copyNetInboxFile InboxPath & sourcefile, outboxPath
Kill InboxPath & sourcefile
Else
Exit Do
End If
End With
x = x + 1
Loop

End Sub

The problem is that if there is a file named 'Analyst10.mdb' this code
matches the very first time through the loop (x=1,
sourcefile='Analyst1.mdb'). I get errors in the subroutine because it is
trying to copy a file named 'Analyst1.mdb' which doesn't exists.

How do I get this code to match only if the exact filename is found?

 

[Dirk Goldgar]

I have some code that is supposed to copy files from one folder to another.
However, on different days the files change. For example:

On day 1, the inbox folder contains Analyst3.mdb, Analyst5.mdb and
Analyst9.mdb

On day 2, the inbox folder contains Analyst4.mdb, Analyst7.mdb and
Analyst11.mdb

Here is the code:

Sub CopyNetworkInBox(InboxPath, outboxPath)
Dim x
Dim sourcefile

x = 1
notification = False
Do While x <= 20
sourcefile = "analyst" & x & ".mdb"
With Application.FileSearch
.NewSearch
.LookIn = InboxPath
.SearchSubFolders = False
.FileName = sourcefile
.MatchTextExactly = True
If .Execute > 0 Then
copyNetInboxFile InboxPath & sourcefile, outboxPath
Kill InboxPath & sourcefile
Else
Exit Do
End If
End With
x = x + 1
Loop

End Sub

The problem is that if there is a file named 'Analyst10.mdb' this code
matches the very first time through the loop (x=1,
sourcefile='Analyst1.mdb'). I get errors in the subroutine because it is
trying to copy a file named 'Analyst1.mdb' which doesn't exists.

How do I get this code to match only if the exact filename is found?

I've heard that the FileSearch object has some quirks, but I don't remember
at the moment what the details are. However, you can do the same thing
without the FileSearch object easily enough. How about this version -- it's
air code, but should be close to working:

'----- start of air code -----
Sub CopyNetworkInBox(InboxPath, OutboxPath)

Dim sourcefile As String

sourcefile = Dir(InboxPath & "\analyst*.mdb")
Do Until Len(sourcefile) = 0
FileCopy InboxPath & "\" & sourcefile, _
OutboxPath & "\" & sourcefile
Kill InboxPath & "\" & sourcefile
sourcefile = Dir()
Loop

End Sub
'----- end of code -----

Note that the above code will copy *all* files in InboxPath that begin with
"analyst" and end in ".mdb", not just those with the numbers 1 to 20. If
that difference is important, adjustments to the code have to be made.

Note also that, if the file names in the inbox will never exist already in
the outbox, then you could just use the Name statement to move the file,
rather than the combination of FileCopy and Kill. I've substituted that
latter combination for your call to your routine copyNetInboxFile, but you
can of course put that back if you want.