none
Excel Hyperlink Help RRS feed

  • Question

  • I am looking for a way (macro) to make a hyperlink, that when clicked, will do a search for the contents in the excel block to a folder/subfolders.

    Lets say my hyperlink in excel is called Apples.  When clicked, I want it to search a folder called Fruit and all subfolders automatically for files named apples.  I only need the files display in explorer, like it shows using the explorer search function.  Don't need, and would prefer the files not be opened, just the file names displayed.

    Is it possible to automate the search to autofill with the contents of the cell?  This would save an enormous amount of time.

    Also, the file names will be around 49 digits, but my search will need to be on the first 25 digits.  Can the search be modified to only capture that part of the text?

    Basically, I need the hyperlink file when clicked, to open windows explorer and display any files like the explorer search feature.

    Hope I explained it enough.

    Thanks for your help

    Friday, June 16, 2017 4:36 PM

Answers

  • You can use a macro - which could be fired by the selection change event rather than run manually.  Use this in a standard codemodule, and set a reference in your project to MS Scripting Runtime. This code will output the list of file information to columns A and B of the first worksheet in the workbook with the code, but you can do whatever you want with the list.

    Option Explicit

    Dim FSO As Scripting.FileSystemObject
    Dim lngR As Long
    Dim strFNFilter As String

    Sub ListFiles()
        strFNFilter = "*" & ActiveCell.Value & "*"
        FindFilesFromFolders ThisWorkbook.Path '"C:\Fruit"     'Change to your actual folder
    End Sub

    Sub FindFilesFromFolders(strFolderName As String)
        Dim fsoFolder As Scripting.Folder

        If FSO Is Nothing Then
            Set FSO = New Scripting.FileSystemObject
        End If
        
        With ThisWorkbook.Worksheets(1)
            .Range("A:B").ClearContents
            .Cells(1, "A").Value = "Folder Name"
            .Cells(1, "B").Value = "File Name"
        End With
        
        lngR = 2
        
        Set fsoFolder = FSO.GetFolder(strFolderName)
        FindFiles fsoFolder
        
        ThisWorkbook.Worksheets(1).Range("A:B").EntireColumn.AutoFit
        
    End Sub

    Sub FindFiles(fsoPFolder As Scripting.Folder)
        Dim fsoFile As Scripting.File
        Dim fsoSFolder As Scripting.Folder
        Dim wkbkW1 As Workbook

        For Each fsoFile In fsoPFolder.Files
            If fsoFile.Name Like strFNFilter Then
                With ThisWorkbook.Worksheets(1)
                    .Cells(lngR, "A").Value = fsoPFolder.Path
                    .Cells(lngR, "B").Value = fsoFile.Name
                End With
                lngR = lngR + 1
            End If
        Next fsoFile

        For Each fsoSFolder In fsoPFolder.SubFolders
            FindFiles fsoSFolder
        Next fsoSFolder

    End Sub


    Friday, June 16, 2017 5:11 PM
  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel features, I'll move your question to the MSDN forum for Excel

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.


    Regards,
    Emi Zhang
    TechNet Community Support

    Please remember to mark the replies as answers if they helped.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    • Marked as answer by Joe191919 Monday, June 19, 2017 1:08 PM
    Monday, June 19, 2017 3:02 AM

All replies

  • You can use a macro - which could be fired by the selection change event rather than run manually.  Use this in a standard codemodule, and set a reference in your project to MS Scripting Runtime. This code will output the list of file information to columns A and B of the first worksheet in the workbook with the code, but you can do whatever you want with the list.

    Option Explicit

    Dim FSO As Scripting.FileSystemObject
    Dim lngR As Long
    Dim strFNFilter As String

    Sub ListFiles()
        strFNFilter = "*" & ActiveCell.Value & "*"
        FindFilesFromFolders ThisWorkbook.Path '"C:\Fruit"     'Change to your actual folder
    End Sub

    Sub FindFilesFromFolders(strFolderName As String)
        Dim fsoFolder As Scripting.Folder

        If FSO Is Nothing Then
            Set FSO = New Scripting.FileSystemObject
        End If
        
        With ThisWorkbook.Worksheets(1)
            .Range("A:B").ClearContents
            .Cells(1, "A").Value = "Folder Name"
            .Cells(1, "B").Value = "File Name"
        End With
        
        lngR = 2
        
        Set fsoFolder = FSO.GetFolder(strFolderName)
        FindFiles fsoFolder
        
        ThisWorkbook.Worksheets(1).Range("A:B").EntireColumn.AutoFit
        
    End Sub

    Sub FindFiles(fsoPFolder As Scripting.Folder)
        Dim fsoFile As Scripting.File
        Dim fsoSFolder As Scripting.Folder
        Dim wkbkW1 As Workbook

        For Each fsoFile In fsoPFolder.Files
            If fsoFile.Name Like strFNFilter Then
                With ThisWorkbook.Worksheets(1)
                    .Cells(lngR, "A").Value = fsoPFolder.Path
                    .Cells(lngR, "B").Value = fsoFile.Name
                End With
                lngR = lngR + 1
            End If
        Next fsoFile

        For Each fsoSFolder In fsoPFolder.SubFolders
            FindFiles fsoSFolder
        Next fsoSFolder

    End Sub


    Friday, June 16, 2017 5:11 PM
  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel features, I'll move your question to the MSDN forum for Excel

    https://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.


    Regards,
    Emi Zhang
    TechNet Community Support

    Please remember to mark the replies as answers if they helped.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    • Marked as answer by Joe191919 Monday, June 19, 2017 1:08 PM
    Monday, June 19, 2017 3:02 AM