Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    543
    Thanks
    56
    Thanked 0 Times in 0 Posts

    Lock and copy files

    Finance has a requirement to electronically sign off files at 2 levels on a monthly basis. Once signed off they are marked as checked and the colour should change to green and when all the files are showing green and checked they are locked for editing and are copied and posted to a new folder and unlocked for use the next month.

    Does anyone have any ideas how we could manage this?
    cheers

    Phil Carter

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Phil,

    Lock what files? Where are they located? Where do you want them to turn green? We need more information.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,631
    Thanks
    115
    Thanked 645 Times in 589 Posts
    Additional questions:
    Will they be open simultaneously?
    How do you validate if they are marked as "checked"?

  4. #4
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    543
    Thanks
    56
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by RetiredGeek View Post
    Phil,

    Lock what files? Where are they located? Where do you want them to turn green? We need more information.
    Sorry they are monthly reconcilliation excel files located on a network drive and the master file would have a table for checking off the files as per attached.
    Attached Files Attached Files
    cheers

    Phil Carter

  5. #5
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Phil,

    The file you sent was a Word file is this the actual file to be used or will this operation be done in an Excel file (this would be much easier)?

    If done in Excel you could use the master file to actually open the files for review then when both checks are done save the file with a password. The changing of the color could easily be accomplished by conditional formatting.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #6
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    543
    Thanks
    56
    Thanked 0 Times in 0 Posts
    It will all be done in Excel.
    cheers

    Phil Carter

  7. #7
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Phil,

    Ok, here's a partial solution. Please play around with it and see if it meets the user interface portion of your problem. If it works like you'd expect let me know and I'll code the actual file operations part.

    Add/Delete File names.
    Check/Uncheck Accountant/Manager cols.
    Try the button w/all checked or any unchecked.

    HTH

    Test File: PhilCarter.xlsm

    P.S. Once a file is locked and copied do you want the lock on the copy PERMANENT or REMOVEABLE?
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. #8
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    543
    Thanks
    56
    Thanked 0 Times in 0 Posts
    RG hi
    What you have supplied is great. However, (there is always a BUT!) I have now found out the truth!

    There is one file, Balance Sheet Rec <month> <year>.xls, that is worked on by 4 staff so table will have 4 columns. The debtors and creditors clerks do there bit and indicate that they are finished with the file (6 days into new month) file needs to be locked for them, file checked by accountant (9 days) agrees OK file locked at that level only open to the Finance Manager. Finance Manager checks all is OK, checks last box and clicks "Check file status". File is saved and saved as in the same folder with the same name with new month and current year in file name. Original file locked permanently but still viewable
    cheers

    Phil Carter

  9. #9
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Phil,

    Ok now we're talking hard! I assume from this level of checking that you work for a fairly large organization? I'll also assume that you have a dedicated IT staff running your LAN? The only way I see to accomplish what you are after is to move the file (when a group is finished with it) to another directory on the LAN that only the next group has access to. This can't be accomplished via Excel's password feature as it only has one write protect password! So you'll have to work out with your IT department for a shared directory for each group. Each group will have to be able to Write to the next group up the line but NOT read from that directory (not a normal state of affairs). The final step (when all files are checked) can be accomplished via Excel's passwords. If the files are NEVER to be changed after the final step Excel can generate a random password that nobody will know (interesting huh!) for the Modify password. The Read password can be set to a known value to upper management or left blank depending on the access rights to the final folder the locked copy is stored to.

    I hope this makes some sense!

    BTW: Another thing occurred to me do you want the files to be opened by clicking on the file names in the master file (e.g. the one I just passed you)? Also do you want the macro operations to be controlled by the UserId of the person using the master file? These things can all be done but as I said it is starting to get complicated.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  10. #10
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    543
    Thanks
    56
    Thanked 0 Times in 0 Posts
    RG thanks again
    No only about 50 staff but we do need that level of checking. I thought you might come to this conclusion.
    Don't need the link to files but the macro operations controllled by the UserID is good
    cheers

    Phil Carter

  11. #11
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Phil,

    If I can make a suggestion I'd save the Locked Files with Filename-Year-Month(as number). This way when you look at them in File Explorer they will sort in order e.g.

    File1-2014-01.xlsx
    File1-2014-02.xlsx
    File2-2014-01.xlsx
    File2-2014-02.xlsx

    If you sort by date you'll of course get them in date order not File name order. Let me know it is not a big change to make either way.

    Also, are the files actually for the Month BEFORE when they will be saved? If so will they always be saved before the end of the month or should I prompt the user what month and maybe year the files are for?

    HTH
    Last edited by RetiredGeek; 2014-11-17 at 18:41.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  12. #12
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    543
    Thanks
    56
    Thanked 0 Times in 0 Posts
    Yes the files are always for the month before
    cheers

    Phil Carter

  13. #13
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Phil,

    Ok, here's a working copy with everything except the intermediate locking feature (I just don't know how to do that).
    To get the final files to write to the same directory just change the initialization for zDestPath to the same value as zSourcePath, make sure you include the trailing \ .

    Check out the Conditional formatting so you know how it's done if you have to change it. Note: I've set it up to use more rows than I think you'll ever need! Same for the RangeName AcctFiles.

    If you have any questions just post back.

    File: Bad Attachment See file in Post #17.

    HTH
    Last edited by RetiredGeek; 2014-11-17 at 20:41. Reason: Deleted bad attachment file
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  14. #14
    4 Star Lounger
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    543
    Thanks
    56
    Thanked 0 Times in 0 Posts
    I get a VB error message when I try to open the file
    cheers

    Phil Carter

  15. #15
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Phil,

    What message?
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Page 1 of 2 12 LastLast

Posting Permissions

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