On top of the problem that Bill found, I don't think that it's the worksheet
protection that's the problem.

I'm betting that it's the workbook protection--if you have workbookA with links
to workbookB that is password protected and try to update the links in
workbookA, you'll have to provide the password for workbookB--to retrieve those
values.

This is different than the worksheet protection that your code uses.

It sounds like you'd want to:

Open each file without updating links
loop through the links and open each of those files (while supplying the
password) so that the links can update.
close that linked workbook
open the next linked workbook (and so forth)

Then open the next workbook with links (and repeat).

I don't see changing the worksheet protection as doing anything important.



doctorjones_md wrote:

Dave, thanks for your reply. The reasson why the EXCEL worksheets are
password protected is:

Background History:
===============
Originally, each of the 200+ budget templates had their own (incorporated)
Rates worksheet, but if and when we made adjustments to the rates, we had to
maked them individually in the 200+ workbooks. The decision was made to
utilize a MasterRates workbook (with the 200+ workbooks using a VLOOKUP to
the rates), and have the MasterRates workbook utilized an ODBC Link to an
ACCESS table (which is where the Rates will be updated) -- the ultimate goal
is to transistion from the EXCEL spreadsheet method to ACCESS -- this is
just a patch during this transistion.

The reason why the 200+ budget templates are password protected is so that
the Update Links process won't take place until the Accountants run this
code that I'm trying to iron-out here. For example: If they're in the
process of updating the Rates in the ACCESS table (which has an ODBC Link to
the MasterRates workbook), they don't want a user to open one of the
templates and have these Rates Updated (I know the process sounds somewhat
convoluted -- I hope this explanation helps)
"Dave Peterson" wrote in message
...
I'm not sure why you need code to unprotect any of the worksheets.

And you can specify that links should be updated when you open the file:

Set bk = Workbooks.Open(Filename:=sPath & sName, UpdateLinks:=1)

Calculations will still update when the worksheet is protected.



doctorjones_md wrote:

I have the following code that should open all EXCEL workbooks in a
specified path, and unprotoect any password-protected worksheets to allow
for Link Updates, then close the workbook after password protecting it.
For
some reason, I can't get this code to work -- any ideas?
================================
I put the following code in a general module of a sheet1 of a workbook

sub UpdateAllLinks()
Dim vLinkSources
Dim iLinkSource As Integer
Dim AnySheet As Worksheet
sPath = " C:\Documents and Settings\Shane\My Documents\Harcourt
Assessments\password"
sName = Dir(sPath & "*.xls")
do while sName ""
set bk = Workbook.Open(sPath & sName)
For Each AnySheet In ActiveWorkbook.Worksheets
ActiveWorkbook.Worksheets(AnySheet.Name) _
.Unprotect Password:="mypassword"
Next
vLinkSources = ActiveWorkbook.LinkSources(xlExcelLinks)
If Not IsEmpty(vLinkSources) Then
For iLinkSource = LBound(vLinkSources) To UBound(vLinkSources)
ActiveWorkbook.UpdateLink _
vLinkSources(iLinkSource), xlExcelLinks
Next
End If
For Each AnySheet In ActiveWorkbook.Worksheets
ActiveWorkbook.Worksheets(AnySheet.Name) _
.Protect Password:="mypassword"
Next
bk.Close Savechanges:=True
sName = Dir()
Loop
End Sub

Then I put a command button on worksheet1 in that workbook, and added the
following code.

Private Sub CommandButton1_click()
UpdateAllLinks
End sub

Thanks in advance for any assistance

--

Dave Peterson

--

Dave Peterson