0

In our company, everyone has a personal Excel sheet where they fill in working hours for each customer and project they work on.

The customers and projects are chosen via dropdown. Customers/projects are altered from time to time and have to be changed in everyone's separate Excel sheet.

I created a file where the person responsible for the working hours can type in the name of the person who's file has to be checked, the old names of customer and project and the new names for the two. The VBA code then opens the corresponding workbooks and checks the file for matches and replaces them.

If a file which has to be checked is in use by a different user, it can't be opened and altered.

I want to find a way to recognize if a file is in use, if yes skip said file and then put the filename in a column "file in use" in my sheet.

Community
  • 1
  • 1
RW234
  • 1
  • 2
    Possible duplicate of [Detect whether Excel workbook is already open](https://stackoverflow.com/questions/9373082/detect-whether-excel-workbook-is-already-open) – CLR Nov 12 '19 at 11:04
  • 1
    This could be solved in a number of ways, `Onerror` would be my first thought. Can you show us your code so we can have a look for you what would be most efficient? – Plutian Nov 12 '19 at 11:05
  • I had a project like that once, using one document. Opening as moderator would open as read/write and otherwise as read-only. Employees could still add data but this data is first read to .txt files which the moderator can import to enrich the data in the master document. Worked quite well. – JvdV Nov 12 '19 at 11:32
  • Collaboration with spreadsheets is easy with Google sheets since it provides transparent multi-use of a single spreadsheet in the cloud with control over who can read, comment, or view. There are many things Excel can do that Google Sheets cannot, but that list is getting shorter as the years go by. I've always loved MS Excel, but sometimes it's not the best tool – Tony M Nov 12 '19 at 11:57
  • What I like in such cases (as long as everyone may know which customers/projects there are in the whole company): Create a central Workbook with all Customers/projects/people/whatever. Then let every timesheet itself check against that central workbook, if its projectlist is old and get a new one (opening the central sheet readonly(!)). – Jochen Feb 12 '20 at 10:08

0 Answers0