Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    New Lounger
    Join Date
    Sep 2013
    Posts
    9
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Search files for information contained in Excel cell and return path

    Hi,

    I have an Excel spreadsheet with a filename in column A. The filenames listed in column A appear in one or more Ms office .doc files in one or more source directories.

    I need Excel to search the .doc files recursively and return the path(s) of the file(s) that contain the filename specified in column A into column B. If more than one file go to column C etc.

    I have the macro which does the search file in folder and sub folder and list the path, I want path of those filename which appear in column A

    __|______A_____|______B_____|
    1 | test_1.doc |c:\cost\test_1.doc|
    2 | test_2.doc |c:\cost\test_2.doc|

    Downloaded from internet : This script does the search in folder & sub folder and returns the path of files.
    Code:
    Sub search() 
        Dim myDir As String, myList() 
        With Application.FileDialog(msoFileDialogFolderPicker) 
            If .Show = True Then 
                myDir = .SelectedItems(1) 
            End If 
        End With 
        On Error Resume Next 
        myList = SearchFiles(myDir, "*.doc", 0, myList()) 
        If Err = 0 Then 
            Sheets(1).Cells(2).Resize(UBound(myList, 2), UBound(myList, 1)).Value = _ 
            Application.Transpose(myList) 
        Else 
            MsgBox "No file found" 
        End If 
        On Error Goto 0 
    End Sub 
      
    Private Function SearchFiles(myDir As String _ 
        , myFileName As String, n As Long, myList()) As Variant 
        Dim fso As Object, myFolder As Object, myFile As Object 
        Set fso = CreateObject("Scripting.FileSystemObject") 
        For Each myFile In fso.getfolder(myDir).Files 
            If (Not myFile.Name Like "~$*") * (myFile.Name <> ThisWorkbook.Name) _ 
            * (myFile.Name Like myFileName) Then 
                n = n + 1 
                Redim Preserve myList(1 To 2, 1 To n) 
                myList(1, n) = myDir & "\" & myFile.Name 
                 End If 
        Next 
        For Each myFolder In fso.getfolder(myDir).subfolders 
            SearchFiles = SearchFiles(myFolder.Path, myFileName, n, myList) 
        Next 
        SearchFiles = IIf(n > 0, myList, "") 
    End Function

  2. #2
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    Welcome to the Lounge

    If I understand your question the current Macro is working but it is showing the PATH and FILENAME in COL B, Col C, Etc.

    What you want is Col B, Col C, etc just to show the PATH.

    To do this you should change the following

    Current line myList(1, n) = myDir & "\" & MyFileName
    New Line myList(1, n) = myDir & "\"

    Hope this helps you issue.

    Regards,

    TD
    Last edited by duthiet; 2013-09-19 at 15:14. Reason: Send without full message

  3. #3
    New Lounger
    Join Date
    Sep 2013
    Posts
    9
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Thanks for response, NO actually I need Excel to search the .doc files recursively and return the path(s) of the file(s) that contain the filename specified in column A
    I need to only have those path(s) whose filenames are in Column A.
    Hoping to have a positive reply.

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi Shakir

    I believe what you are asking for is this:
    Starting with a chosen folder,
    ..for each file listed in column [A],
    ..list all folders which contain that file,
    ..using adjacent columns for each folder and subfolder the file is found in.

    If this is what you want, then my attached file does this.

    On completion of the search for the specified files, the column widths will be adjusted automatically to show the folder paths.

    You can adapt the routine as required.

    Please let me know if this does what you want.

    zeddy
    Attached Files Attached Files

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi Shakir

    ..I originally thought your request was to search all documents in folders and subfolders for documents that 'included' the specified filename somewhere in the document!

    zeddy

  6. #6
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi Shakir

    ..so what I think you are really asking for is to list all folders that contain 'copies' of specified files.

    zeddy

  7. #7
    New Lounger
    Join Date
    Sep 2013
    Posts
    9
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by zeddy View Post
    Please let me know if this does what you want.
    zeddy
    You are a genius, Thanks.
    Zeedy one problem with script on testing i found that if suppose a file name which is not there in the folder and it is listed in Column A then the script throws 'No file found' & for all descending list of Colum A whereas i have the other files inside the folder then to it.
    Please have a look to this issue.
    Last edited by shakir; 2013-09-20 at 14:43.

  8. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi shakir

    ..try the attached next version..


    zeddy
    Attached Files Attached Files

  9. The Following User Says Thank You to zeddy For This Useful Post:

    doorjam1 (2013-09-26)

  10. #9
    New Lounger
    Join Date
    Sep 2013
    Posts
    9
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Zeddy, Thanks for response, highly obliged.
    Zeddy, I want to have the filename along with path(s) right now it gives only path, i.e. c:\payday\cost\test.doc ,this will be a complete search.
    Secondly if the extention of filenames to be searched in column A is not available it returns empty result whereas the files are in the folder. Please remove compulsory extention required in Col A.
    Once again thankyou.
    Hope to have a favourable response.

  11. #10
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi Shakir

    ..I'll try and post a solution tomorrow. I've been busy.

    zeddy

  12. #11
    New Lounger
    Join Date
    Sep 2013
    Posts
    9
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Hello,

    I am desperately in need of the codes which needs some changes, please help to get the results. The code fetchs the path and not the file name along the path in Col 'B' secondly if i don't provide the extention in Col 'A' the search returns empty whereas the files are in the folder. Remove the compulsory extention part.

    Col-A_____Col-B
    test1.doc|c:\payday\cost\test1.doc
    test5.doc|c:\payday\cost\test5.doc
    test3.doc|c:\payday\cost\test3.doc
    test6.doc|c:\payday\cost\test6.doc
    test2.doc|:\payday\cost\test2.doc

    Code:
    Private zList() As String
    
    Sub searchFiles()
    'Dim zPath As String
    
    With Application.FileDialog(msoFileDialogFolderPicker)  'use shortcut
    If .Show = True Then                'a folder has been chosen
    zStartFolder = .SelectedItems(1)    'folder to search;
    Else                                'otherwise..
    Exit Sub                            '..nothing else to do
    End If                              'end of test for folder selection
    End With                            'end of shortcut
    
    On Error Resume Next
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    For Each cell In [a1:a100]          'loop through all entries in this defined range
    zFilename = cell.Value              'current file being sought
    zRow = cell.Row                     'row number of current file being sought
    
    If zFilename = "" Then              'reached end of list of filenames, so..
    Cells.EntireColumn.AutoFit          '..make all columns fit
    [a1].Select                         'put cellpointer in tidy location
    End                                 'all done; nothing else to do
    End If                              'end of test for empty filename cell
    
    'PROCEED WITH SEARCH FOR FILE..
    Erase zList                        'clear array for start of file search
    myFileSearch zStartFolder, zFilename, zIncludeSubFolders:=True, zCounter:=0
    
    zCount = UBound(zList)             'e.g. 4
    Sheets(1).Cells(zRow, 2).Resize(1, zCount).Value = zList
    Next                                'process with next filename in column [A]
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    End Sub
    Private Sub myFileSearch(zPath, zFilename, zIncludeSubFolders As Boolean, zCounter As Long)
    
    Dim fso As Object, zSubFolder As Object, myFile As Object
    
    zFetch = zPath & "\" & zFilename
    If Dir(zFetch) <> "" Then
    zCounter = zCounter + 1
    ReDim Preserve zList(1 To zCounter)
    zList(zCounter) = zPath & "\"
    End If
    
    If zIncludeSubFolders Then
    Set fso = CreateObject("Scripting.FileSystemObject")
    For Each zSubFolder In fso.GetFolder(zPath).Subfolders
    myFileSearch zPath & "\" & zSubFolder.Name, zFilename, True, zCounter
    Next
    End If
    
    End Sub

  13. #12
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,823
    Thanks
    135
    Thanked 482 Times in 459 Posts
    Hi Shakir

    I believe what you are now asking for is this:
    Starting with a chosen folder,
    ..for each entry listed in column [A],
    ..list all folders which contain any file that includes the contents specified in col [A] within any filename,
    ..using adjacent columns for each folder and subfolder the file is found in.

    If this is what you want, then my attached file does this.

    On completion of the search for the specified files, the column widths will be adjusted automatically to show the folder paths.

    You can adapt the routine as required.

    Please let me know if this does what you want.

    ..And thank you doorjam1 for your Thanks. I hope you like this newer, flexible version

    zeddy
    Attached Files Attached Files

  14. The Following 3 Users Say Thank You to zeddy For This Useful Post:

    DILiro (2014-02-21),Maudibe (2014-02-21),Sarah Crawford (2016-05-23)

  15. #13
    New Lounger
    Join Date
    Sep 2013
    Posts
    9
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Hello,

    Many Thanks Zeddy, that's perfect.

    Thanks.

  16. #14
    New Lounger
    Join Date
    Feb 2014
    Posts
    1
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Hi,

    This was a good one, thanks zeddy!

    Is it possible to write the results as hyperlinks? So one could open the file from excel with a single click.

    BR,
    DILiro

  17. #15
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,634
    Thanks
    115
    Thanked 649 Times in 592 Posts
    Zeddy, very sweet! I like the way you used an array

    DILiro

    You will need to add the three highlighted in blue lines to Zeddy's code to create the hyperlinks

    Code:
    Private zList() As String
    
    Sub searchFiles()
    'Dim zPath As String
    
    With Application.FileDialog(msoFileDialogFolderPicker)  'use shortcut
    If .Show = True Then                'a folder has been chosen
    zStartFolder = .SelectedItems(1)    'folder to search;
    Else                                'otherwise..
    Exit Sub                            '..nothing else to do
    End If                              'end of test for folder selection
    End With                            'end of shortcut
    
    On Error Resume Next
    
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    For Each cell In [a1:a100]          'loop through all entries in this defined range
    zFilename = cell.Value              'current file being sought
    zRow = cell.Row                     'row number of current file being sought
    
    If zFilename = "" Then              'reached end of list of filenames, so..
    Cells.EntireColumn.AutoFit          '..make all columns fit
    [a1].Select                         'put cellpointer in tidy location
    End                                 'all done; nothing else to do
    End If                              'end of test for empty filename cell
    
    'PROCEED WITH SEARCH FOR FILE..
    Erase zList                        'clear array for start of file search
    myFileSearch zStartFolder, zFilename, zIncludeSubFolders:=True, zCounter:=0
    
    zCount = UBound(zList)             'e.g. 4
    Sheets(1).Cells(zRow, 2).Resize(1, zCount).Value = zList
    For I = 2 To zCount + 1 
        ActiveSheet.Hyperlinks.Add Cells(zRow, I), Cells(zRow, I) 
    Next I 
    Next                                'process with next filename in column [A]
    '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    
    End Sub
    
    
    Private Sub myFileSearch(zPath, zFilename, zIncludeSubFolders As Boolean, zCounter As Long)
    
    Dim fso As Object, zSubFolder As Object, myFile As Object
    Dim zFolder As Object
    
    If Right(zPath, 1) <> "\" Then zPath = zPath & "\"
    
    zMask = "*" & zFilename & "*"
    zSearch = Dir(zPath & zMask)
    
    Do While Len(zSearch) > 0
    zCounter = zCounter + 1
    ReDim Preserve zList(1 To zCounter)
    zList(zCounter) = zPath & zSearch
    zSearch = Dir
    Loop
    
    If zIncludeSubFolders Then
    Set fso = CreateObject("Scripting.FileSystemObject")
    For Each zSubFolder In fso.GetFolder(zPath).Subfolders
    myFileSearch zPath & zSubFolder.Name, zFilename, True, zCounter
    Next
    End If
    
    End Sub

  18. The Following User Says Thank You to Maudibe For This Useful Post:

    DILiro (2014-02-21)

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •