If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Links to password protected workbooks
Hello, I have a summary sheet which links to many (20ish) password protected
files. Every time I open it or update the links, I have to enter many (20ish) passwords. Is there a way I can avoid or automate this process. I am happy to use VBA (or any other method), but bear in mind that the files are big and take 10 seconds or so to open. I'd really appreciate any feedback. Many thanks M |
#2
|
|||
|
|||
Links to password protected workbooks
Hi Michelle
Maybe you can use this add-in to get the values when you need them There is a option to fill in the password http://www.rondebruin.nl/merge.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Michelle" wrote in message ... Hello, I have a summary sheet which links to many (20ish) password protected files. Every time I open it or update the links, I have to enter many (20ish) passwords. Is there a way I can avoid or automate this process. I am happy to use VBA (or any other method), but bear in mind that the files are big and take 10 seconds or so to open. I'd really appreciate any feedback. Many thanks M |
#3
|
|||
|
|||
Links to password protected workbooks
Thanks Ron - Will the add in allow me to store 20 passwords for all the
different files? M "Ron de Bruin" wrote in message ... Hi Michelle Maybe you can use this add-in to get the values when you need them There is a option to fill in the password http://www.rondebruin.nl/merge.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Michelle" wrote in message ... Hello, I have a summary sheet which links to many (20ish) password protected files. Every time I open it or update the links, I have to enter many (20ish) passwords. Is there a way I can avoid or automate this process. I am happy to use VBA (or any other method), but bear in mind that the files are big and take 10 seconds or so to open. I'd really appreciate any feedback. Many thanks M |
#4
|
|||
|
|||
Links to password protected workbooks
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. (Saved from a previous post.) 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 myRealWkbkName = "C:\my documents\excel\book1.xls" myFileNames = Array("C:\my documents\excel\book11.xls", _ "C:\my documents\excel\book21.xls", _ "C:\my other folder\book11.xls") myPasswords = Array("pwd1", _ "pwd2", _ "pwd3") If UBound(myFileNames) UBound(myPasswords) Then MsgBox "check names & passwords--qty mismatch!" Exit Sub End If Set myRealWkbk = Workbooks.Open(Filename:=myRealWkbkName, UpdateLinks:=0) For iCtr = LBound(myFileNames) To UBound(myFileNames) Set wkbk = Nothing On Error Resume Next Set wkbk = Workbooks.Open(Filename:=myFileNames(iCtr), _ Password:=myPasswords(iCtr)) On Error GoTo 0 If wkbk Is Nothing Then MsgBox "Check file: " & myFileNames(iCtr) Exit Sub End If wkbk.Close savechanges:=False Next iCtr End Sub Michelle wrote: Hello, I have a summary sheet which links to many (20ish) password protected files. Every time I open it or update the links, I have to enter many (20ish) passwords. Is there a way I can avoid or automate this process. I am happy to use VBA (or any other method), but bear in mind that the files are big and take 10 seconds or so to open. I'd really appreciate any feedback. Many thanks M -- Dave Peterson |
#5
|
|||
|
|||
Links to password protected workbooks
No, if you have 20 passwords you need Dave's example
Make your life easier and use the same password or not use a password Very easy to break the password -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Michelle" wrote in message ... Thanks Ron - Will the add in allow me to store 20 passwords for all the different files? M "Ron de Bruin" wrote in message ... Hi Michelle Maybe you can use this add-in to get the values when you need them There is a option to fill in the password http://www.rondebruin.nl/merge.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "Michelle" wrote in message ... Hello, I have a summary sheet which links to many (20ish) password protected files. Every time I open it or update the links, I have to enter many (20ish) passwords. Is there a way I can avoid or automate this process. I am happy to use VBA (or any other method), but bear in mind that the files are big and take 10 seconds or so to open. I'd really appreciate any feedback. Many thanks M |
#6
|
|||
|
|||
Links to password protected workbooks
I like it, when should I run it? Is it in the Open event? Should I click
update links when the file opens? How does it interface with the requirement to have the links updated when the file opens? Thanks M "Dave Peterson" wrote in message ... 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. (Saved from a previous post.) 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 myRealWkbkName = "C:\my documents\excel\book1.xls" myFileNames = Array("C:\my documents\excel\book11.xls", _ "C:\my documents\excel\book21.xls", _ "C:\my other folder\book11.xls") myPasswords = Array("pwd1", _ "pwd2", _ "pwd3") If UBound(myFileNames) UBound(myPasswords) Then MsgBox "check names & passwords--qty mismatch!" Exit Sub End If Set myRealWkbk = Workbooks.Open(Filename:=myRealWkbkName, UpdateLinks:=0) For iCtr = LBound(myFileNames) To UBound(myFileNames) Set wkbk = Nothing On Error Resume Next Set wkbk = Workbooks.Open(Filename:=myFileNames(iCtr), _ Password:=myPasswords(iCtr)) On Error GoTo 0 If wkbk Is Nothing Then MsgBox "Check file: " & myFileNames(iCtr) Exit Sub End If wkbk.Close savechanges:=False Next iCtr End Sub Michelle wrote: Hello, I have a summary sheet which links to many (20ish) password protected files. Every time I open it or update the links, I have to enter many (20ish) passwords. Is there a way I can avoid or automate this process. I am happy to use VBA (or any other method), but bear in mind that the files are big and take 10 seconds or so to open. I'd really appreciate any feedback. Many thanks M -- Dave Peterson |
#7
|
|||
|
|||
Links to password protected workbooks
Use it to open all 21 workbooks. Don't open them yourself.
If you look at the code, it opens your "real" workbook first--but doesn't update the links. Then it opens each of the other 20 "sending" workbooks. After each of the sending workbooks is opened, the links to that workbook will recalc. Then that sending workbook is closed. If these other 20 workbooks change while you have the real workbook open and you want to refresh the links, you can run a macro that's almost exactly the same. Just delete the line that opens the real workbook--you don't want that to happen again. This is the line to be removed: Set myRealWkbk = Workbooks.Open(Filename:=myRealWkbkName, UpdateLinks:=0) Michelle wrote: I like it, when should I run it? Is it in the Open event? Should I click update links when the file opens? How does it interface with the requirement to have the links updated when the file opens? Thanks M "Dave Peterson" wrote in message ... 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. (Saved from a previous post.) 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 myRealWkbkName = "C:\my documents\excel\book1.xls" myFileNames = Array("C:\my documents\excel\book11.xls", _ "C:\my documents\excel\book21.xls", _ "C:\my other folder\book11.xls") myPasswords = Array("pwd1", _ "pwd2", _ "pwd3") If UBound(myFileNames) UBound(myPasswords) Then MsgBox "check names & passwords--qty mismatch!" Exit Sub End If Set myRealWkbk = Workbooks.Open(Filename:=myRealWkbkName, UpdateLinks:=0) For iCtr = LBound(myFileNames) To UBound(myFileNames) Set wkbk = Nothing On Error Resume Next Set wkbk = Workbooks.Open(Filename:=myFileNames(iCtr), _ Password:=myPasswords(iCtr)) On Error GoTo 0 If wkbk Is Nothing Then MsgBox "Check file: " & myFileNames(iCtr) Exit Sub End If wkbk.Close savechanges:=False Next iCtr End Sub Michelle wrote: Hello, I have a summary sheet which links to many (20ish) password protected files. Every time I open it or update the links, I have to enter many (20ish) passwords. Is there a way I can avoid or automate this process. I am happy to use VBA (or any other method), but bear in mind that the files are big and take 10 seconds or so to open. I'd really appreciate any feedback. Many thanks M -- Dave Peterson -- Dave Peterson |
#8
|
|||
|
|||
Links to password protected workbooks
Thanks - that's great
M "Dave Peterson" wrote in message ... Use it to open all 21 workbooks. Don't open them yourself. If you look at the code, it opens your "real" workbook first--but doesn't update the links. Then it opens each of the other 20 "sending" workbooks. After each of the sending workbooks is opened, the links to that workbook will recalc. Then that sending workbook is closed. If these other 20 workbooks change while you have the real workbook open and you want to refresh the links, you can run a macro that's almost exactly the same. Just delete the line that opens the real workbook--you don't want that to happen again. This is the line to be removed: Set myRealWkbk = Workbooks.Open(Filename:=myRealWkbkName, UpdateLinks:=0) Michelle wrote: I like it, when should I run it? Is it in the Open event? Should I click update links when the file opens? How does it interface with the requirement to have the links updated when the file opens? Thanks M "Dave Peterson" wrote in message ... 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. (Saved from a previous post.) 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 myRealWkbkName = "C:\my documents\excel\book1.xls" myFileNames = Array("C:\my documents\excel\book11.xls", _ "C:\my documents\excel\book21.xls", _ "C:\my other folder\book11.xls") myPasswords = Array("pwd1", _ "pwd2", _ "pwd3") If UBound(myFileNames) UBound(myPasswords) Then MsgBox "check names & passwords--qty mismatch!" Exit Sub End If Set myRealWkbk = Workbooks.Open(Filename:=myRealWkbkName, UpdateLinks:=0) For iCtr = LBound(myFileNames) To UBound(myFileNames) Set wkbk = Nothing On Error Resume Next Set wkbk = Workbooks.Open(Filename:=myFileNames(iCtr), _ Password:=myPasswords(iCtr)) On Error GoTo 0 If wkbk Is Nothing Then MsgBox "Check file: " & myFileNames(iCtr) Exit Sub End If wkbk.Close savechanges:=False Next iCtr End Sub Michelle wrote: Hello, I have a summary sheet which links to many (20ish) password protected files. Every time I open it or update the links, I have to enter many (20ish) passwords. Is there a way I can avoid or automate this process. I am happy to use VBA (or any other method), but bear in mind that the files are big and take 10 seconds or so to open. I'd really appreciate any feedback. Many thanks M -- Dave Peterson -- Dave Peterson |
#9
|
|||
|
|||
Links to password protected workbooks
Does anyone know why this happens? I will soon be writing similar
summary files, so it may be apropros for me. But when I test it, my links update without requiring the passwords. |
#10
|
|||
|
|||
Links to password protected workbooks
What did you protect and how did you protect it?
Did you use worksheet protection or workbook protection? If workbook, did you use tools|Protect|protect workbook? Or did you use File|SaveAs|tools|General options|Password to modify or password to open? Spiky wrote: Does anyone know why this happens? I will soon be writing similar summary files, so it may be apropros for me. But when I test it, my links update without requiring the passwords. -- Dave Peterson |
Thread Tools | |
Display Modes | |
|
|