Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically Updating Cell Links On A Shared Workbook
what about the update links option in Edit=Links.
-- Regards, Tom Ogilvy "DaveyC4S" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically Updating Cell Links On A Shared Workbook
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" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically Updating Cell Links On A Shared Workbook
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" wrote in message ... 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" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically Updating Cell Links On A Shared Workbook
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" wrote in message ... 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" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically Updating Cell Links On A Shared Workbook
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" wrote in message ... 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" wrote in message ... 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automatically Updating Cell Links On A Shared Workbook
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" wrote in message ... 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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Links not updating automatically | Links and Linking in Excel | |||
Paste Links not updating automatically | Links and Linking in Excel | |||
Paste Links not updating automatically. | Excel Worksheet Functions | |||
Links not updating in shared workbook | Excel Worksheet Functions | |||
Links not updating automatically | Links and Linking in Excel |