Friday, January 11, 2013

How to Display Latest Modified File Name in a Specified Path using VBA Macro

Excel VBA Macro to display the Latest Modified File Name in a Specified Path
Suppose You have Copied a File from a Location to Your Desired Path or You have Modified a File in Your Desired Path , Up to so long time you didn't do any modifications in that Path, Now after so long time You want to know the File Name That You Modified Latest , Now You can find it , by using this Macro.

Note: 
This Macro will show only the latest Modified .i.e.the file that you modified recently.
-----------------------------------------------------------------------------------
Model- I :
Sub Latest_Modified_File()

Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim StrPath As String
Dim StrName As String
Dim LModDate As Date

On Error GoTo Label1:

' Specifying File Path To Find Latest Modified File:

StrPath = InputBox("Enter Path of Latest Modified File To Open " & vbNewLine _
& "Eg: D:\Excel_VBA ", "File Path")

' Using Microsoft Scripting Runtime.
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(StrPath)

' Check date of each File in Folder:
For Each objFile In objFolder.Files
If objFile.DateLastModified > LModDate Then

LModDate = objFile.DateLastModified

StrName = objFile.Name
End If
Next 'objFile

' Displaying Latest Modified File name in Specified Path:
MsgBox StrName & " - Is The latest File Modified On = " & LModDate & vbNewLine _
& "Doucment Path:" & StrPath, vbInformation, "Lastest Modified Document"

Set objFSO = Nothing
Set objFolder = Nothing
Set objFile = Nothing

Label1:
End Sub
-----------------------------------------------------------------------------------

Model- II :
Sub Latest_Modified_File()

Dim StrFile As String
Dim StrPath As String
Dim LatDt As Date
Dim LMFile As String

StrPath = InputBox("Enter Path of Latest Modified File" & vbNewLine _
& "Eg: D:\Excel_VBA\ ", "File Path")

StrFile = Dir(StrPath & "*.*", vbNormal)

Do While StrFile <> ""
      If FileDateTime(StrPath & StrFile) > LatDt Then
           LatDt = FileDateTime(StrPath & StrFile)
           LMFile = StrFile
      End If
   StrFile = Dir
Loop

MsgBox "Last Modified File is : " & LMFile & " , " & "Modified On :" & LatDt

End Sub

Note:
This Macro will show only the latest Modified .i.e.the file that you modified recently.

-------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional 
--------------------------------------------------------------------------------------------------------

No comments:

Post a Comment

Hi User, Thank You for visiting My Blog. Please post your genuine Feedback or comments only related to this Blog Posts. Please do not post any Spam comments or Advertising kind of comments which will be Ignored.

Featured Post from this Blog

How to compare Current Snapshot Data with Previous Snapshot in Power BI

How to Dynamically compare two Snapshots Data in Power BI Scenario: Suppose, we have a sample Sales data, which is stored with Monthly Snaps...

Popular Posts from this Blog