Home |
Search |
Today's Posts |
#1
|
|||
|
|||
password issues in Excel 2002
I am trying to help a friend with an Excel project. She is a teacher. We
are using Excel 2002. She would like to have 30 files for 30 people on a network drive everyone can access Each of the 30 files would be password protected. (Save As, Tools, General Options). Each person could access her own file but no one else's. She would like me to create a Grand Total sheet that would take data from each of the 30 files and link the data to show the overall totals. Everyone could see the totals, but they could not see the individual scores each teacher had. I can do all of the above. The problem is that when you open the Grand Total file which is not password protected, you are told that the links must be updated. If you click to update, you are then prompted for all the passwords. This is too much work for each person to do, plus it gives them passwords to get into the 30 files so there goes your security. I guess I am asking two contradictory things from Excel. First, I am saying I want to password protect some data in the 30 files. But then I want Excel to make the data available in the Grand Total sheet for anyone to see. Does anyone have any suggestions on how I could set up Excel to achieve my goals? Or am I asking the impossible? TIA |
#2
|
|||
|
|||
I think you're asking the impossible if you just work with passwords.
You could save the workbooks with the file|saveas|tools|general options|"password to modify" set You won't be prompted for a password for your links. But anyone could open that workbook and see all the data (in readonly mode, though). In fact, if you could see the links you wanted (without a password), then anyone could build their own link and point at any/all cells and see everything. One option would be to have a workbook that knows all the filenames and passwords and opens each and copies the relevant information from each and creates a new workbook (without the links--just the values) that any one could access. I think if I were doing this, I wouldn't take a chance that someone could break into my workbook and see all the filenames/passwords. I think I'd run that on demand (whenever I had to). === Another option. You update that summary workbook. Open all the files, have the links update, and save it with the current values. Tell the other users that they should not update links when they open that summary workbook. But you'd want to open the 30 workbooks before you open the summary workbook (with links). Then you could avoid all the prompts. saved from a previous post: Maybe you could create a macro that would open all those workbooks first. Your macro would supply both the workbook's name and password. Then after all these workbooks are open, you could open the file that contains the links. Kind of like: option explict sub auto_open() dim wkbk1 as workbook dim wkbk2 as workbook dim wkbk3 as workbook set wkbk1 = workbooks.open(filename:="c:\a.xls",password:="one ") set wkbk2 = workbooks.open(filename:="c:\b.xls",password:="two ") set wkbk3 = workbooks.open(filename:="c:\c.xls",password:="thr ee") 'wkbk3 is the real one! wkbk1.close savechanges:=false wkbk2.close savechanges:=false 'thisworkbook.close savechanges:=false end sub (I'd create a 4th workbook that opens the files in order and just closes the first 2 and then itself.) Stephen Larivee wrote: I am trying to help a friend with an Excel project. She is a teacher. We are using Excel 2002. She would like to have 30 files for 30 people on a network drive everyone can access Each of the 30 files would be password protected. (Save As, Tools, General Options). Each person could access her own file but no one else's. She would like me to create a Grand Total sheet that would take data from each of the 30 files and link the data to show the overall totals. Everyone could see the totals, but they could not see the individual scores each teacher had. I can do all of the above. The problem is that when you open the Grand Total file which is not password protected, you are told that the links must be updated. If you click to update, you are then prompted for all the passwords. This is too much work for each person to do, plus it gives them passwords to get into the 30 files so there goes your security. I guess I am asking two contradictory things from Excel. First, I am saying I want to password protect some data in the 30 files. But then I want Excel to make the data available in the Grand Total sheet for anyone to see. Does anyone have any suggestions on how I could set up Excel to achieve my goals? Or am I asking the impossible? TIA -- Dave Peterson |
#3
|
|||
|
|||
Thank you. I will have to rethink this.
Thanks again. "Dave Peterson" wrote in message ... I think you're asking the impossible if you just work with passwords. You could save the workbooks with the file|saveas|tools|general options|"password to modify" set You won't be prompted for a password for your links. But anyone could open that workbook and see all the data (in readonly mode, though). In fact, if you could see the links you wanted (without a password), then anyone could build their own link and point at any/all cells and see everything. One option would be to have a workbook that knows all the filenames and passwords and opens each and copies the relevant information from each and creates a new workbook (without the links--just the values) that any one could access. I think if I were doing this, I wouldn't take a chance that someone could break into my workbook and see all the filenames/passwords. I think I'd run that on demand (whenever I had to). === Another option. You update that summary workbook. Open all the files, have the links update, and save it with the current values. Tell the other users that they should not update links when they open that summary workbook. But you'd want to open the 30 workbooks before you open the summary workbook (with links). Then you could avoid all the prompts. saved from a previous post: Maybe you could create a macro that would open all those workbooks first. Your macro would supply both the workbook's name and password. Then after all these workbooks are open, you could open the file that contains the links. Kind of like: option explict sub auto_open() dim wkbk1 as workbook dim wkbk2 as workbook dim wkbk3 as workbook set wkbk1 = workbooks.open(filename:="c:\a.xls",password:="one ") set wkbk2 = workbooks.open(filename:="c:\b.xls",password:="two ") set wkbk3 = workbooks.open(filename:="c:\c.xls",password:="thr ee") 'wkbk3 is the real one! wkbk1.close savechanges:=false wkbk2.close savechanges:=false 'thisworkbook.close savechanges:=false end sub (I'd create a 4th workbook that opens the files in order and just closes the first 2 and then itself.) Stephen Larivee wrote: I am trying to help a friend with an Excel project. She is a teacher. We are using Excel 2002. She would like to have 30 files for 30 people on a network drive everyone can access Each of the 30 files would be password protected. (Save As, Tools, General Options). Each person could access her own file but no one else's. She would like me to create a Grand Total sheet that would take data from each of the 30 files and link the data to show the overall totals. Everyone could see the totals, but they could not see the individual scores each teacher had. I can do all of the above. The problem is that when you open the Grand Total file which is not password protected, you are told that the links must be updated. If you click to update, you are then prompted for all the passwords. This is too much work for each person to do, plus it gives them passwords to get into the 30 files so there goes your security. I guess I am asking two contradictory things from Excel. First, I am saying I want to password protect some data in the 30 files. But then I want Excel to make the data available in the Grand Total sheet for anyone to see. Does anyone have any suggestions on how I could set up Excel to achieve my goals? Or am I asking the impossible? TIA -- Dave Peterson |
#4
|
|||
|
|||
Hi Stephan
How about breaking the password for all the 30 files??? Let me know if you are interested and mail me personally. Bye and Take Care!! "Stephen Larivee" wrote: Thank you. I will have to rethink this. Thanks again. "Dave Peterson" wrote in message ... I think you're asking the impossible if you just work with passwords. You could save the workbooks with the file|saveas|tools|general options|"password to modify" set You won't be prompted for a password for your links. But anyone could open that workbook and see all the data (in readonly mode, though). In fact, if you could see the links you wanted (without a password), then anyone could build their own link and point at any/all cells and see everything. One option would be to have a workbook that knows all the filenames and passwords and opens each and copies the relevant information from each and creates a new workbook (without the links--just the values) that any one could access. I think if I were doing this, I wouldn't take a chance that someone could break into my workbook and see all the filenames/passwords. I think I'd run that on demand (whenever I had to). === Another option. You update that summary workbook. Open all the files, have the links update, and save it with the current values. Tell the other users that they should not update links when they open that summary workbook. But you'd want to open the 30 workbooks before you open the summary workbook (with links). Then you could avoid all the prompts. saved from a previous post: Maybe you could create a macro that would open all those workbooks first. Your macro would supply both the workbook's name and password. Then after all these workbooks are open, you could open the file that contains the links. Kind of like: option explict sub auto_open() dim wkbk1 as workbook dim wkbk2 as workbook dim wkbk3 as workbook set wkbk1 = workbooks.open(filename:="c:\a.xls",password:="one ") set wkbk2 = workbooks.open(filename:="c:\b.xls",password:="two ") set wkbk3 = workbooks.open(filename:="c:\c.xls",password:="thr ee") 'wkbk3 is the real one! wkbk1.close savechanges:=false wkbk2.close savechanges:=false 'thisworkbook.close savechanges:=false end sub (I'd create a 4th workbook that opens the files in order and just closes the first 2 and then itself.) Stephen Larivee wrote: I am trying to help a friend with an Excel project. She is a teacher. We are using Excel 2002. She would like to have 30 files for 30 people on a network drive everyone can access Each of the 30 files would be password protected. (Save As, Tools, General Options). Each person could access her own file but no one else's. She would like me to create a Grand Total sheet that would take data from each of the 30 files and link the data to show the overall totals. Everyone could see the totals, but they could not see the individual scores each teacher had. I can do all of the above. The problem is that when you open the Grand Total file which is not password protected, you are told that the links must be updated. If you click to update, you are then prompted for all the passwords. This is too much work for each person to do, plus it gives them passwords to get into the 30 files so there goes your security. I guess I am asking two contradictory things from Excel. First, I am saying I want to password protect some data in the 30 files. But then I want Excel to make the data available in the Grand Total sheet for anyone to see. Does anyone have any suggestions on how I could set up Excel to achieve my goals? Or am I asking the impossible? TIA -- Dave Peterson |
#5
|
|||
|
|||
What do you mean by "breaking the password"???
"Raj" wrote in message ... Hi Stephan How about breaking the password for all the 30 files??? Let me know if you are interested and mail me personally. Bye and Take Care!! "Stephen Larivee" wrote: Thank you. I will have to rethink this. Thanks again. "Dave Peterson" wrote in message ... I think you're asking the impossible if you just work with passwords. You could save the workbooks with the file|saveas|tools|general options|"password to modify" set You won't be prompted for a password for your links. But anyone could open that workbook and see all the data (in readonly mode, though). In fact, if you could see the links you wanted (without a password), then anyone could build their own link and point at any/all cells and see everything. One option would be to have a workbook that knows all the filenames and passwords and opens each and copies the relevant information from each and creates a new workbook (without the links--just the values) that any one could access. I think if I were doing this, I wouldn't take a chance that someone could break into my workbook and see all the filenames/passwords. I think I'd run that on demand (whenever I had to). === Another option. You update that summary workbook. Open all the files, have the links update, and save it with the current values. Tell the other users that they should not update links when they open that summary workbook. But you'd want to open the 30 workbooks before you open the summary workbook (with links). Then you could avoid all the prompts. saved from a previous post: Maybe you could create a macro that would open all those workbooks first. Your macro would supply both the workbook's name and password. Then after all these workbooks are open, you could open the file that contains the links. Kind of like: option explict sub auto_open() dim wkbk1 as workbook dim wkbk2 as workbook dim wkbk3 as workbook set wkbk1 = workbooks.open(filename:="c:\a.xls",password:="one ") set wkbk2 = workbooks.open(filename:="c:\b.xls",password:="two ") set wkbk3 = workbooks.open(filename:="c:\c.xls",password:="thr ee") 'wkbk3 is the real one! wkbk1.close savechanges:=false wkbk2.close savechanges:=false 'thisworkbook.close savechanges:=false end sub (I'd create a 4th workbook that opens the files in order and just closes the first 2 and then itself.) Stephen Larivee wrote: I am trying to help a friend with an Excel project. She is a teacher. We are using Excel 2002. She would like to have 30 files for 30 people on a network drive everyone can access Each of the 30 files would be password protected. (Save As, Tools, General Options). Each person could access her own file but no one else's. She would like me to create a Grand Total sheet that would take data from each of the 30 files and link the data to show the overall totals. Everyone could see the totals, but they could not see the individual scores each teacher had. I can do all of the above. The problem is that when you open the Grand Total file which is not password protected, you are told that the links must be updated. If you click to update, you are then prompted for all the passwords. This is too much work for each person to do, plus it gives them passwords to get into the 30 files so there goes your security. I guess I am asking two contradictory things from Excel. First, I am saying I want to password protect some data in the 30 files. But then I want Excel to make the data available in the Grand Total sheet for anyone to see. Does anyone have any suggestions on how I could set up Excel to achieve my goals? Or am I asking the impossible? TIA -- Dave Peterson |
#6
|
|||
|
|||
Her's an approach that I haven't thoroughly tested, but may be a solution.
1) Store all student info in the file we'll call Master and set up one sheet/range for each student plus a Totals sheet, 2) Provide each student with a file that links to the appropriate areas in the Master, 3) Hide all student sheets in the Master and use ToolsProtect Workbook with a check for Structure and apply a password Each student file can be password protected which will not interfer with updates from Master. Even though anyone can open Master they will not be able to view anything but the Totals sheet (at least 1 sheet must remain unhidden) unless they unhide the other sheets which they can't do without the password. Hope this is useful |:) "Stephen Larivee" wrote: I am trying to help a friend with an Excel project. She is a teacher. We are using Excel 2002. She would like to have 30 files for 30 people on a network drive everyone can access Each of the 30 files would be password protected. (Save As, Tools, General Options). Each person could access her own file but no one else's. She would like me to create a Grand Total sheet that would take data from each of the 30 files and link the data to show the overall totals. Everyone could see the totals, but they could not see the individual scores each teacher had. I can do all of the above. The problem is that when you open the Grand Total file which is not password protected, you are told that the links must be updated. If you click to update, you are then prompted for all the passwords. This is too much work for each person to do, plus it gives them passwords to get into the 30 files so there goes your security. I guess I am asking two contradictory things from Excel. First, I am saying I want to password protect some data in the 30 files. But then I want Excel to make the data available in the Grand Total sheet for anyone to see. Does anyone have any suggestions on how I could set up Excel to achieve my goals? Or am I asking the impossible? TIA |
#7
|
|||
|
|||
There is software available that will break/remove/identify the password for
protected workbooks (file|saveAs|Tools protection). http://www.lostpassword.com (I've never used it.) And the password for both Tools|protection|protect Sheet and tools|protection|protect workbook is also broken easily. visit JE McGimpsey's site: http://www.mcgimpsey.com/excel/removepwords.html Stephen Larivee wrote: What do you mean by "breaking the password"??? "Raj" wrote in message ... Hi Stephan How about breaking the password for all the 30 files??? Let me know if you are interested and mail me personally. Bye and Take Care!! "Stephen Larivee" wrote: Thank you. I will have to rethink this. Thanks again. "Dave Peterson" wrote in message ... I think you're asking the impossible if you just work with passwords. You could save the workbooks with the file|saveas|tools|general options|"password to modify" set You won't be prompted for a password for your links. But anyone could open that workbook and see all the data (in readonly mode, though). In fact, if you could see the links you wanted (without a password), then anyone could build their own link and point at any/all cells and see everything. One option would be to have a workbook that knows all the filenames and passwords and opens each and copies the relevant information from each and creates a new workbook (without the links--just the values) that any one could access. I think if I were doing this, I wouldn't take a chance that someone could break into my workbook and see all the filenames/passwords. I think I'd run that on demand (whenever I had to). === Another option. You update that summary workbook. Open all the files, have the links update, and save it with the current values. Tell the other users that they should not update links when they open that summary workbook. But you'd want to open the 30 workbooks before you open the summary workbook (with links). Then you could avoid all the prompts. saved from a previous post: Maybe you could create a macro that would open all those workbooks first. Your macro would supply both the workbook's name and password. Then after all these workbooks are open, you could open the file that contains the links. Kind of like: option explict sub auto_open() dim wkbk1 as workbook dim wkbk2 as workbook dim wkbk3 as workbook set wkbk1 = workbooks.open(filename:="c:\a.xls",password:="one ") set wkbk2 = workbooks.open(filename:="c:\b.xls",password:="two ") set wkbk3 = workbooks.open(filename:="c:\c.xls",password:="thr ee") 'wkbk3 is the real one! wkbk1.close savechanges:=false wkbk2.close savechanges:=false 'thisworkbook.close savechanges:=false end sub (I'd create a 4th workbook that opens the files in order and just closes the first 2 and then itself.) Stephen Larivee wrote: I am trying to help a friend with an Excel project. She is a teacher. We are using Excel 2002. She would like to have 30 files for 30 people on a network drive everyone can access Each of the 30 files would be password protected. (Save As, Tools, General Options). Each person could access her own file but no one else's. She would like me to create a Grand Total sheet that would take data from each of the 30 files and link the data to show the overall totals. Everyone could see the totals, but they could not see the individual scores each teacher had. I can do all of the above. The problem is that when you open the Grand Total file which is not password protected, you are told that the links must be updated. If you click to update, you are then prompted for all the passwords. This is too much work for each person to do, plus it gives them passwords to get into the 30 files so there goes your security. I guess I am asking two contradictory things from Excel. First, I am saying I want to password protect some data in the 30 files. But then I want Excel to make the data available in the Grand Total sheet for anyone to see. Does anyone have any suggestions on how I could set up Excel to achieve my goals? Or am I asking the impossible? TIA -- Dave Peterson -- Dave Peterson |
#8
|
|||
|
|||
Thank you to all who responded. Some interesting leads but it is still not
coming out the way I had hoped. While this person had hoped to be able to accomplish her goals with this file, I don't think it is a vital issue. I will tell her that I was not able to find a satisfactory solution. But thanks again for all the help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2003 FAILS, but Excel 2000 SUCCEEDS ??? | Excel Discussion (Misc queries) | |||
sharing/using/saving Excel 2002 files in Excel 2003 | Excel Discussion (Misc queries) | |||
Excel 2002 and 2000 co-install. Control Which Starts ? | Excel Discussion (Misc queries) | |||
Excel 2003 back to Excel 2002 | Excel Discussion (Misc queries) | |||
Macro in Excel 2002 to save a workbook to a FTP location | Excel Discussion (Misc queries) |