+ Reply to Thread
Results 1 to 14 of 14

Macro to search and report findings in another directory

  1. #1
    Registered User
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    MS Office Professional 2010
    Posts
    30

    Post Macro to search and report findings in another directory

    Hi all,
    I need help creating a macro. I need it to look at my cities and go into a directory in a shared drive and look up the folders for those cities and look in a folder called "2016", then look for the current months file and in it look in the tab called "Summary" and report back if they filled out info for that day.

    For instance, I have attached two files. One is the Report file which is where I want to run the macro from. The other is the Log file which is where my cities will log their info. From here i need it to look at the city on column A on my Report file, go to the shared drive location (i.e. C\path1\path2\"city") and in there find a folder called "2016" (this folder "2016" will be the same in all cities). This folder has 12 Log files, one for each month labeled "1 Jan Log", "2 Feb Log", "3 Mar Log" ..etc. In these Log files, there is a summary tab which is where I want it to look and see if they completed the log for that day. The total on row 13 for that respective day will have something other than 0 if it was completed, which i want it to return that on my Report file under the column for that day for that city. Hope this makes sense.

    Any help is greatly appreciated!
    Attached Files Attached Files
    Last edited by rubenidas; 04-04-2016 at 03:56 PM.

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,460

    Re: Macro to search and report findings in another directory

    I think I understand most of the requirement, but I need a better explanation about the files from which you get the data and on the log file.

    What is the format of the files from which you get the data? Are the *.xls, *.xlsx or what? Also what does the data on a sheet within these files look like? You mention getting a total on Row 13. But what Cell? How is the data laid out on the sheet?

    For the log files, do you mention 1 Jan Log, etc. Don't you need these broken out by city? Do you want a separate file for each city and a tab for each city within?

    Finally, is the number of cities fixed, or do you anticipate adding cities in the future?
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Registered User
    Join Date
    03-24-2016
    Location
    USA
    MS-Off Ver
    12
    Posts
    2

    Re: Macro to search and report findings in another directory

    Let me look

  4. #4
    Registered User
    Join Date
    03-24-2016
    Location
    USA
    MS-Off Ver
    12
    Posts
    2

    Re: Macro to search and report findings in another directory

    this is a test posting failure

  5. #5
    Registered User
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    MS Office Professional 2010
    Posts
    30

    Re: Macro to search and report findings in another directory

    Hi Dflak,
    Thanks for the reply. Glad you reached out, i was almost giving up hope. So to answer your questions, ...

    "What is the format of the files from which you get the data? Are the *xls, *xlsx or what? Also what does the data on a sheet within these files look like? You mention getting a total on Row 13. But what Cell? How is the data laid out on the sheet?
    - These are xls . This is the Log File i attached, but i believe it attached an xlsx format of it. So on this Log file, they will have 31 tabs (32 with the summary tab). Each day they fill one out, and that is carried over to the summary tab on this Log file which is what i provided. So on this log file in the summary tab you have a row for days (row 3) and on row 13 is the Total for that day. I want to see if they filled out for that day on my REPORT file. Thats why i set it up similar, except for the cities on the first column. So for ANC row on REPORT, it will go into the shared drive and look in the folder ANC and find the folder "2016" (the file path will basically be the same for all cities, the only difference is the city, so ie. C/path/path/city/2016 ) and report whats been filled out (up to todays date, so if today is the 24th , then it technically should only check up till day 24 of course)

    For the log files, do you mention 1 Jan Log, etc. Don't you need these broken out by city? Do you want a separate file for each city and a tab for each city within?
    - These will be in folders for each city, therefore i am not breaking down the titles by city if that's what you mean.

    Finally, is the number of cities fixed, or do you anticipate adding cities in the future?
    - right now its fixed, there is a possibly of adding city's in the future. So pretty much i would add a folder for that city in the shared drive and also add it to the bottom of the list on REPORT file.

  6. #6
    Registered User
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    MS Office Professional 2010
    Posts
    30

    Re: Macro to search and report findings in another directory

    Hi Dflak, any ideas on how to do this?

  7. #7
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,460

    Re: Macro to search and report findings in another directory

    It's been a week. I'll have to take another look at it and "relearn" the requirement.

    Where do you want your log file? Also, I took a look at the log file. If you have the date down column A, then why do you need days on row 3?

  8. #8
    Registered User
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    MS Office Professional 2010
    Posts
    30

    Re: Macro to search and report findings in another directory

    The date was there for me to use when I was 'trying' to brainstorm how my macro would work. That doesn't have to be there technically.

  9. #9
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,460

    Re: Macro to search and report findings in another directory

    I'm having a difficult time visualizing the directory structure with dates, city names, month names, etc. Can you draw up a directory structure (picture)for where you get your data and where you want to write the logs?

    Also can you provide a zipped set of folders with sample data? I set one up, but it was very limited in scope for a specific date and I am not sure I have it right. I would be spending more time setting up the data than actually coding.

  10. #10
    Registered User
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    MS Office Professional 2010
    Posts
    30

    Re: Macro to search and report findings in another directory

    Dflak,
    I don't think I fully understand your request. I have attached a zip folder for you (at the top with my original attachments. It would not let me attach to this reply for some reason). I tried to make it more clearer here.

    1) I will open the Report.xls file on my laptop
    2) I want to run the macro and have it populate for each day up to todays DAY (today is the 4th so 1-4 only) a Yes or No if the log.xls file has been filled.
    3) the Log.xls file is in the shared drive folder and there are cities there with a folder inside (same for all cities) called 2016 Logs.
    4) on the summary tab it will have a number greater than 0 if they performed any of the checks for that day (similar setup as my Report.xls file with the day number at the top row)
    5) if the total for the day is greater than 0 (that means something was checked that day) then i want it to put Yes for that city for that day on my Report.xls file.

  11. #11
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,460

    Re: Macro to search and report findings in another directory

    Due to the difficulty setting up the data, I did minimal QA on this and there are still some things to be completed. I think I have the directory hierarchy figured out.

    From the "root" data directory you have sub directories of city names. Each city name has subdirectories of years. Each year has a monthly file. Each monthly file has daily tabs.

    I set up the log directory in almost similar fashion. From the "root" log directory there are sub directories of city names. Each city name has a log file with the format YYYY Log.xls. In each of these files are tabs 1-Jan, 2-Feb, 3-Mar …

    The program loops through the list of cities. These are in cells starting in cell A14 The program only looks at the number of cites you have. As you add cities the list will grow and the program will accommodate them. The city list is actually an Excel table.

    The program uses the date parts, city name, etc. to find the right path to the right file and the right sheet in that file. It checks cell A13 and if it isn’t blank it adds a “Yes” next to the date in the log file.

    The data files must exist. The log files must exist. You will need to add additional pages for the log files. I’ve only taken them as far as April. You can “clone” the pages and set the month start date in cell A1. I do not do any checks in the program to see if the files or pages exist.

    It is your responsibility to assure that they do exist.

    A potential future enhancement will do these checks and if the log file doesn’t exist, the program will create it. If the appropriate monthly sheet does not exist, the program will create it. If the data file or sheet does not exist, it will notify the user and exit gracefully rather than crashing.
    Attached Files Attached Files

  12. #12
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,460

    Re: Macro to search and report findings in another directory

    I just noticed an error in my code. I will fix it tomorrow.

  13. #13
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    365
    Posts
    7,460

    Re: Macro to search and report findings in another directory

    Here is the file along with the directory structure. Remember you will be responsible for assuring that the log and data files exist. You can copy the sheets in one of the log files to fill our a sheet for every month. All you will have to do is change the date in cell A1 on each sheet and rename the sheet. Once you have one workbook done up this way, then you can copy it to the log directory under the various city names.

    In 2017, make a copy of one of the logbooks. change the name, change the dates, clean out the old data, and copy it to the log directory.
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    11-24-2014
    Location
    Texas
    MS-Off Ver
    MS Office Professional 2010
    Posts
    30

    Re: Macro to search and report findings in another directory

    Thanks, I will test it out tomorrow and let you know how it goes.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Excel - Outlook Search Macro & Report
    By Pergo in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-08-2015, 09:20 AM
  2. Replies: 1
    Last Post: 02-04-2015, 09:59 AM
  3. Need Spreadsheet to Report Active Directory Data in Chart
    By djtech2k in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-26-2015, 09:53 PM
  4. Need a macro to search my phone directory.
    By fysxx in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 11-10-2013, 10:59 AM
  5. Need VBA code to search file in directory and sub-directory and show result
    By johnchencanada in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-19-2012, 11:13 PM
  6. macro to search in parent directory
    By jaango123 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-15-2011, 02:00 AM
  7. Macro to produce multiple 'Data sort' findings
    By rushdenx1 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-22-2010, 09:24 AM

Tags for this Thread

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