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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> >> news:(E-Mail Removed)...
> >> > 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
|
Similar Threads
