Use VBA to create hyperlinks to every file in a directory or folders and subfolders

**Billdick7788** · 09-26-2012, 10:41 AM

I have a directory of folders and subfolders named M:\GPTEAMS\CTT. I want to create a macro that will create hyperlinks to every file in all of the folders and subfolders in this directory. I also would like to see the date last modified as well as the date created. I have been trying to put this together for weeks and I cant seem to figure it out!!! Thank you so much!!

**patel45** · 09-26-2012, 11:42 AM

this macro list all files

Sub foglioDate()
    Call CallTest("M:\GPTEAMS\CTT\")
End Sub

Sub CallTest(sPath As String)
    Dim strPath As String
    Dim sFile() As String
    Dim TotFile, x As Long
    Worksheets("Date").Cells(2, 1).Activate
    TotFile = 0
    strPath = Dir(sPath & "*.*", vbDirectory)
    
    While strPath <> ""
        If strPath <> "." And strPath <> ".." Then
'            If (GetAttr(sPath & strPath) And vbDirectory) = vbDirectory Then
            If GetAttr(sPath & strPath) = vbDirectory Then
                TotFile = TotFile + 1
                ReDim Preserve sFile(TotFile)
                sFile(TotFile) = sPath & strPath & "\"
            Else
                ActiveCell.Value = strPath
                ActiveCell.Offset(0, 1).Value = FileDateTime(sPath & strPath)
                Worksheets("Date").Cells(ActiveCell.Row + 1, 1).Activate
            End If
        End If
        strPath = Dir()
    Wend
    
    If TotFile <> 0 Then
        For x = 1 To TotFile
            Call CallTest(sFile(x))
        Next
    End If
End Sub

**Billdick7788** · 09-26-2012, 11:52 AM

Thanks. I tried this macro and it appears that the lists for every sub folder overwrote the previous subfolder. Also, how can I make these hyperlinks? Thanks!

**patel45** · 09-26-2012, 01:51 PM

there is a mistake

Sub foglioDate()
    arow = 2
    Call CallTest("D:\DATI\prova\", arow)
End Sub

Sub CallTest(sPath As String, arow)
    Dim strPath As String
    Dim sFile() As String
    Dim TotFile, x As Long
    Cells(2, 1).Activate
    TotFile = 0
    strPath = Dir(sPath & "*.*", vbDirectory)
    
    While strPath <> ""
        If strPath <> "." And strPath <> ".." Then
'            If (GetAttr(sPath & strPath) And vbDirectory) = vbDirectory Then
            If GetAttr(sPath & strPath) = vbDirectory Then
                TotFile = TotFile + 1
                ReDim Preserve sFile(TotFile)
                sFile(TotFile) = sPath & strPath & "\"
            Else
                Cells(arow, 1).Value = strPath
                Cells(arow, 2).Value = FileDateTime(sPath & strPath)
                arow = arow + 1
            End If
        End If
        strPath = Dir()
    Wend
    
    If TotFile <> 0 Then
        For x = 1 To TotFile
            Call CallTest(sFile(x), arow)
        Next
    End If
End Sub

Use VBA to create hyperlinks to every file in a directory or folders and subfolders

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Use VBA to create hyperlinks to every file in a directory or folders and subfolders

Re: Use VBA to create hyperlinks to every file in a directory or folders and subfolders

Re: Use VBA to create hyperlinks to every file in a directory or folders and subfolders

Re: Use VBA to create hyperlinks to every file in a directory or folders and subfolders

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions