Noodlesoft Forums

Hello:

Each day I must create a collateral report. These are stored in the following folder structure: Bank Name\Year\Month\Day\Files… An example would be: Citizens\2019\06 – Jun\2019-06-26\Files…

My process goes something like this:

Step 1: Run an access program that creates the appropriate daily subfolder and fills it with almost all the necessary files.

Step 2: I go into the previously created subfolder (generally the prior working day) and copy a spreadsheet manually into the new subfolder for today.

Step 3: I rename the copied spreadsheet using the following structure: BBC_1234_YYYYMMDD.xls. Where 1234 = a sequential borrowing base report number. An example would be: BBC_1869_20190626.xls.

I would love to automate this with hazel, but I have spent quite a bit of time with not much success. So I’m writing now in hopes that someone can embarrass me by laying out how simply this can be done.

To be clear, step 1 is already done by a program you are already using and don't need Hazel to do that part?

For step 2, you will need to have Hazel go into subfolders. Check the manual as there is a whole chapter on this. Now, in your case, you only want it to go into yesterdays folder so it's going to be tricky. You can use a bunch of conditions for each folder level. I also assume the spreadsheet is different for each bank and that Hazel is monitoring the folder containing each bank folder. As a result, you want a set of conditions like:

Code: Select all

    If (all) are met
        If Kind is Folder
        If (all) are met
            Subfolder level is 1
        If (all) are met
            Subfolder level is 2
            Name matches (◦ year)
            (◦ year) is Today
        If (all) are met
           Subfolder level is 2
           Name matches (◦ month)
           (◦ month) is Today
        If (all) are met
           Subfolder level is 3
           Name matches (◦ date)
           (◦ date) is before Today
           (◦ date) is in the last 24 hours
    Then
        Run rules on folder contents


In this case, it will go specifically into yesterday's set of folders. Once there, you can create a rule to match the file:

Code: Select all

    If (all) are met
        Name matches (• file name)_(◦ file date)
        Extension is xls
   Do
        Move to Monitored Folder
        Sort into subfolder (current date <<format set to year>>) ▸ (current date <<format set to month>>) ▸ (current date)
        Rename (• file name)_(current date)


There's a bunch to digest so I suggest reading through the relevant parts of the manual. You will need to tweak the above and should probably do this in a test set of folders/files before using it on the real thing.