PDA

View Full Version : Linking Password Protected Workbooks



Andybuck86
08-28-2010, 05:30 AM
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
08-28-2010, 09:17 PM
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:

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

what is the anticipated structure of the master workbook?
will there be a new master each time period or will one master accumulate 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
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
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
09-11-2010, 03:14 AM
This does not seem to be a very difficult problem providing you have the network access required. Based on your description, I assume:

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

what is the anticipated structure of the master workbook?
will there be a new master each time period or will one master accumulate 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
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
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!! :banghead:

Here is the code in my save button:

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