Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Project: Filesearch drop results into a worksheet

    I have a mini-project I would like to build as a utility to help support some work I do for current and future projects, but I'm not quite sure how to start. I appologize that I do not have time right now to provide some code to show how I would start this project (I am a relative newby to VBA, but I'm learning quickly). I thought I'd throw this "out there" and see what happens. If anyone has ideas and/or answers to my questions below, that would get me started. Thanx.

    MY MINI-PROJECT:
    I would like to return the results of a filesearch (multiple files) into a worksheet including the filename, path, filetype (if possible) and the update timestamp. This is similar to the results of the find-files tool in Windows Explorer, except that I would like to have these results in Excel so they can be manipulated.

    Ideally, I would like to use the update timestamp of files to narrow the search criteria (return only those files that have been modified within the last ten days, two months, or within a certain range of dates). I also would like to use wildcard characters in the search string. In my limited experience with FileSearch, I have had nothing but bad luck. I can't figure out how to use wildcards (is it possible?) I also would like to search the contents of the .Lookin directory as well as any child directories.

    It would also be ideal if the user could specify the name of the workbook they want to create that would contain the results of the FileSearch.

    I'd like to build my own code, but snippets of code that anyone here can provide to any aspect of the above problem would very interesting to see. I can piece the puzzle together and when I'm finished I will post the finished code here for other newbies. Thanx!

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Project: Filesearch drop results into a worksheet

    Rob,

    Here's a starting point:

    <pre>Dim sFileName As String
    Dim i As Long
    Dim sPath As String

    sPath = "c:afpforms97"
    sFileName = Dir(sPath)
    Do While sFileName <> ""
    If Not (GetAttr(sPath & sFileName) And vbDirectory) = vbDirectory Then
    If sFileName <> "." And sFileName <> ".." Then
    i = i + 1
    Cells(i, 1) = sFileName
    Cells(i, 2) = FileDateTime(sPath & sFileName)
    End If
    End If
    sFileName = Dir ' Get next entry.
    Loop
    </pre>

    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  4. #3
    New Lounger
    Join Date
    Jan 2001
    Location
    Canada
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Project: Filesearch drop results into a worksheet

    Thanks Geoff, this got me started. I don't have a lot of time right now to tinker with this, but I attached this code to a button control and it worked like a charm. Gratefully it doesn't use FileSearch at all.

    Thanks for the tip, I can definately start my project with this.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •