Linking Password Protected Workbooks

[Andybuck86]

Hi,

I am hoping you all can help me - I have had a lot of success asking you guys and girls in the past!

I am looking to link several (more than 70) spreadsheets to one master spreadsheet.

I have created an 'electronic timesheet' that will be distributed to over 70 members of staff. On the spreadsheet is a save button that I have created which will save their timesheet with a name based on cells in the workbook and in a folder based on cells in the workbook.

I now wish to create a master spreadsheet which will collate some of the data in these 70+ spreadsheets.

What is the easiest way to do this? Is it possible for me to edit my 'save' button so that when pressed it sends data to the 'master' spreadsheet? This would have to look at some of the data in the master spreadsheet and put it in the correct place.

All of the 70+ spreadsheets will be password protected with an end user created password although they will all have a master password for read only access.

Any help would be really appreciated!

Thanks in advance


Andy

[MWE]

Hi,

I am hoping you all can help me - I have had a lot of success asking you guys and girls in the past!

I am looking to link several (more than 70) spreadsheets to one master spreadsheet.

I have created an 'electronic timesheet' that will be distributed to over 70 members of staff. On the spreadsheet is a save button that I have created which will save their timesheet with a name based on cells in the workbook and in a folder based on cells in the workbook.

I now wish to create a master spreadsheet which will collate some of the data in these 70+ spreadsheets.

What is the easiest way to do this? Is it possible for me to edit my 'save' button so that when pressed it sends data to the 'master' spreadsheet? This would have to look at some of the data in the master spreadsheet and put it in the correct place.

All of the 70+ spreadsheets will be password protected with an end user created password although they will all have a master password for read only access.

Any help would be really appreciated!

Thanks in advance


Andy

This does not seem to be a very difficult problem providing you have the network access required. Based on your description, I assume:

1. the file names and directory combinations are unique for each employee
2. the directories are on a server that is accessible by you and all employees
3. you have an expectation regarding when (each week?) employees will have completed their timesheet
4. macro security is appropriately set on the employees computer so that your SAVE button works
5. the SAVE button saves an instance of the completed workbook with a specific filename to a target directory

Questions:

1. what is the anticipated structure of the master workbook?
2. will there be a new master each time period or will one master accumulate data over several time periods?
3. what happens if the employee fails to do what you wanted by the deadline? The procedure running at the master can simply note that and move on and then display/store statistics at the end
4. do you expect the SAVE button to initiate any procedure to check the validity of the information before the workbook is saved to the target location
5. what happens to an individual spreadsheet (the copy on the server) after the master has extracted relevant data?

Please verify the assumptions and provide answers to the questions.

[Andybuck86]

This does not seem to be a very difficult problem providing you have the network access required. Based on your description, I assume:

1. the file names and directory combinations are unique for each employee
2. the directories are on a server that is accessible by you and all employees
3. you have an expectation regarding when (each week?) employees will have completed their timesheet
4. macro security is appropriately set on the employees computer so that your SAVE button works
5. the SAVE button saves an instance of the completed workbook with a specific filename to a target directory

Questions:

1. what is the anticipated structure of the master workbook?
2. will there be a new master each time period or will one master accumulate data over several time periods?
3. what happens if the employee fails to do what you wanted by the deadline? The procedure running at the master can simply note that and move on and then display/store statistics at the end
4. do you expect the SAVE button to initiate any procedure to check the validity of the information before the workbook is saved to the target location
5. what happens to an individual spreadsheet (the copy on the server) after the master has extracted relevant data?

Please verify the assumptions and provide answers to the questions.

Hi,

Sorry, I didn't realise I had a response to this!

the file names and directory combinations are unique for each employee

Correct - file name is based on employee name and payroll month

the directories are on a server that is accessible by you and all employees

Correct again

you have an expectation regarding when (each week?) employees will have completed their timesheet

Timesheets will be done weekly

macro security is appropriately set on the employees computer so that your SAVE button works

Correct

the SAVE button saves an instance of the completed workbook with a specific filename to a target directory

Correct. The timesheet saves with a filename (employee name/payroll month) and to a folder (Payroll Month) on the network

In answer to your questions:

what is the anticipated structure of the master workbook?

This will be one workbook, with 12 tabs for each payroll month. The workbook will be very basic - employee name, payroll number, then payroll information which will be (hopefully) linked to their timesheet they created

will there be a new master each time period or will one master accumulate data over several time periods?

Hopefully I answered this above. One timesheet, accumulates data over several time periods

what happens if the employee fails to do what you wanted by the deadline? The procedure running at the master can simply note that and move on and then display/store statistics at the end

Well, I am going to have a list of employees in the master spreadsheet. If the payroll information is not there/or a timesheet has not been created then it should be pretty easy for me to spot.

do you expect the SAVE button to initiate any procedure to check the validity of the information before the workbook is saved to the target location

At the moment, no. I already have data validation checks running in the timesheets so this shouldn't be a problem.

what happens to an individual spreadsheet (the copy on the server) after the master has extracted relevant data?

The individual spreadsheets need to remain on the server

Hopefully I have answered everything but if you need more info then please give me a shout. I would really appreciate your help with this - it's been driving me insane for weeks!!

Here is the code in my save button:

[vba]Option Explicit
Sub Save_Password2()
Dim Open_Password As String
Dim New_File_Name As String
New_File_Name = Range("G4").Value & "\" & Range("G1").Value & ".xlsm"
Open_Password = InputBox("Please enter a password to stop others from editing your timesheet. YOU MUST REMEMBER THIS PASSWORD")
ActiveWorkbook.SaveAs New_File_Name, _
WriteResPassword:=Open_Password, _
Password:="test"

' ActiveWorkbook.SaveAs New_File_Name, _ ' creates a new file with the name in G1
' Password:=Open_Password, _ ' this is the password the user enters
' WriteResPassword:="password" ' this is the master password
End Sub[/vba]