Solved

FileSearch Application Access97-Windows 2000

Posted on 2002-07-09
9
775 Views
Last Modified: 2008-03-03
I use the VBA facilities of Access 97 to validate data in CSV files.  In a number of routines I use the application filesearch facility:
Set fs = Application.FileSearch
    fs.Lookin = Pathroute' route to files on the server
    fs.FileName = FileSpec ' Filenames e.g. "*.txt"
    If fs.Execute(SortBy:=msoSortbyFileName, SortOrder:=msoSortOrderAscending) > 0 Then etc.
 
to identify the files stored in different folders on our server.  It all works fine using our corporate WINNT operating system, however, we are moving over to Windows 2000 and it doesn't like the filesearch application, particularly the fs.Filename in the above example.  A conversion to Access 2000 interferes with other multiuser dbases that are OK on WINNT and Win 2000 and Access97.
0
Comment
Question by:MartinEversfield
  • 4
  • 4
9 Comments
 
LVL 9

Expert Comment

by:perove
Comment Utility
Could be far out here but..

Filesearch is part of Office.  Not the OS.
It's a object, not part of VBA.


You need to reference Microsoft office 8.0 library (on acc97). MSO97.DLL

And

Set fs = Application.FileSearch should'nt it be something like..
set fs = FileSearch.SOMETHING

Right??


perove



0
 

Author Comment

by:MartinEversfield
Comment Utility
Thanks Perove.  Yes Microsoft Office 8.0 object Library is Referenced properly and fs is Dimmed As Object at the head of each procedure.  The help files list the use of the filesearch as:

With Application.FileSearch
    If .Execute() > 0 Then
        MsgBox "There were " & .FoundFiles.Count & _
            " file(s) found."
        For i = 1 To .FoundFiles.Count
            MsgBox .FoundFiles(i)
        Next i
    Else
        MsgBox "There were no files found."
    End If
End With

Martin

0
 
LVL 1

Expert Comment

by:ssteeves
Comment Utility
I just discovered this problem myself.  I'm noticing that I only have problems with I use a wild card character in the .Filename property.

It almost seems that if you start with a wild card character, as in:
.FileName = "*.*"

it crashes.  But if you start with a character:
.FileName = "o*.*"

it works fine.

0
 

Author Comment

by:MartinEversfield
Comment Utility
Thanks ssteves

That may be the answer, however, all of the files I use are identified with names comprising 8 digits from 00000000.txt to 99999999.txt

  I have devised a solution by writing a VBA function to obtain a directory listing of a folders contents using the output of the DOS DIR command redirected to a text file. I then read the contents of the file using Line Input and obtain the file name by Right String Slicing.

  It isn't very elegant but it works.

Thanks again

Martin
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 1

Expert Comment

by:ssteeves
Comment Utility
Yeah, it wasn't a solution I was trying to provide.  I'm looking for a way around the problem too.  I may end up using that DIR thing as well.

Thanks,

ssteeves
0
 
LVL 1

Accepted Solution

by:
ssteeves earned 100 total points
Comment Utility
Try This:

Dim i as Integer
With Application.FileSearch
  .NewSearch
  .LookIn = "C:\MyFiles\"
  .SearchSubFolders = True
  .FileName = ".txt"
  If .Execute() > 0 Then
    For i = 1 To .FoundFiles.Count
      MsgBox (.FoundFiles(i))
    Next i
  End If
End With

If you leave out the leading * it seems to work.  However, it only allows you to search for one file type at once this way.  In your case (And mine) this may be sufficient.

Hope you fine it helps...

ssteeves
0
 

Author Comment

by:MartinEversfield
Comment Utility
Hi ssteves

   You have solved my problem.  Leaving out the asterix works fine and as I am only searching for .txt files it serves my purpose.  I may have another problem where I use the wildcard ???????? for some searches.  However, you have solved my immediate problem.


Thanks Again

Martin
0
 

Author Comment

by:MartinEversfield
Comment Utility
I believed that the time honoured use of the wildcard asterix to be graven in stone.  I have learned the valuable lesson of looking beyond the end of one's nose.

Martin
0
 
LVL 1

Expert Comment

by:ssteeves
Comment Utility
Glad I could help.  If I happen to stumble upon a solution that allows for wild cards, I will post a follow up.

ssteeves
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now