MAcro to search multiple files in Folder and its subfolders and extracting their name and locatiey are on.

pulkit bahl

New Member
Joined
Dec 7, 2012
Messages
7
HI
I want to search a group of file names in a folder and its sub folders.
I need to know where they are located and the full name.
and files searched should be as per format selected.
Regards
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
hi
this is what was shared here only in one of the forums
this gives the hyperlink to all the files in the directory.
I need it to be selective.


like if i have to search some files
say:
aa
bb
cc
dd
ee
ff
gg
hh

in a directory it should search it and give me the hypertext

Sub HyperlinksToDirectory()

Dim stDir As String
Dim stFile As String
Dim colFiles As New Collection
Dim R As Range
Set R = ActiveCell
stDir = InputBox("Directory?", , Default:=CurDir())
'stFile = Dir(stDir & "\*.*")
RecursiveDir colFiles, stDir, "*.doc*", True
Dim vFile As Variant
For Each vFile In colFiles
R.Hyperlinks.Add R, vFile, , , vFile
Set R = R.Offset(1)
Next vFile


R.CurrentRegion.Sort key1:=R, order1:=xlAscending, Header:=xlNo
End Sub
Public Function RecursiveDir(colFiles As Collection, _
strFolder As String, _
strFileSpec As String, _
bIncludeSubfolders As Boolean)

Dim strTemp As String
Dim colFolders As New Collection
Dim vFolderName As Variant

'Add files in strFolder matching strFileSpec to colFiles
strFolder = TrailingSlash(strFolder)
strTemp = Dir(strFolder & strFileSpec)
Do While strTemp <> vbNullString
colFiles.Add strFolder & strTemp
strTemp = Dir
Loop

If bIncludeSubfolders Then
'Fill colFolders with list of subdirectories of strFolder
strTemp = Dir(strFolder, vbDirectory)
Do While strTemp <> vbNullString
If (strTemp <> ".") And (strTemp <> "..") Then
If (GetAttr(strFolder & strTemp) And vbDirectory) <> 0 Then
colFolders.Add strTemp
End If
End If
strTemp = Dir
Loop

'Call RecursiveDir for each subfolder in colFolders
For Each vFolderName In colFolders
Call RecursiveDir(colFiles, strFolder & vFolderName, strFileSpec, True)
Next vFolderName
End If

End Function

Public Function TrailingSlash(strFolder As String) As String
If Len(strFolder) > 0 Then
If Right(strFolder, 1) = "\" Then
TrailingSlash = strFolder
Else
TrailingSlash = strFolder & "\"
End If
End If
End Function




urgently need it
thanks
 
Upvote 0
Hi thanks for suggesting file mapping ..
But here i hav eto extract for a particular set not all the files of particular format.


I want search a gien set of files each time and its link
 
Upvote 0
The file mapping one show you how to identify all the files in the directory. Idf you only want specific files, either put in an If statement, ignoring any files not on a list, or array, or use the lsit or array to only load specific files.

file.path and file.name will give you the details you want, you just need to finetune to the information from the mapping to get it to work.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

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