Hi Mr. Excel, I was trying to create Macro to search for the values from a list in a specific folder and give the result as hyper link in the same excel, it works fine when the files are in the main file path but when I try to modify it to search in sub folders as well it's not working with me, would please modify this, thanks in advance...
| Sub CreateHyperlink() Dim ws As Worksheet Dim searchRange As String Dim fileName As String Dim filePath As String Dim subFolder As String Dim row As Long Dim lastRow As Long Dim targetCell As Range ' Set the worksheet and file path Set ws = ThisWorkbook.Sheets("TUAE Review") filePath = "\\main.glb.corp.local\Data\RM\AE\DXB\Group\Customer Service\VAT-Export\" ' Get the last row with data in column D lastRow = ws.Cells(ws.Rows.Count, "D").End(xlUp).row ' Loop through each row in the specified range For row = 5 To lastRow ' Get the search range from column D searchRange = ws.Cells(row, "D").Value ' Skip rows with text or empty search range If IsNumeric(searchRange) Then ' Get all files in the specified folder and its subsidiaries fileName = Dir(filePath & "*" & searchRange & "*") subFolder = filePath ' Check if any files were found If Len(fileName) > 0 Then Do While Len(fileName) > 0 ' Set the target cell to be the cell in column L for the current row Set targetCell = ws.Cells(row, "L") ' Create the hyperlink in the target cell targetCell.Hyperlinks.Add anchor:=targetCell, _ Address:=subFolder & fileName, _ TextToDisplay:="Uploaded" ' Get the next file in the folder fileName = Dir subFolder = filePath Loop Else ' If no files were found, set the value of the target cell to "No files" ws.Cells(row, "L").Value = "No files" End If End If Next row End Sub |
