Limiting a file search program in Access

[BTU_needs_assistance_43]

I have a program that searches for and adds data to a table from Excel files
in a folder. Unfortunately I only need certain files from the folder. When
the crews come in, one crew files a report b383a.xls (the files i need) and
the other crew the Drag Line crew comes in and in the same large folder files
a report b383a_dl.xls. I can't move the files. Is there a way I can narrow
the search so that it will exclude any files that end in XXXXX_dl.xls?

I have seen some examples where you perform a search and when it asks for a
file name you give "C:\My Documents\exampl*" and "C:\My Documents\ex?" where
it includes any files that go 1 letter past examl or any files that in ex
followed by anything. Is there a way I can use something like this in reverse
and to limit my search away from those files instead of to only include those
files?

 

[Graham Mandeno]

Hi BTU

Three questions:

1. What code are you using to find the files? Is it a loop using the Dir()
function?

2. What are the possible values for the part before the ".xls" ("b383a" in
your example)? Are they always one of a set of known values (e.g. stored in
a table)? Are they always the same length (5 characters)? Can they ever
contain an underscore?

3. Do the files you wish to exclude ALWAYS ens with "_dl.xls" or are there
other exclusions?

The best approach depends on your answers. For example, if you want ALL the
files with five characters before .xls then you could use ?????.xls. If
your prefixes are a known list in a table, you might be best to loop through
a recordset and check for the existence of each file in turn.

 

[BTU_needs_assistance_43]

The files are consecutively numbered starting at 1 but I don't have to worry
about them til they get into the 50's or 60's. From there they go all the way
up to whatever the most recent one is in the hundreds somewhere. What we've
done is we break each area into sections and as many reports need to be filed
on a section all have the same number starting with 25a, 25b, 25c, etc...
although they don't go higher than e or f. There are also several that we
managed to get done in one report so there is no letter just a number. Then
each set of numbers is preceeded by the abreviation of where the report was
taken from, between 1 and 3 letters (ex: elk, b, owe, rn...) so a full file
name will look something like oww39b.xls. I need all those files but also to
exclude oww39b_dl.xls.

Also we're using Access 2003 so even though I am aware the code I'm using
doesn't work in 2007, we're using 2003 and won't be upgrading in the near
future so this code still works fine. There has been one snag, narrowing the
search results to recently modified files won't work in Access although it
works perfectly in Excel.

With Application.FileSearch
.NewSearch
.LookIn = "C:\My Documents\Reports"
.SearchSubFolders = True
LastModified = msoLastModifiedLastWeek
FileType = msoFileTypeExcelWorkbooks

Hi BTU

Three questions:

1. What code are you using to find the files? Is it a loop using the Dir()
function?

2. What are the possible values for the part before the ".xls" ("b383a" in
your example)? Are they always one of a set of known values (e.g. stored in
a table)? Are they always the same length (5 characters)? Can they ever
contain an underscore?

3. Do the files you wish to exclude ALWAYS ens with "_dl.xls" or are there
other exclusions?

The best approach depends on your answers. For example, if you want ALL the
files with five characters before .xls then you could use ?????.xls. If
your prefixes are a known list in a table, you might be best to loop through
a recordset and check for the existence of each file in turn.
--

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a program that searches for and adds data to a table from Excel
files
in a folder. Unfortunately I only need certain files from the folder. When
the crews come in, one crew files a report b383a.xls (the files i need)
and
the other crew the Drag Line crew comes in and in the same large folder
files
a report b383a_dl.xls. I can't move the files. Is there a way I can narrow
the search so that it will exclude any files that end in XXXXX_dl.xls?

I have seen some examples where you perform a search and when it asks for
a
file name you give "C:\My Documents\exampl*" and "C:\My Documents\ex?"
where
it includes any files that go 1 letter past examl or any files that in ex
followed by anything. Is there a way I can use something like this in
reverse
and to limit my search away from those files instead of to only include
those
files?

 

[BTU_needs_assistance_43]

Actually I just realized I totally lied. The breakdown of the reports
a,b,c... are all in the reports themselves not the names. The names are
simply owe25.xls. However, I need to exclude XXX_dl.xls, XXX_dl2.xls,
XXXdl.xls, and XXXdl2.xls

Hi BTU

Three questions:

1. What code are you using to find the files? Is it a loop using the Dir()
function?

2. What are the possible values for the part before the ".xls" ("b383a" in
your example)? Are they always one of a set of known values (e.g. stored in
a table)? Are they always the same length (5 characters)? Can they ever
contain an underscore?

3. Do the files you wish to exclude ALWAYS ens with "_dl.xls" or are there
other exclusions?

The best approach depends on your answers. For example, if you want ALL the
files with five characters before .xls then you could use ?????.xls. If
your prefixes are a known list in a table, you might be best to loop through
a recordset and check for the existence of each file in turn.
--

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a program that searches for and adds data to a table from Excel
files
in a folder. Unfortunately I only need certain files from the folder. When
the crews come in, one crew files a report b383a.xls (the files i need)
and
the other crew the Drag Line crew comes in and in the same large folder
files
a report b383a_dl.xls. I can't move the files. Is there a way I can narrow
the search so that it will exclude any files that end in XXXXX_dl.xls?

I have seen some examples where you perform a search and when it asks for
a
file name you give "C:\My Documents\exampl*" and "C:\My Documents\ex?"
where
it includes any files that go 1 letter past examl or any files that in ex
followed by anything. Is there a way I can use something like this in
reverse
and to limit my search away from those files instead of to only include
those
files?

 

[Graham Mandeno]

OK, then the best approach is to return ALL the files matching "*.xls" and
filter out the unwanted ones before processing:

For i = 1 To .FoundFiles.Count
strFile = .FoundFiles(i)
If Right(strFile, 6) <> "dl.xls" and Right(strFile, 7) <> "dl2.xls"
Then
' process file
End If
Next i

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Actually I just realized I totally lied. The breakdown of the reports
a,b,c... are all in the reports themselves not the names. The names are
simply owe25.xls. However, I need to exclude XXX_dl.xls, XXX_dl2.xls,
XXXdl.xls, and XXXdl2.xls

Hi BTU

Three questions:

1. What code are you using to find the files? Is it a loop using the
Dir()
function?

2. What are the possible values for the part before the ".xls" ("b383a"
in
your example)? Are they always one of a set of known values (e.g. stored
in
a table)? Are they always the same length (5 characters)? Can they ever
contain an underscore?

3. Do the files you wish to exclude ALWAYS ens with "_dl.xls" or are
there
other exclusions?

The best approach depends on your answers. For example, if you want ALL
the
files with five characters before .xls then you could use ?????.xls. If
your prefixes are a known list in a table, you might be best to loop
through
a recordset and check for the existence of each file in turn.
--

Graham Mandeno [Access MVP]
Auckland, New Zealand

"BTU_needs_assistance_43"
<[email protected]>
wrote in message

I have a program that searches for and adds data to a table from Excel
files
in a folder. Unfortunately I only need certain files from the folder.
When
the crews come in, one crew files a report b383a.xls (the files i need)
and
the other crew the Drag Line crew comes in and in the same large folder
files
a report b383a_dl.xls. I can't move the files. Is there a way I can
narrow
the search so that it will exclude any files that end in XXXXX_dl.xls?

I have seen some examples where you perform a search and when it asks
for
a
file name you give "C:\My Documents\exampl*" and "C:\My Documents\ex?"
where
it includes any files that go 1 letter past examl or any files that in
ex
followed by anything. Is there a way I can use something like this in
reverse
and to limit my search away from those files instead of to only include
those
files?