Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Updating links in protected workbooks
I have a number of workbooks that I must lock and password protect (so folks
stop changing formulas). However these are linked to other source files. If they go to Edit Links they cannot simply update the links, the only option is to individually open the source files (which then updates the receiving file). Is there some permission setting I can change to allow them to manually update without opening all the source files? Thanks, Alan |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Updating links in protected workbooks
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.) Alan P wrote: I have a number of workbooks that I must lock and password protect (so folks stop changing formulas). However these are linked to other source files. If they go to Edit Links they cannot simply update the links, the only option is to individually open the source files (which then updates the receiving file). Is there some permission setting I can change to allow them to manually update without opening all the source files? Thanks, Alan -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
UPDATING LINKS TO OTHER WORKBOOKS :( | Excel Discussion (Misc queries) | |||
Updating Workbooks from multiple links Workbooks | Excel Worksheet Functions | |||
Updating Links in a Protected Sheets | Excel Discussion (Misc queries) | |||
Updating links to closed workbooks | Excel Discussion (Misc queries) | |||
Excel 2003 links are not updating between workbooks | Excel Worksheet Functions |