Automatically Updating Cell Links On A Shared Workbook

Hi everyone

I have a shared workbook which is updated on a daily basis by around 5
different people on the same network. Some users reconfigure links to their
own MS Excel files and when they save the shared workbook all appears normal.

When other users subsequently access the file they see #REF! in the cells
previously updated with new links.

I understand that this limitation is documented within Microsoft help and
the only way around it is to click into each cell containing #REF!

Is there a way I can provide a facility to automatically refresh these links
without visiting each cell separately?

Any help very much appreciated.
Dave

what about the update links option in Edit=>Links.

--
Regards,
Tom Ogilvy


"DaveyC4S" <[email protected]> wrote in message
news:[email protected]...
> Hi everyone
>
> I have a shared workbook which is updated on a daily basis by around 5
> different people on the same network. Some users reconfigure links to
their
> own MS Excel files and when they save the shared workbook all appears
normal.
>
> When other users subsequently access the file they see #REF! in the cells
> previously updated with new links.
>
> I understand that this limitation is documented within Microsoft help and
> the only way around it is to click into each cell containing #REF!
>
> Is there a way I can provide a facility to automatically refresh these
links
> without visiting each cell separately?
>
> Any help very much appreciated.
> Dave

Hi Tom

My apologies I neglected to say that there is one more restriction to the
shared workbook in that it is a requirement that both the workbook and
individual worksheets remain protected.

The 'Update Now' button is then greyed out and the links menu only allows
sources to be opened. Opening the sources has no affect on the linked cells.

I have tried to configure some code to refresh links but have been
unsuccessful.

Dave

"Tom Ogilvy" wrote:

> what about the update links option in Edit=>Links.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "DaveyC4S" <[email protected]> wrote in message
> news:[email protected]...
> > Hi everyone
> >
> > I have a shared workbook which is updated on a daily basis by around 5
> > different people on the same network. Some users reconfigure links to
> their
> > own MS Excel files and when they save the shared workbook all appears
> normal.
> >
> > When other users subsequently access the file they see #REF! in the cells
> > previously updated with new links.
> >
> > I understand that this limitation is documented within Microsoft help and
> > the only way around it is to click into each cell containing #REF!
> >
> > Is there a way I can provide a facility to automatically refresh these
> links
> > without visiting each cell separately?
> >
> > Any help very much appreciated.
> > Dave
>
>
>

you can try

selecting all cells then

edit=>Replace
Replace what: =
Replace with: =

use an equal sign in each case. Perhaps this will do what you want. It can
of course be done with code as well.

--
Regards,
Tom Ogilvy

"DaveyC4S" <[email protected]> wrote in message
news:[email protected]...
> Hi Tom
>
> My apologies I neglected to say that there is one more restriction to the
> shared workbook in that it is a requirement that both the workbook and
> individual worksheets remain protected.
>
> The 'Update Now' button is then greyed out and the links menu only allows
> sources to be opened. Opening the sources has no affect on the linked
cells.
>
> I have tried to configure some code to refresh links but have been
> unsuccessful.
>
> Dave
>
> "Tom Ogilvy" wrote:
>
> > what about the update links option in Edit=>Links.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "DaveyC4S" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hi everyone
> > >
> > > I have a shared workbook which is updated on a daily basis by around 5
> > > different people on the same network. Some users reconfigure links to
> > their
> > > own MS Excel files and when they save the shared workbook all appears
> > normal.
> > >
> > > When other users subsequently access the file they see #REF! in the
cells
> > > previously updated with new links.
> > >
> > > I understand that this limitation is documented within Microsoft help
and
> > > the only way around it is to click into each cell containing #REF!
> > >
> > > Is there a way I can provide a facility to automatically refresh these
> > links
> > > without visiting each cell separately?
> > >
> > > Any help very much appreciated.
> > > Dave
> >
> >
> >

I had tried this approach as well but although the 'Find' option is available
within a protected worksheet the 'Replace' option is not.

Is code the only solution?

Dave

"Tom Ogilvy" wrote:

> you can try
>
> selecting all cells then
>
> edit=>Replace
> Replace what: =
> Replace with: =
>
> use an equal sign in each case. Perhaps this will do what you want. It can
> of course be done with code as well.
>
> --
> Regards,
> Tom Ogilvy
>
> "DaveyC4S" <[email protected]> wrote in message
> news:[email protected]...
> > Hi Tom
> >
> > My apologies I neglected to say that there is one more restriction to the
> > shared workbook in that it is a requirement that both the workbook and
> > individual worksheets remain protected.
> >
> > The 'Update Now' button is then greyed out and the links menu only allows
> > sources to be opened. Opening the sources has no affect on the linked
> cells.
> >
> > I have tried to configure some code to refresh links but have been
> > unsuccessful.
> >
> > Dave
> >
> > "Tom Ogilvy" wrote:
> >
> > > what about the update links option in Edit=>Links.
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > > "DaveyC4S" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Hi everyone
> > > >
> > > > I have a shared workbook which is updated on a daily basis by around 5
> > > > different people on the same network. Some users reconfigure links to
> > > their
> > > > own MS Excel files and when they save the shared workbook all appears
> > > normal.
> > > >
> > > > When other users subsequently access the file they see #REF! in the
> cells
> > > > previously updated with new links.
> > > >
> > > > I understand that this limitation is documented within Microsoft help
> and
> > > > the only way around it is to click into each cell containing #REF!
> > > >
> > > > Is there a way I can provide a facility to automatically refresh these
> > > links
> > > > without visiting each cell separately?
> > > >
> > > > Any help very much appreciated.
> > > > Dave
> > >
> > >
> > >
>
>
>

Hi Tom

Do you have any advice as to the best way of putting code together to solve
this problem?

Best regards
Dave

"Tom Ogilvy" wrote:

> you can try
>
> selecting all cells then
>
> edit=>Replace
> Replace what: =
> Replace with: =
>
> use an equal sign in each case. Perhaps this will do what you want. It can
> of course be done with code as well.
>
> --
> Regards,
> Tom Ogilvy
>
> "DaveyC4S" <[email protected]> wrote in message
> news:[email protected]...
> > Hi Tom
> >
> > My apologies I neglected to say that there is one more restriction to the
> > shared workbook in that it is a requirement that both the workbook and
> > individual worksheets remain protected.
> >
> > The 'Update Now' button is then greyed out and the links menu only allows
> > sources to be opened. Opening the sources has no affect on the linked
> cells.
> >
> > I have tried to configure some code to refresh links but have been
> > unsuccessful.
> >
> > Dave
> >
> > "Tom Ogilvy" wrote:
> >
> > > what about the update links option in Edit=>Links.
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > > "DaveyC4S" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Hi everyone
> > > >
> > > > I have a shared workbook which is updated on a daily basis by around 5
> > > > different people on the same network. Some users reconfigure links to
> > > their
> > > > own MS Excel files and when they save the shared workbook all appears
> > > normal.
> > > >
> > > > When other users subsequently access the file they see #REF! in the
> cells
> > > > previously updated with new links.
> > > >
> > > > I understand that this limitation is documented within Microsoft help
> and
> > > > the only way around it is to click into each cell containing #REF!
> > > >
> > > > Is there a way I can provide a facility to automatically refresh these
> > > links
> > > > without visiting each cell separately?
> > > >
> > > > Any help very much appreciated.
> > > > Dave
> > >
> > >
> > >
>
>
>

I finally solved this problem using the following code.

Private Sub RevalidateButton_Click()
ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources
End Sub

Best regards
Dave

"DaveyC4S" wrote:

> Hi Tom
>
> Do you have any advice as to the best way of putting code together to solve
> this problem?
>
> Best regards
> Dave
>
> "Tom Ogilvy" wrote:
>
> > you can try
> >
> > selecting all cells then
> >
> > edit=>Replace
> > Replace what: =
> > Replace with: =
> >
> > use an equal sign in each case. Perhaps this will do what you want. It can
> > of course be done with code as well.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "DaveyC4S" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hi Tom
> > >
> > > My apologies I neglected to say that there is one more restriction to the
> > > shared workbook in that it is a requirement that both the workbook and
> > > individual worksheets remain protected.
> > >
> > > The 'Update Now' button is then greyed out and the links menu only allows
> > > sources to be opened. Opening the sources has no affect on the linked
> > cells.
> > >
> > > I have tried to configure some code to refresh links but have been
> > > unsuccessful.
> > >
> > > Dave
> > >
> > > "Tom Ogilvy" wrote:
> > >
> > > > what about the update links option in Edit=>Links.
> > > >
> > > > --
> > > > Regards,
> > > > Tom Ogilvy
> > > >
> > > >
> > > > "DaveyC4S" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > Hi everyone
> > > > >
> > > > > I have a shared workbook which is updated on a daily basis by around 5
> > > > > different people on the same network. Some users reconfigure links to
> > > > their
> > > > > own MS Excel files and when they save the shared workbook all appears
> > > > normal.
> > > > >
> > > > > When other users subsequently access the file they see #REF! in the
> > cells
> > > > > previously updated with new links.
> > > > >
> > > > > I understand that this limitation is documented within Microsoft help
> > and
> > > > > the only way around it is to click into each cell containing #REF!
> > > > >
> > > > > Is there a way I can provide a facility to automatically refresh these
> > > > links
> > > > > without visiting each cell separately?
> > > > >
> > > > > Any help very much appreciated.
> > > > > Dave
> > > >
> > > >
> > > >
> >
> >
> >