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

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

The reason may well be that there is no \ at the end of

sPath = " C:\Documents and Settings\Shane\My Documents\Harcourt
Assessments\password"

so you are looking for files called password*.xls in the Harcourt
Assessments folder.

If that is not the problem, it would be helpful if you told us in what
way it failed to work - any messages, any sign of it opening any files
etc.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Bill -- thank you for your reply ...

I was thinking that the path might have been too long, so I shortened it a
bit, and added the "\" at the end of the path. When I run the macro, I get
the following Error Message:

Run-Time error 424
Object Required

and when I debug the code, it flags this line:

set bk = Workbook.Open(sPath & sName)

Here's my code:

I inserted a Module (Module1) and inserted the following code:
========================
Sub UpdateAllLinks()
Dim vLinkSources
Dim iLinkSource As Integer
Dim AnySheet As Worksheet
sPath = "C:\SOW\"
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

In Microsoft Excel Objects (Sheet1) I added a Command Button and inserted
the following code:
===================================
Private Sub CommandButton1_Click()
UpdateAllLinks
End Sub

"Bill Manville" wrote in message
...
The reason may well be that there is no \ at the end of

sPath = " C:\Documents and Settings\Shane\My Documents\Harcourt
Assessments\password"

so you are looking for files called password*.xls in the Harcourt
Assessments folder.

If that is not the problem, it would be helpful if you told us in what
way it failed to work - any messages, any sign of it opening any files
etc.

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

should be
Workbooks.Open

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Bill,

Thanks for your keen eye -- works like a charm, except for the problem that
the Finance Folks have varying passwords on individual worksheets -- they'll
need to get together and decide on a universal (shared) password, but other
than that, the code works great.

Thanks again for your help.
==================================
"Bill Manville" wrote in message
...
should be
Workbooks.Open

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup