Closed Thread
Results 1 to 4 of 4

Macro to search file location for presence of a specific file

  1. #1
    Registered User
    Join Date
    07-25-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    88

    Macro to search file location for presence of a specific file

    Hello,

    I want to design a worksheet, sort of a “dashboard”, that can summarize reporting for multiple employees. I work for a small accounting firm where 3 / 4 different employees are responsible for compiling approximately a dozen reports on a daily basis. Each report is saved in a unique folder with the same file name, except the date is changed/rolled to reflect reporting for the current day.

    I’d like to be able to open this dashboard, and for a given report, see whether or not something has been saved down. For example, in the attached sample file, the first report in row 7 is the “Trial Balance”. Column B expresses how the report is saved each day, as “TrialBalance_MMDDYYYY”. Based upon the date in row 5, is it possible for a macro to search for a file with the name TrialBalance_MMDDYYYY in a given directory, and return TRUE or FALSE? E.g. Given a specific location, if the macro identifies a file with the name “ExpReport_12242013”, it will return TRUE in cell C9. If that file name does not exist in the given directory location, then FALSE.

    If this is something that could be done, then the macro could look for a fixed file name, with a variable date suffix, in a given directory. The report would then return TRUE if that specific file exists and FALSE if it does not. This would create a useful and quick summary for me that would enable me to see which reports have been completed and which have not.

    Thank you for taking the time to read!

    -Student1990
    Attached Files Attached Files

  2. #2
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,258

    Re: Macro to search file location for presence of a specific file

    Hello Student1990,

    The attached workbook contains the macro below. This macro will run automatically when the workbook is opened or whenever the worksheet be the active sheet.

    You will need to change the parent directory in the macro to where your report folders are being saved.

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  3. #3
    Registered User
    Join Date
    07-25-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    88

    Re: Macro to search file location for presence of a specific file

    Leith,

    This is great, thank you for your solution.

    I need to modify my example as my situation has changed slightly. For organizational purposes, we’re required to keep each report in a different folder. Therefore, the directory for each type of report will be different. My VBA knowledge is pretty basic, but I’m thinking the easiest way will be to create a different macro for each type of report, where the specific directory is entered in the code of each macro. I could then use one master macro to call each individual macro for each type of report.

    Attached is a simpler workbook. If I wanted to do this, how could I modify your code so that, given a specific directory and a specific row, it will search that directory for a file with that matches the date in row 5. If it finds a match, it will return TRUE in the appropriate row. For example, in the attached workbook, row 9 is for the Expense Report. This report will always be saved in the format “ExpReport_MMDDYYYY”. Given a specific directory location for the Expense Report, if a file exists in that location that ends in the specific date in row 5, then the macro returns TRUE for that column, in row 9. If “ReportingDashboard_2_Student1990.xlsxExpReport_12282013” is saved in the directory, then cell E9 would read TRUE, and so on and so forth.

    How might I make this adjustment? Thanks again to all for taking the time to read.

  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Macro to search file location for presence of a specific file

    Closed at user's request.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

Closed Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 0
    Last Post: 07-11-2013, 02:08 AM
  2. Save as macro that specifies file type, file location and takes file name from three cells
    By ExcelFailure in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 06-13-2013, 10:09 PM
  3. macro button to attach file to a specific location
    By nicko54 in forum Excel General
    Replies: 0
    Last Post: 06-25-2012, 08:41 PM
  4. Macro for PDF to specific location and file name from Cell
    By NokiaFanatic in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-13-2010, 12:00 AM
  5. Macro - send a file to a specific location
    By Bob1955 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-28-2006, 03:29 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1