Thank You So Much!!!
It works great
On Feb 3, 8:58 pm, KC Cheung <cheung.kc...@gmail.com> wrote:
> add one more dim statement as below
> Dim d1 As String
> Dim d2 As String
> Dim d3 As String
> Dim d4 As String
> Dim full_dir As String
>
>
>
> On Thu, Feb 4, 2010 at 12:44 AM, Dave <davidstev...@gmail.com> wrote:
> > Thanks for the help I am getting a compile error when I run the code.
> > the error is "ByRef argument type mismatch" & it highlights Call
> > searchsub(full_dir)
>
> > On Feb 2, 8:20 pm, KC Cheung <cheung.kc...@gmail.com> wrote:
> > > 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 athttp://www.excel-macros.blogspot.com
> > > > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> > > > 5. Excel Tips and Tricks athttp://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-Hide quoted text
> > -
>
> > > - Show quoted text -
>
> > --
>
> > ----------------------------------------------------------------------------------
> > 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 athttp://www.excel-macros.blogspot.com
> > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> > 5. Excel Tips and Tricks athttp://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- Hide quoted text -
>
> - Show quoted text -
--
----------------------------------------------------------------------------------
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
