Air code, not tested
Sub all_2010()
' Macro to searh & display PDf files
Dim i As Long
dim d1 as string
dim d2 as string
dim d3 as string
dim d4 as string
d1=inputbox("Select year, 1988-2010")
d2=inputbox("Select months, Jan-Dec")
d3=inputbox("Select A or B folder, default to both")
if d3="" then
d3="A-cast"
d4="B-cast"
end if
full_dir="c:\Scans\TW\MAIN" & "\" & d1 & "\" & d2 & "\" & d3
call searchsub(full_dir)
if d4<>"" then
full_dir="c:\Scans\TW\MAIN" & "\" & d1 & "\" & d2 & "\" & d4
call searchsub(full_dir)
end if
end sub
sub searchsub(fulldirectory as string)
With Application.FileSearch
.NewSearch
.SearchSubFolders = True
.Filename = "*.pdf"
.LookIn = fulldirectory
.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
On Wed, Feb 3, 2010 at 3:39 AM, Dave <davidstev...@gmail.com> wrote:
> 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
>
>
> --
>
> ----------------------------------------------------------------------------------
> Some important links for excel users:
> 1. Follow us on TWITTER for tips tricks and links :
> http://twitter.com/exceldailytip
> 2. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
> http://www.excelitems.com
> 3. Excel tutorials at http://www.excel-macros.blogspot.com
> 4. Learn VBA Macros at http://www.quickvba.blogspot.com
> 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
>
>
> To post to this group, send email to excel-macros@googlegroups.com
> If you find any spam message in the group, please send an email to:
> Ayush Jain @ jainayus...@gmail.com
> <><><><><><><><><><><><><><><><><><><><><><>
> HELP US GROW !!
>
> We reach over 6,700 subscribers worldwide and receive many nice notes about
> the learning and support from the group.Let friends and co-workers know they
> can subscribe to group at
> http://groups.google.com/group/excel-macros/subscribe
>
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
http://www.excelitems.com
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain @ jainayus...@gmail.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,700 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
