How to lock external workbooks

[markadon]

I have 30+ supervisors who each have their own workbook containing data in a (for our company) standard format. I have a master workbook that reads the data from each supervisor workbook, massages it, and re-writes it to the individual workbooks. The massaging may take several minutes. I want to lock all the workbooks BEFORE I read in the data and then unlock it AFTER I write the data. Is there some way, other than leaving them open (they all have the same name), to use VBA to lock the workbooks until I'm done with them?

 

[pbornemeier]

You can copy each workbook from its current folder to a different "secret" folder (named for the supervisor since the files all have the same name) and delete the original. Massage and close the files, then copy them back to their source folders and archive the ones you massaged until you get next day's copy. You will not have to keep each workbook open, but the supervisor will not be able to find it, so it is effectively "locked".

to copy a file

Name strOldFileFullPathName As strNewFileFullPathName

to delete a file

Kill strOldFileFullpathName

 

[markadon]

Thanks. I thought about that last night and it was the only way I could come up with, too.