asked on

VBA Doubt

How to make the following code work in Office 2010. Please edit the code with the suitable replacement for Application.Filesearch

Function LastFileSaved(sFileDir As String, DebutNomFichier As String, Optional sType As String = "*.*") As String

Dim fs
Set fs = Application.FileSearch
With fs
    .LookIn = sFileDir
    .Filename = DebutNomFichier
    
    If .Execute(SortBy:=msoSortByLastModified, _
        SortOrder:=msoSortOrderDescending) > 0 Then
        LastFileSaved = .FoundFiles(1)
    Else
        MsgBox "There were no files found in:" & vbLf & _
        sFileDir & vbLf & _
        "Type: " & sType
      LastFileSaved = vbNullString
    End If
End With

End Function


Function FileExist(Chemin, NomFichier) As Boolean
    
    ' Ne marche plus avec Excel 2007
    Set fs = Application.FileSearch
    With fs
        Resultat = False
        .LookIn = Chemin
        .Filename = NomFichier
        If .Execute > 0 Then FileExist = True Else FileExist = False
    End With
    
End Function

Open in new window

ASKER CERTIFIED SOLUTION

mbizup

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Arno Koster

You should be able to use

Function FileExist(Chemin, NomFichier) As Boolean
    FileExist = (Dir(Chemin & "\" & NomFichier) <> "")
End Function

Open in new window

this works in office 2007

VBAlearner2010

ASKER

@mbizup & akoster:: Is this the replacement for both the functions? Last file saved and File exists?

Will it work if i replace both these functions with the one function?
Please advise since I am very new and not getting hold in coding yet. It will be very kind if you can explain me what the line does..

mbizup

No - Just for FileExist.

These are really two seperate questions...

mbizup

Both of our code blocks use the Dir function to check for a file in a directory. Mine checks for the existence of a backslash character at the end of the folder (chemin) if it is not there, it adds it. This is why it is a little more verbose, but our posts basically do the same thing.

VBAlearner2010

ASKER

Ok i will post last file saved as a seperate question. Thanks for this :)

SOLUTION

Arno Koster

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

VBAlearner2010

ASKER

@akoster: I get an error User defined or type defined error on the line "Set fso = New FileSystemObject"

Should i add any reference because when i hit space after AS when declaring i dont see FilesystemObject in the list.

VBAlearner2010

ASKER

Kindly let me know asap... I will have to deliver this app today.

VBAlearner2010

ASKER

I added Microsoft scripting Runtime to reference.. And it compiled.. Thanks for the help.

VBAlearner2010

ASKER

Thanks for the help