Lock and copy files

Topic

New Reply

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?

Reply | Quote

Replies

Phil,

Lock what files? Where are they located? Where do you want them to turn green? We need more information. :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Phil,

Lock what files? Where are they located? Where do you want them to turn green? We need more information. :cheers:

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.

Reply | Quote

Additional questions:
Will they be open simultaneously?
How do you validate if they are marked as “checked”?

Reply | Quote

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 :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

It will all be done in Excel.

Reply | Quote

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 :cheers:

Test File: 38437-PhilCarter

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!
Computer Specs

Reply | Quote

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 .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

Reply | Quote

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 :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

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

Reply | Quote

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 :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

I get a VB error message when I try to open the file

Reply | Quote

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 :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Yes the files are always for the month before

Reply | Quote

Phil,

What message? :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

This is how the file link shows
38442-RGFile

and when I click to open I get this error message
38441-VBAerror

Reply | Quote

Phil,

Ok, let’s try it in zip file.

File: 38444-PhilCarter

HTH :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Instead of locking the files as the last process, why not just save the worksheet as a PDF and then delete the spreadsheet file?

Reply | Quote

Not sure how that would work as Finance need to copy bits of the spreadsheets out various other reports.

I’ll give it a try though

Reply | Quote

I think using folder permissions is a much better solution. You don’t need write only permissions, just restrict write access and have the higher level user move and rename the file. The Finance Manager is then responsible for locking the file with a write protect password. Then everyone can read the file but only people with write access can change anything and you don’t need to maintain macros.

cheers, Paul

Reply | Quote

Paul hi
You’re right that would be the best way.
We are in the process of installing and rolling out an ECMS system which I had overlooked with the initial request.
The system is M-Files and can be viewed here http://www.m-files.com/en. This system allows for the restriction of access to information at file or folder level so is ideal for what we require. We chose this system over SharePoint because the funtionality is very similar but the licensing costs are way lower and more manageable as we need to give up to 2500 people accessability

Reply | Quote