I am using Office 2010. I am trying to search and display PDF files in
a excel spreadsheet.I got the following macro with help from this
group, thanks Ashish Koul.
This macro works great I need to modify it some. I want the macro to
be able to search the directory based on answers prompted from the
user. If the user does not provide the answer than I want it to run
the search based on the value entered upto the last question the user
answered. So if the user only entered the year as 2010 I want the
macro to search all sub-directories and display the PDF files below
the year 2010 folder. If they enter the year and the month then I want
the macro to search the sub directory for the month and year combo.
Ann so on
The directory structure on the drive is as follows:
Year
Month
Day
I want the macro to prompt the user for:
Please Select Year (4 digit year like 2010, 2011...)
Please Select Month ( 3 character like Jan , Feb, Mar,Jun.....)
Please Select Day ( Numeric 1 to 30)
The macro is
Public f As Object, d As Object, i As Long
Sub Mcfile_10()
Dim Fso As Object
Set Fso = CreateObject("Scripting.FileSystemObject")
'RootFolder is the main folder
Set RootFolder = Fso.getfolder("\\fileserver\Scans\")
FolderRead RootFolder
End Sub
Sub FolderRead(ByRef myFolder)
For Each f In myFolder.Files
Var = f.Path
If Right(f.Name, 4) = ".pdf" Then
i = i + 1
ActiveSheet.Hyperlinks.Add Anchor:=Range("A" & i), _
Address:=f.Path, TextToDisplay:=f.Name
End If
Next f
For Each d In myFolder.SubFolders
FolderRead d
Next d
End Sub
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
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 [email protected]
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
