[RESOLVED] Excel VBA FileSearch Different Results

[Torc]

I am using the following piece of code to find the full path where Excel is installed.

Private Function FindExcel() As String
    With Application.FileSearch
        .FileName = "EXCEL.EXE"
        .LookIn = "C:\"
        .SearchSubFolders = True
        .Execute
        If .FoundFiles.Count >= 1 Then
            FindExcel = .FoundFiles(1)
        End If
    End With
End Function

When I run it on my machine I get the following results

C:\Program Files\Microsoft Office\Office10\Excel.exe

However when I run it on a colleagues machine it just returned an
empty string, despite us verifying he had Excel installed in the exact same
place.

When he tried to start Excel by tying it in Start/Run it did not work,
whereas on my machine I could.

Can anyone suggest why it cannot find Excel on his machine.

[malik641]

Hi,

I can't explain what's going on with your colleague's computer, but why don't you use Application.Path to get the path for Excel.exe?

Sub Test()
    Debug.Print Application.Path
End Sub

It's much quicker this way.

Here's some documentation:
http://msdn2.microsoft.com/en-us/lib...fice.11).aspx#

HTH

[malik641]

Also, you shouldn't use Application.FileSearch anyway as it's been removed (or hidden?) from Excel 2007.

[Torc]

Oh the joys of programming without a manual.

Thanks, that should make things much easier to do.

[malik641]

Glad to help Torc!

[PavHabr]

//------------------------------------------------------------------------------------------------

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

//------------------------------------------------------------------------------------------------