Solved

FileSearch Application Access97-Windows 2000

Posted on 2002-07-09
9
797 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
ID: 7140853
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
ID: 7142751
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
ID: 7186237
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:MartinEversfield
ID: 7187331
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
 
LVL 1

Expert Comment

by:ssteeves
ID: 7187706
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
ID: 7187782
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
ID: 7190005
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
ID: 7190008
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
ID: 7190385
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

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…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

685 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