IME, FileSearch is not reliable. It works inconsistently across different
has been removed from Access 2007.
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
news:1171314252.848360.15920@a34g2000cwb.googlegro ups.com...
Quote:
>I have recently converted an Access 97 database to Access 2003.
However, I am running into a problem with using
Application.Filesearch
to locate a file in my directory. The code follows:
>
-------------------------------
Dim dbDir As String 'Directory where database stored
Dim dbPath As String 'Directory + Name of database
Dim dbFileName As String 'Name of the database
Dim Card1File As String 'Text file to hold Card 1 data
Dim Card2File As String 'Text file to hold Card 2 data
Dim Card3File As String 'Text file to hold Card 3 data
Dim PAAFile As String 'The resulting file created
from
export
Dim fs As Variant 'File Search variable
>
>
'Retrieve the database path and database name and directory
dbPath = CurrentDb.Name
dbFileName = Dir(dbPath)
dbDir = Left(dbPath, Len(dbPath) - Len(dbFileName))
>
>
'Create the Pesticide text file names
Card1File = dbDir & "PAACard1.txt"
Card2File = dbDir & "PAACard2.txt"
Card3File = dbDir & "PAACard3.txt"
>
>
'Create final text file by appending julian day to the file name
"PAA200"
PAAFile = dbDir & "paa200." & DatePart("y", Now())
>
>
'Export the data from tables PestCard1, PestCard2, PestCard3 into
the 3 text
'files. These 3 text files will then be joined into 1 file.
DoCmd.TransferText acExportFixed, "PestCard1 Export Specification",
_
"PestCard1", Card1File, False, ""
DoCmd.TransferText acExportFixed, "PestCard2 Export Specification",
_
"PestCard2", Card2File, False, ""
DoCmd.TransferText acExportFixed, "PestCard3 Export Specification",
_
"PestCard3", Card3File, False, ""
>
>
'Set File search parameter to look for output file name
Set fs = Application.FileSearch
fs.FileName = PAAFile
>
>
'if output file is found, append data from the three text files to
the
'end of the current file. "/b" is added as a command line option to
'prevent EOF character from being copied to the file
If fs.Execute 0 Then
Call Shell(Environ$("COMSPEC") & " /c copy """ & PAAFile & """ +
""" & _ Card1File & """ + """ & Card2File & """ + """ & Card3File &
_
""" """ & PAAFile & """ /b ", vbHide)
>
>
'Output file is not found, so Concatenate the 3 text files into one
file
Else
Call Shell(Environ$("COMSPEC") & " /c copy """ & Card1File & _
""" + """ & Card2File & """ + """ & Card3File & """
""" & _
PAAFile & """ /b ", vbHide)
End If
>
>
------------------------------------------------------
>
>
The filesearch is not finding the file as it should. Has there been
a
change with how this works in Access 2003? This worked perfectly in
A97.
>
>
Thanks in advance for any help!!!
>
