Application.Filesearch does not work in Access 2007, workaround? (1 Viewer)

Hey guys. For a project at work I have to use Access 2007 because I need a Rich Text box on my reports. Do not ask why or tell me I should use something different becuase neither are happening. I have some code for printing pdfs from the reports and Access 07 is hung up on the application.filesearch method because it apparently does not exist anymore. I have looked for some kind of reference to older access stuff that I could add but one does not exist. Anyone know of any alternative or a way to get it to work?

Rob

02bluesuperroo said:

...Access 07 is hung up on the application.filesearch method because it apparently does not exist anymore.

Click to expand...

Check your references...in Access '03, the Application.FileSearch object is part of the Microsoft Office 11.0 Object Library so it's most probably in whatever version of the office object library that is available in '97

Bodisathva said:

Check your references...in Access '03, the Application.FileSearch object is part of the Microsoft Office 11.0 Object Library so it's most probably in whatever version of the office object library that is available in '97

Click to expand...

I'm not using '97, i'm using '07. I have looked through all the references and there are none that are for any version of access.

02bluesuperroo said:

I'm not using '97, i'm using '07. I have looked through all the references and there are none that are for any version of access.

Click to expand...

hmmm... a. I wasn't aware that the name had shifted from Access 12 to Access 2007, and b. I thought Access 12 was still in the beta phase

never mind...

Bodisathva said:

hmmm... a. I wasn't aware that the name had shifted from Access 12 to Access 2007, and b. I thought Access 12 was still in the beta phase

never mind...

Click to expand...

It is still in beta but I HAVE to have a rich text box on my reports and this was the easiest/cheapest way to do it.

Edit: Also, it is Office 2007, not 12 now.

Since the Application.FileSearch method is depreciated or hidden for 2007, try the File System Object's FileExists method. This could actually accomplish the same thing in just a few more lines of code if you do it recursively.

The references SHOULD show: Microsoft Access 2007 (Beta) Object Library. If that is missing, that could be causing your problem.

02bluesuperroo said:

Hey guys. For a project at work I have to use Access 2007 because I need a Rich Text box on my reports. Do not ask why or tell me I should use something different becuase neither are happening. I have some code for printing pdfs from the reports and Access 07 is hung up on the application.filesearch method because it apparently does not exist anymore. I have looked for some kind of reference to older access stuff that I could add but one does not exist. Anyone know of any alternative or a way to get it to work?

Rob

Click to expand...

I realise it's something of a late response - you probably know by now that Application.Filesearch was deprecated in Office 2007. The scripting object is great if you were just checking folders for files - if you want something that checks subfolders and allows some of the other things that Application.Filesearch did, Codematic have a module that you can just copy-paste into your code and use instead. You have to pay for it but it's not much. It's in the "resources" bit on their site.

Mark

Yes, the method has been removed from Access 2007. The preferred method is to use the file system object.

Use the following:

'===============
Sub GetFIleNames (StrPath)
Dim FolderLength As Integer
Dim fName As String
Dim objFSO As FileSystemObject
Dim objFolder As Folder
Dim objFile As File

Set objFSO = New FileSystemObject
Set objFolder = objFSO.GetFolder(StrPath)

For Each objFile In objFolder.Files
FolderLength = Len(StrPath) + 1
fName = Mid(objFile, FolderLength)

'Do your thing here ...
You can set a watch on objFile to read its value, debug.print it out or
do whatever else floats your boat []

Next objFile

end sub

'===============

Sean

My client uses FileSearch, NewSearch, MatchAllWordForms, LookIn, FileType, TextOrProperty, Execute.

This looks inside all documents in the specified folder for a keyword supplied by the caller.

So I need more that what FileSystemObject can provide.

Is it possable that MS has improved things by removing some features - I thought the idea was just to make them harder to accomplish.

I ran into this problem, too. I needed to TransferSpreadsheet multiple files w/ Access 2007, but couldn't find a way to do it w/o FileSearch....Until I came across this. I don't know if this will help the original issue, but it works fine.

The code was listed here.

'!!!!!!!!!!!!! Replacement solution including searching in subdirectories !!!!!!


//------------------------------------------------------------------------------------------------
Sub FileSearchByHavrda_Example_of_procedure_calling()
'
' Example of FileSearchByHavrda procedure calling as replacement of missing FileSearch function in the newest MS Office VBA
' 01.06.2009, Author: P. Havrda, Czech Republic
'
Dim FileNameWithPath As Variant
Dim ListOfFilenamesWithParh As New Collection ' create a collection of filenames
' Filling a collection of filenames (search Excel files including subdirectories)
Call FileSearchByHavrda(ListOfFilenamesWithParh, "C:\Temp", "*.xls", True)
' Print list to immediate debug window and as a message window
For Each FileNameWithPath In ListOfFilenamesWithParh ' cycle for list(collection) processing
Debug.Print FileNameWithPath & Chr(13)
MsgBox FileNameWithPath & Chr(13)
Next FileNameWithPath
' Print to immediate debug window and message if no file was found
If ListOfFilenamesWithParh.Count = 0 Then
Debug.Print "No file was found !"
MsgBox "No file was found !"
End If
End Sub
//------------------------------------------------------------------------------------------------
Private Sub FileSearchByHavrda(pFoundFiles As Collection, pPath As String, pMask As String, pIncludeSubdirectories As Boolean)
'
' Search files in Path and create FoundFiles list(collection) of file names(path included) accordant with Mask (search in subdirectories if enabled)
' 01.06.2009, Author: P. Havrda, Czech Republic
'
Dim DirFile As String
Dim CollectionItem As Variant
Dim SubDirCollection As New Collection
' Add backslash at the end of path if not present
pPath = Trim(pPath)
If Right(pPath, 1) <> "\" Then pPath = pPath & "\"
' Searching files accordant with mask
DirFile = Dir(pPath & pMask)
Do While DirFile <> ""
pFoundFiles.Add pPath & DirFile 'add file name to list(collection)
DirFile = Dir ' next file
Loop
' Procedure exiting if searching in subdirectories isn't enabled
If Not pIncludeSubdirectories Then Exit Sub
' Searching for subdirectories in path
DirFile = Dir(pPath & "*", vbDirectory)
Do While DirFile <> ""
' Add subdirectory to local list(collection) of subdirectories in path
If DirFile <> "." And DirFile <> ".." Then If ((GetAttr(pPath & DirFile) And vbDirectory) = 16) Then SubDirCollection.Add pPath & DirFile
DirFile = Dir 'next file
Loop
' Subdirectories list(collection) processing
For Each CollectionItem In SubDirCollection
Call FileSearchByHavrda(pFoundFiles, CStr(CollectionItem), pMask, pIncludeSubdirectories) ' Recursive procedure call
Next
End Sub
//------------------------------------------------------------------------------------------------

If you haven't already gotten an answer to the workaround for application.filesearch,try this link:

http://support.microsoft.com/kb/935402

It provides links to articles to work around application.filesearch to find files in directories using either the Dir functoin or the FileSystemObject class. I solved my provlem using the Dir function.