hyperlink to listed files

[tass]

Hi experts!

I have the following code which searches for xls-files in a given path and lists me filename, path, sheet names and the content of cells a1 and a2 (titel) in an excel table.

What I need is, that the path appears as hyperlink opening directly the file it refers to. How could I include that in this code?

Thank you and best wishes.

Sub metadata1()
    Dim wsOut As Worksheet
    Dim zOut As Long
    Dim xOut As Long
    Dim ws As Worksheet, WB As Workbook
    Dim varFS
    Dim i
   
    Set wsOut = ThisWorkbook.Worksheets(1)
    wsOut.Cells.Clear ' <--Achtung ;-)
    zOut = 1
    
    Sheets("Tabelle1").Range("A1").Value = "File name"
    Sheets("Tabelle1").Range("b1").Value = "Path"
    Sheets("Tabelle1").Range("c1").Value = "Worksheet1 name"
    Sheets("Tabelle1").Range("d1").Value = "Worksheet1 titel"
    Sheets("Tabelle1").Range("e1").Value = "Worksheet2 name"
    Sheets("Tabelle1").Range("f1").Value = "Worksheet2 titel"
    Sheets("Tabelle1").Range("g1").Value = "Worksheet3 name"
    Sheets("Tabelle1").Range("h1").Value = "Worksheet3 titel"
    Sheets("Tabelle1").Range("i1").Value = "Worksheet4 name"
    Sheets("Tabelle1").Range("j1").Value = "Worksheet4 titel"
    Sheets("Tabelle1").Range("k1").Value = "Worksheet5 name"
    Sheets("Tabelle1").Range("l1").Value = "Worksheet5 titel"
    Sheets("Tabelle1").Range("m1").Value = "Worksheet6 name"
    Sheets("Tabelle1").Range("n1").Value = "Worksheet6 titel"
    Sheets("Tabelle1").Range("o1").Value = "Worksheet7 name"
    Sheets("Tabelle1").Range("p1").Value = "Worksheet7 titel"
    Sheets("Tabelle1").Range("q1").Value = "Worksheet8 name"
    Sheets("Tabelle1").Range("r1").Value = "Worksheet8 titel"
    Sheets("Tabelle1").Range("s1").Value = "Worksheet9 name"
    Sheets("Tabelle1").Range("t1").Value = "Worksheet9 titel"
    Sheets("Tabelle1").Range("u1").Value = "Worksheet10 name"
    Sheets("Tabelle1").Range("v1").Value = "Worksheet10 titel"
    
    Application.ScreenUpdating = False
   
    Set varFS = Application.FileSearch
    With varFS
        .LookIn = "H:\BFPs\Data CDs\modified files for metasearch"
        .Filename = "*.xls"
        .SearchSubFolders = True
        If .Execute > 0 Then
            For i = 1 To .FoundFiles.Count
                Set WB = Workbooks.Open(varFS.FoundFiles(i), UpdateLinks:=0)
                zOut = zOut + 1
                wsOut.Cells(zOut, 1) = WB.Name
                wsOut.Cells(zOut, 2) = WB.Path & "\" & WB.Name
                xOut = 3
                For Each ws In WB.Worksheets
                    wsOut.Cells(zOut, xOut) = "WS " & ws.Index & ":" & ws.Name
                    xOut = xOut + 1
                    wsOut.Cells(zOut, xOut) = Cells(1, 1) & " - " & Cells(2, 1)
                    xOut = xOut + 1
                    'usw
                Next
                WB.Close savechanges:=False
            Next i
        End If
    End With 'varFS
    Application.ScreenUpdating = True
End Sub

 

[mike31z]

If you have the location of the files in a cell (Column), Why not use the Hyperlink () formula in a third cell (column) to complete the process?

 

[tass]

Hey mike31z,

I had this idea, but thought that if it was easy to link it in the same cell it would be a more elegant solution. However, it seems its not so easy, and probably in the end that is, what I will have to do.

Thanks so far...