FSO Object

[mrinal saha]

Hi,

I have a code which finds the latest file in a folder T. But What i want is to replace the link in my activeworkbook to the latest file in folder T .
Give me a good code.

Here is the code

Sub link()
Dim fldpath As String, fil As Object, fld As Object, wbcur As Workbook, wbsrch As Workbook, fso As Object

fldpath = "T:\Public\Reporting\Ealing\"
Set wbcur = ActiveWorkbook
Set fso = CreateObject("scripting.filesystemobject")
Set fld = fso.getfolder(fldpath)
lMax = 0
    For Each fil In fld.Files
        If InStr(fil.Name, ".xls") > 0 Then
            If fil.DateCreated > lMax Then
                lMax = fil.DateCreated
                fName = fil.Name
            End If
        End If
    Next fil
MsgBox "Last file created is: " & fName
end sub

Thanks,
Cheers...

 

[nirvana_]

Hope this is what you are looking for.

Sub link()
Dim fldpath As String, fil As Object, fld As Object, wbcur As Workbook, wbsrch As Workbook, fso As Object
fldpath = "T:\Public\Reporting\Ealing\"
Set wbcur = ActiveWorkbook
Set fso = CreateObject("scripting.filesystemobject")
Set fld = fso.getfolder(fldpath)
lMax = 0
    For Each fil In fld.Files
        If InStr(fil.Name, ".xls") > 0 Then
            If fil.DateCreated > lMax Then
                lMax = fil.DateCreated
                ' Change fil.Name to fil.Path
                fname = fil.Path
            End If
        End If
    Next fil
MsgBox "Last file created is: " & fname
' Delete the existing hyperlink
Range("D10").Select
Selection.Hyperlinks.Delete
'Update the new link
Selection.Hyperlinks.Add Anchor:=Selection, Address:=fname, _
        TextToDisplay:="Updated File Name: " & fname
End Sub