Help on a macro please

[Dave]

I have the code listed below that searches a folder and if it finds
any
PDF files its displays them with a hyperlink in the spreadsheet. My
problem is that under the MAIN folder I have tons of files now and
its
very hard to find the PDF I am looking for.

I redid my file structure under main and now I have folders for years
2010,
2012,2013,2014,2015 and so on. Under each years directory are the
directories for the 12 months of the year & under each of these
months
I have two folders A-cast & B-cast.


What I would like is that the code could search the folders starting
form MAIN by prompting the user for the year, & than the month & then
the folder name And then display contents of the folder as results
on the spreadsheet If a user did not have lets say the folder names
A-
cast & B-cast the
program could display contents of both A-cast & B-cast.


Here is the code I got from the forum.


Sub all_2010()
' Macro to searh & display PDf files
Dim i As Long
With Application.FileSearch
.NewSearch
.SearchSubFolders = True
.Filename = "*.pdf"
.LookIn = "c:\Scans\TW\MAIN"
.Execute
For i = 1 To .FoundFiles.Count
ActiveSheet.Hyperlinks.Add Anchor:=Range("A" & i), _
Address:=.FoundFiles(i), TextToDisplay:= _
Right(.FoundFiles(i), Len(.FoundFiles(i)) - _
InStrRev(.FoundFiles(i), "\"))
Next
End With
End Sub

 

[Daryl S]

Dave -

This sounds like it should be in the Excel newsgroup, not the Access
newsgroup.

To approach this problem, you need to adjust your code to use a variable for
the path to look in. Then you need to build this variable, and use that in
your .LookIn statement. Here is the basic idea, but you will need to change
the items in <>s to be the data from your user interface.

Sub all_2010()
' Macro to searh & display PDf files
Dim i As Long
Dim LookInPath As string

LookInPath = "c:\Scans\TW\MAIN\" & <YearFolder> & "\" & <MonthFolder>
If right(<foldername>,4) = "cast" Then
LookInPath = LookInPath & "\" & <foldername>
End If

With Application.FileSearch
.NewSearch
.SearchSubFolders = True
.Filename = "*.pdf"
.LookIn = LookInPath
.Execute
For i = 1 To .FoundFiles.Count
ActiveSheet.Hyperlinks.Add Anchor:=Range("A" & i), _
Address:=.FoundFiles(i), TextToDisplay:= _
Right(.FoundFiles(i), Len(.FoundFiles(i)) - _
InStrRev(.FoundFiles(i), "\"))
Next
End With
End Sub