Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel linked documents password hold in code
hi there
i have a document which links to 14 other documents, which all have passwords. i would like to be able to update without having to remember all the passwords. does anyone have any idea on how to do this? a way of holding the passwords in vba? i think i will have to open and then close each doc as Excel will not open all of them together.... runs out of memory... any help! as always gratefully received.... x x x |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
excel linked documents password hold in code
Untested.
Maybe you could have a helper workbook open your workbook and not update the links. Then it could open each of the 14 other workbooks (the links should refresh when the workbook opens), then close that workbook and open the next. Kind of like this: 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 (I got bored after 3 workbooks. You may want to test it with a couple to get it going.) If you're new to macros, you may want to read David McRitchie's intro at: http://www.mvps.org/dmcritchie/excel/getstarted.htm wrote: hi there i have a document which links to 14 other documents, which all have passwords. i would like to be able to update without having to remember all the passwords. does anyone have any idea on how to do this? a way of holding the passwords in vba? i think i will have to open and then close each doc as Excel will not open all of them together.... runs out of memory... any help! as always gratefully received.... x x x -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can I convert linked into embedded documents in Excel 2003? | Excel Discussion (Misc queries) | |||
How do I automatically open linked documents in Excel? | New Users to Excel | |||
Password protection of Excel Sheet linked to Bloomberg Data | Excel Discussion (Misc queries) | |||
OFFSET command and linked documents | Excel Worksheet Functions | |||
Approach Linked documents via INDIRECT | Links and Linking in Excel |