file search

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have created a form where the user can specify the path they would like the
macro to look into. I would like to provide them a list (like the windows
file search). Any ideas?

Thanks
 
A quick question regarding the code located here:
http://www.mvps.org/access/api/api0001.htm

Does anyone know if you can tweak the filter in that code to only show the
user the last file?

By last I mean either the max name (in the event the file contains a
date/time in the name) or by creation date (in the event you want the user to
only see the most recently created file).

Thanks!
 
Hi Robert,

If you only want the user to see the one file it means you know better
than they do which file is wanted. So you can just use the code at
http://www.mvps.org/access/api/api0001.htm to let the user select the
folder (unless you know that already too) and then write VBA code to
find the most recent file in that folder with an appropriate name.
 
John/Dirk,

I think maybe I mis-stated what I'm looking for.

The code (http://www.mvps.org/access/api/api0001.htm) works great. I am
able to 'filter' some of the contents already. For example, in the folder
the user picks, I only want files that begin with "FAPSNDL". I already
filter for that, but I want a twist.

What I'm looking to do is this:
The files have the date and time in the name:
FAPSNDL200704010800.TXT
(FAPSNDLyyyymmddhhmm.TXT)

I'd like to find the last file I imported (on each record I import, I have a
field that indicates the file it came from) and only list the files in the
user's import folder that are newer (greater) than the max file I imported.

Using the code I already have (http://www.mvps.org/access/api/api0001.htm),
I'd like to tweak it to somehow filter for not just the files that start with
FAPSNDL, but rather files that have a file name greater (since the file has a
date/time, I can consider files as greater than/less than) than the most
recent file I imported.

Is there some kind of way to filter for files that start with FAPSNDL but
that are "greater than" the maximum file name I have as the result of a query
or on a form?
 
Robert,

The standard File dialog doesn't allow fancy filtering like that.
Probably what I'd do is use the code at
http://www.mvps.org/access/api/api0002.htm to let the user select the
folder, then make repeated calls to Dir() to iterate through all the
files in the folder and test their names. If a name fits your criteria,
append it to a string variable; when you've got all the names, assign
the string to the RowSource of a listbox and let the user pick a file
from the list.
 
John,

Ok, so I have the folder dialog up and running, but I'm a little thick on
how to get the variable set up that will hold a string of files to
select...I'm guessing I need to set up a loop that runs for X times (x being
the number of files in the folder to evaluate - all of them I'm guessing).

How do I determine how many files are in the selected folder so I know what
X is, then how do I append them to a variable list that I can use as a source
for my drop down box? Is there an example somewhere?
 
Dir() does the iteration semi-automatically. Air code:

Dim TheFolder As String
Dim FileFilterMinimum As String
Dim FileList As String
Dim ThisFile As String

TheFolder = "D:\Folder\Subfolder"
FileFilterMinimum = "FAPSNDL200704010800.TXT"

ThisFile = Dir(TheFolder & "\*.TXT")
Do While Len(ThisFile) > 0
If ThisFile >= FileFilterMinimum Then
FileList = FileList & ThisFile & ";"
End If
ThisFile = Dir() 'get next filename
Loop

'get rid of superfluous terminal ;
FileList = Left(FileList, Len(FileList) - 1)


This will need some refinement if you want to list the files in
alphabetical order.
 
John,

That code works great! Very little tweaking required (set 'TheFolder'
variable to pull from an existing field on my form, changed the 'if ThisFile
=' to 'if This File >'
Click to expand...

Now I have 2 ways for a user to choose their import file - automatically
using this code and a drop down box, or manually by using the
http://www.mvps.org/access/api/api0001.htm code (I added a line at the end of
that code to update the combo box you helped me code...then just had the
import process use whatever is in the combo box).

Thanks to all of you for your help!
 
Back
Top