Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Protected workbooks blocking transfer of data
I have 30 workbooks that are protected by way of the security tab on the
Tools menu. All have different passwords, assigned by their "owner". These workbooks are totaled on another monthly sheet, which is also protected in the same way. When I open the Total Monthly worksheet and it attempts to update the information, it asks me to unlock the 30 workbooks - one at a time - which is entirely too much work to keep up with all of these different passwords. Is there anyway to circumvent this? Thanks for any help. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Protected workbooks blocking transfer of data
Are these are passwords to open the file?
Or are they passwords to unprotect the workbook? or are they passwords to unprotect sheets within the workbook? Or a combination of any of them??? If they're passwords to open the file, you could use a macro that would open the 30 "sending" workbooks first (supplying the password in the code) and then finally open your total workbook last. If they're passwords to unprotect the workbooks, you could do the same kind of in code. Same with the worksheets. But you'll have to know the passwords for the code to work. And the code is different for each of those, so it wouldn't be worth the trouble of guessing what one you meant. Cindyt wrote: I have 30 workbooks that are protected by way of the security tab on the Tools menu. All have different passwords, assigned by their "owner". These workbooks are totaled on another monthly sheet, which is also protected in the same way. When I open the Total Monthly worksheet and it attempts to update the information, it asks me to unlock the 30 workbooks - one at a time - which is entirely too much work to keep up with all of these different passwords. Is there anyway to circumvent this? Thanks for any help. -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Protected workbooks blocking transfer of data
They are passwords to open the files.
Thx "Dave Peterson" wrote: Are these are passwords to open the file? Or are they passwords to unprotect the workbook? or are they passwords to unprotect sheets within the workbook? Or a combination of any of them??? If they're passwords to open the file, you could use a macro that would open the 30 "sending" workbooks first (supplying the password in the code) and then finally open your total workbook last. If they're passwords to unprotect the workbooks, you could do the same kind of in code. Same with the worksheets. But you'll have to know the passwords for the code to work. And the code is different for each of those, so it wouldn't be worth the trouble of guessing what one you meant. Cindyt wrote: I have 30 workbooks that are protected by way of the security tab on the Tools menu. All have different passwords, assigned by their "owner". These workbooks are totaled on another monthly sheet, which is also protected in the same way. When I open the Total Monthly worksheet and it attempts to update the information, it asks me to unlock the 30 workbooks - one at a time - which is entirely too much work to keep up with all of these different passwords. Is there anyway to circumvent this? Thanks for any help. -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Protected workbooks blocking transfer of data
For 30 files, I would create a new workbook that would open first.
This new workbook would have a worksheet with the names of the files in column A and the associated passwords in column B. Protect this workbook so that you share it with others who shouldn't know all 30 passwords. After that, I'd add a macro that would open the real workbook without refreshing the links. Then open the each of the other 30 files. When each opens, the formulas that refer to it in the real workbook should update. Then close that file. If you want to try... Saved from a previous post. Maybe you could have another workbook contains a macro that opens the other 20 workbooks. The macro would need to know all the names and passwords for the files. This expects a worksheet named WkbkList that contains the names of the files in A2:Axxx (headers in row 1) and passwords in B2:Bxxx. (The filenames have to include the drive, path and name.) Option Explicit Sub testme() Dim myFileNames As Variant Dim myPasswords As Variant Dim iCtr As Long Dim myRealWkbk As Workbook Dim myRealWkbkName As String Dim wkbk As Workbook 'the workbook with all the links myRealWkbkName = "C:\my documents\excel\book1.xls" With Worksheets("WkbkList") 'headers in row 1 myFileNames = .Range("a2:b" & _ .Cells(.Rows.Count, "A").End(xlUp).Row).Value End With Set myRealWkbk = Workbooks.Open(Filename:=myRealWkbkName, UpdateLinks:=0) For iCtr = LBound(myFileNames, 1) To UBound(myFileNames, 1) Set wkbk = Nothing On Error Resume Next Set wkbk = Workbooks.Open(Filename:=myFileNames(iCtr, 1), _ Password:=myFileNames(iCtr, 2), _ ReadOnly:=True) On Error GoTo 0 If wkbk Is Nothing Then MsgBox "Check file: " & myFileNames(iCtr, 1) Else 'the links should have updated when 'this workbook was opened. 'so we can close it here wkbk.Close savechanges:=False End If Next iCtr End Sub If the files are all in the same folder, you don't have to include that in the worksheet cells. You could just tell the program where to look: Change this line: Set wkbk = Workbooks.Open(Filename:=myFileNames(iCtr, 1), _ Password:=myFileNames(iCtr, 2), _ ReadOnly:=True) to: Set wkbk = Workbooks.Open(Filename:="C:\myfoldernamehere\" & _ myFileNames(iCtr, 1), _ Password:=myFileNames(iCtr, 2), _ ReadOnly:=True) If you're new to macros: Debra Dalgleish has some notes how to implement macros he http://www.contextures.com/xlvba01.html David McRitchie has an intro to macros: http://www.mvps.org/dmcritchie/excel/getstarted.htm Ron de Bruin's intro to macros: http://www.rondebruin.nl/code.htm (General, Regular and Standard modules all describe the same thing.) Cindyt wrote: They are passwords to open the files. Thx "Dave Peterson" wrote: Are these are passwords to open the file? Or are they passwords to unprotect the workbook? or are they passwords to unprotect sheets within the workbook? Or a combination of any of them??? If they're passwords to open the file, you could use a macro that would open the 30 "sending" workbooks first (supplying the password in the code) and then finally open your total workbook last. If they're passwords to unprotect the workbooks, you could do the same kind of in code. Same with the worksheets. But you'll have to know the passwords for the code to work. And the code is different for each of those, so it wouldn't be worth the trouble of guessing what one you meant. Cindyt wrote: I have 30 workbooks that are protected by way of the security tab on the Tools menu. All have different passwords, assigned by their "owner". These workbooks are totaled on another monthly sheet, which is also protected in the same way. When I open the Total Monthly worksheet and it attempts to update the information, it asks me to unlock the 30 workbooks - one at a time - which is entirely too much work to keep up with all of these different passwords. Is there anyway to circumvent this? Thanks for any help. -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
workbooks transfer from 2000 to 2007 | Excel Worksheet Functions | |||
How can I transfer formulas between 2 Excel workbooks? | Excel Worksheet Functions | |||
Hyperlinks and protected workbooks | Excel Worksheet Functions | |||
Blocking entry of data in a cell | Excel Worksheet Functions | |||
Password protected workbooks | Excel Discussion (Misc queries) |