[Solved] Update list of files from Folder with Hyperlink

[mfaisal.ce]

Dear,

I need to make an excel file which have list of all the files from a specific folder with hyperlink. In addition with an update button, to update whenever a new file is added in that particular folder.

Regards,

 

[Lutfor Rahman Shimanto]

Dear,

I need to make an excel file which have list of all the files from a specific folder with hyperlink. In addition with an update button, to update whenever a new file is added in that particular folder.

Regards,

Thank you for bringing this issue to our attention, Mfaisal.ce. I understand you have been experiencing difficulties creating an excel file listing all the files from a specific folder with hyperlinks. In addition, with an update button to update whenever a new file is added to that particular folder. Regarding your query, you can use the following VBA code to insert a specific folder and to get all files with hyperlinks within that folder.

Public FolderPath As String

Sub GetFileNamesWithHyperlinks()
 
    Dim FileName As String
    Dim i As Integer
 
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select a Folder"
        .AllowMultiSelect = False
        If .Show = True Then
            FolderPath = .SelectedItems(1)
 
        End If
    End With 
 
    Range("A2:B" & Range("B" & Rows.Count).End(xlUp).Row).ClearContents
 
    FileName = Dir(FolderPath & "\*.*")
 
    i = 4
    Do While FileName <> ""
        ActiveSheet.Hyperlinks.Add Anchor:=Cells(i, 2), Address:=FolderPath & "\" & FileName, TextToDisplay:=FileName
        i = i + 1
        FileName = Dir()
    Loop
    Range("B2").Value = "File Names with Hyperlinks from A Specific Folder"
 
End Sub

Sub Update()

    Range("A2:B" & Range("B" & Rows.Count).End(xlUp).Row).ClearContents
 
    FileName = Dir(FolderPath & "\*.*")
 
    i = 4
    Do While FileName <> ""
        ActiveSheet.Hyperlinks.Add Anchor:=Cells(i, 2), Address:=FolderPath & "\" & FileName, TextToDisplay:=FileName
        i = i + 1
        FileName = Dir()
    Loop
    Range("B2").Value = "File Names with Hyperlinks from A Specific Folder"

End Sub

You have to build two buttons titled GET FOLDER and UPDATE. You must assign the GetFileNamesWithHyperlinks to the GET FOLDER button and the Update macro to the UPDATE button. I've attached the spreadsheet I used to explore your issue for clarity.

Input: In the shown folder, there are several files.

​

Output: The GET FOLDER button lets you choose the folder and retrieves all the file names. In contrast, the UPDATE button sends file names from the previous folder. That's why technically, we must use the GET FOLDER button first.

​

Please do not hesitate to contact us with any additional questions or problems regarding Excel. We are here to assist you with any Excel-related issues you may experience.

Regards
Lutfor Rahman Shimanto

 

[mfaisal.ce]

Dear,

Many thanks for your support and help. What I understood from your solution you provided and looking at the code;
for every new file added in column , I have to press update button to update the list but in code it is clearing the whole sheet and then adding from start. In this way, if i have a lot of files suppose 1000 or more, it will take much every time during updating. correct me if I am wrong.

Regards,

 

[Lutfor Rahman Shimanto]

Dear,

Many thanks for your support and help. What I understood from your solution you provided and looking at the code;
for every new file added in column , I have to press update button to update the list but in code it is clearing the whole sheet and then adding from start. In this way, if i have a lot of files suppose 1000 or more, it will take much every time during updating. correct me if I am wrong.

Regards,

Dear Mfaisal.ce,

Thank you for bringing up your concerns about the previous code I provided for updating the list of files in the Excel sheet. You are absolutely correct in pointing out that the previous code would have been time-consuming for large datasets.

I have considered your feedback and modified the Update sub-procedure accordingly. This time, I have named the updated sub-procedure as UpdateNewFiles and assigned it to the UPDATE button. I have also kept the GetFileNamesWithHyperlinks subroutine as it is. I am attaching the updated Workbook and modified code to address the issue.

Public FolderPath As String

Sub GetFileNamesWithHyperlinks()
    
    Dim FileName As String
    Dim i As Integer
    
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select a Folder"
        .AllowMultiSelect = False
        If .Show = True Then
            FolderPath = .SelectedItems(1)
        End If
    End With
    
    Range("A2:B" & Range("B" & Rows.Count).End(xlUp).Row).ClearContents
    
    FileName = Dir(FolderPath & "\*.*")
    
    i = 4
    Do While FileName <> ""
        ActiveSheet.Hyperlinks.Add Anchor:=Cells(i, 2), Address:=FolderPath & "\" & FileName, TextToDisplay:=FileName
        i = i + 1
        FileName = Dir()
    Loop
    Range("B2").Value = "File Names with Hyperlinks from A Specific Folder"
    
End Sub

Sub UpdateNewFiles()

    Dim lastRow As Long
    lastRow = Range("B" & Rows.Count).End(xlUp).Row

    Dim existingFiles() As Variant
    existingFiles = Range("B2:B" & lastRow).Value

    Dim FileName As String
    FileName = Dir(FolderPath & "\*.*")
    
    Dim i As Long
    i = lastRow + 1
    
    Do While FileName <> ""
        Dim found As Boolean
        found = False
        For j = 1 To UBound(existingFiles, 1)
            If existingFiles(j, 1) = FileName Then
                found = True
                Exit For
            End If
        Next j
        If Not found Then
            ActiveSheet.Hyperlinks.Add Anchor:=Cells(i, 2), Address:=FolderPath & "\" & FileName, TextToDisplay:=FileName
            i = i + 1
        End If
        FileName = Dir()
    Loop

End Sub

In the UpdateNewFiles sub-procedure, the code first retrieves the existing file names from the Excel sheet and stores them in an array called existingFiles. Then, it searches for any new files in the specified folder and adds them to the Excel sheet only if they are not already in the existingFiles array. The modified code reduces the processing time significantly for larger datasets as it only updates the new files.

Thank you again for your valuable concern. Please let me know if you need any further help or feedback.

Best regards,
Lutfor Rahman Shimanto

 

[mfaisal.ce]

Really Thanks dear...