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

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

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

Dave,

The workbooks are not protected, only the worksheets -- do you recommend
protecting the entire workbook? The accountants want the users to have
access to the data in the worksheets without having to supply a password.
The only reason they have opted to password protect the worksheets is to
force the Update Links not to occur.

The current setting on the workbooks is:

Startup Prompt: "Don't display the alert, update automatic links" -- As I
understand the requirement, the code should Open each workbook in the
directory/path, unprotect the worksheets (It's my understanding that a
password-protected worksheet with a VLOOKUP to another unpassword protected
MasterRates workbook) won't Update Links unless the password is supplied --
is this correct?) NOTE: It's the ODBC Link in the MasterRates worksheet
that's creating the Update issue -- when the Rates data in the ACCESS db
tblRates are updated, the values in the EXCEL MasterRates worksheet change,
and when the 200+ workbooks are opened, the link needs to update (but only
when this code is run).

Am I explaining this issue clearly -- I know it may sound somewhat
convoluted -- any thoughts?

Thanks In Advance
===================================

"Dave Peterson" wrote in message
...
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

I don't think that worksheet protection has anything to do with links or the
updating of links.

And I don't know anything about OBDC links, so take this lightly.

If I have a formula like in B1 of sheet1 of book1.xls:
=VLOOKUP(A1,'C:\My Documents\excel\[book3.xls]Sheet1'!$A:$B,2,FALSE)

And sheet1 of book1.xls is protected, then the formula will still evaluate when
it needs to.

BUT....

If book3.xls has a password to open, then I'll have to supply that password to
book3.xls before that formula updates.

Worksheet protection doesn't apply.

So that's not a good reason to apply worksheet protection.



doctorjones_md wrote:

Dave,

The workbooks are not protected, only the worksheets -- do you recommend
protecting the entire workbook? The accountants want the users to have
access to the data in the worksheets without having to supply a password.
The only reason they have opted to password protect the worksheets is to
force the Update Links not to occur.

The current setting on the workbooks is:

Startup Prompt: "Don't display the alert, update automatic links" -- As I
understand the requirement, the code should Open each workbook in the
directory/path, unprotect the worksheets (It's my understanding that a
password-protected worksheet with a VLOOKUP to another unpassword protected
MasterRates workbook) won't Update Links unless the password is supplied --
is this correct?) NOTE: It's the ODBC Link in the MasterRates worksheet
that's creating the Update issue -- when the Rates data in the ACCESS db
tblRates are updated, the values in the EXCEL MasterRates worksheet change,
and when the 200+ workbooks are opened, the link needs to update (but only
when this code is run).

Am I explaining this issue clearly -- I know it may sound somewhat
convoluted -- any thoughts?

Thanks In Advance
===================================

"Dave Peterson" wrote in message
...
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

--

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