VBA help to search in the sub folders

abdowadah

New Member
Joined
Dec 20, 2022
Messages
1
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
  2. Mobile
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
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Forum statistics

Threads
1,223,909
Messages
6,175,313
Members
452,634
Latest member
cpostell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top