Solved hi i am navin, i need to search a file using macro in excel.

August 8, 2012 at 04:47:50
Specs: Windows XP
hi i need to search a file using macro in excel the name of the file is mention in cells A1,A2...................... so i wanna search that mention files and get the last modified date of paricular files only in the same sheet in cell F1,F2................... can this be done using excel macro, if so can you provide me that coding

See More: hi i am navin, i need to search a file using macro in excel.

Report •

August 9, 2012 at 06:13:10
✔ Best Answer
Here is something i used a long time ago, i have modified it for your requirements, you can try do that yourself. this will give you additional information as well as the last modified date of a file, However this works on a file path rather than file names within a worksheet.

Sub TestListFilesInFolder()
    Workbooks.Add ' create a new workbook for the file list
    ' add headers
    With Range("A1")
        .Formula = "Folder contents:"
        .Font.Bold = True
        .Font.Size = 12
    End With
    Range("A3").Formula = "File Name:"
    Range("B3").Formula = "File Size:"
    Range("C3").Formula = "File Type:"
    Range("D3").Formula = "Date Created:"
    Range("E3").Formula = "Date Last Accessed:"
    Range("F3").Formula = "Date Last Modified:"
    Range("G3").Formula = "Attributes:"
    Range("H3").Formula = "Short File Name:"
    Range("A3:H3").Font.Bold = True
 ListFilesInFolder "C:\", True 
' Change this to your folder location make sure you have the "\" at the end

    ' list all files included subfolders
End Sub

Sub ListFilesInFolder(SourceFolderName As String, IncludeSubfolders As Boolean)
' lists information about the files in SourceFolder
' example: ListFilesInFolder "C:\FolderName\", True
Dim FSO As Scripting.FileSystemObject
Dim SourceFolder As Scripting.Folder, SubFolder As Scripting.Folder
Dim FileItem As Scripting.File
Dim r As Long
    Set FSO = New Scripting.FileSystemObject
    Set SourceFolder = FSO.GetFolder(SourceFolderName)
    r = Range("A65536").End(xlUp).Row + 1
    For Each FileItem In SourceFolder.Files
        ' display file properties
        Cells(r, 1).Formula = TrimedFileName(FileItem.Name)
        Cells(r, 2).Formula = FileItem.Size
        Cells(r, 3).Formula = FileItem.Type
        Cells(r, 4).Formula = FileItem.DateCreated
        Cells(r, 5).Formula = FileItem.DateLastAccessed
        Cells(r, 6).Formula = FileItem.DateLastModified
        Cells(r, 7).Formula = FileItem.Attributes
        Cells(r, 8).Formula = FileItem.ShortPath & FileItem.ShortName

'        FileItem.Copy "C:\FolderName\Filename.txt", True
'        FileItem.Move "C:\FolderName\Filename.txt"
'        FileItem.Delete True
        r = r + 1 ' next row number
    Next FileItem
    If IncludeSubfolders Then
        For Each SubFolder In SourceFolder.SubFolders
            ListFilesInFolder SubFolder.Path, True
        Next SubFolder
    End If
    Set FileItem = Nothing
    Set SourceFolder = Nothing
    Set FSO = Nothing
    ActiveWorkbook.Saved = True
End Sub

Public Function TrimedFileName(nName As String) As String
    Dim nLen As Integer
    Dim endLen As Integer
   nLen = Len(nName)
    For i = nLen To 1 Step -1
        If Mid(nName, i, 1) = "." Then
            endLen = i - 1
            Exit For
        End If
    Next i
    TrimedFileName = Mid(nName, 1, endLen)
    endLen = 0
    nLen = 0
End Function

Report •
Related Solutions

Ask Question