A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Links and Linking
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Wish to change where links without dynamically updating data



 
 
Thread Tools Display Modes
  #1  
Old September 2nd, 2004, 12:53 PM
mark
external usenet poster
 
Posts: n/a
Default Wish to change where links without dynamically updating data

Hi,

We are using Excel 2003, have tried both versions (pre SP1 and with SP1)
We have an Excel spreadsheet that contains links to multiple files, and
enter data in a cell, for example a cell may have the formula

='H:\Expenses2004\Apr04\[file1Apr04.xls]1020'!$M46

The spreadsheet contains links to about fifty different spreadsheets, and
approximately 10000 links. Each link contains a three letter code for the
month, i.e. Apr or Jul etc.

With Excel 97 we used to be able to do a find and replace on the
spreadsheet, searching for Apr and replacing with Jul for example. This used
to perform pretty quickly.

With Excel 2003 we are finding this taking a very long time and consuming
high network resources, basically, for each of the 10000 links, it is finding
Apr and changing to Jul so we get
='H:\Expenses2004\Jul04\[file1Jul04.xls]1020'!$M46

it is also updating the values in the cell, so essentially it gets to cell
1, changes the link, opens the referenced file, reads the referenced value
and closes the file. It then goes to cell 2, changes the link, opens the
file (quite often the same file as the previous one), reads the referenced
value and once again closes the file.

Is there a way to streamline this process, whether it be,
1. It changes the referenced link but doesn't read it for the requested
value until specified (only really worthwhile with option 2)
2. Whether it opens the referenced file once and updates all values that
reference that file
3. We have found that opening all of the source spreadsheets speeds up the
process, as you would expect because it is loaded in memory (or in temp
location) and not having to open, read, close the networked copy. This isn't
acceptable as this means opening about 50 source spreadsheets all located in
different folders.

Does anyone have any ideas?

TIA
  #2  
Old September 2nd, 2004, 02:38 PM
Bill Manville
external usenet poster
 
Posts: n/a
Default

Mark wrote:
2. Whether it opens the referenced file once and updates all values that
reference that file


Edit / Links / Change Source

3. We have found that opening all of the source spreadsheets speeds up the
process, as you would expect because it is loaded in memory (or in temp
location) and not having to open, read, close the networked copy. This isn't
acceptable as this means opening about 50 source spreadsheets all located in
different folders.


You could use a macro to do it all for you (untested):

Sub ChangeLinks(FromMonth As String, ToMonth As String)
Dim vLink, vLinks
vLinks = ActiveWorkbook.LinkSources(xlExcelLinks)
If IsEmpty(vLinks) Then Exit Sub
For Each vLink In vLinks
ActiveWorkbook.ChangeLink vLink, Replace(vLink,FromMonth,ToMonth)
Next
End Sub

Sub Test()
ChangeLinks "Aug", "Sep"
End Sub

If this is too slow with the workbooks closed you could change it to:

Sub ChangeLinks(FromMonth As String, ToMonth As String)
Dim vLink, vLinks
Dim WB As Workbook
Set WB=ActiveWorkbook
Application.ScreenUpdating=False
vLinks = WB.LinkSources(xlExcelLinks)
If IsEmpty(vLinks) Then Exit Sub
For Each vLink In vLinks
Workbooks.Open Replace(vLink,FromMonth,ToMonth), ReadOnly:=True
WB.ChangeLink vLink, ActiveWorkbook.FullName
ActiveWorkbook.Close False
Next
Application.ScreenUpdating=True
End Sub



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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Links from Word no longer updating Martyn Lawson General Discussion 0 July 12th, 2004 11:08 AM
Change plotting order of data points Jon Peltier Charts and Charting 0 April 1st, 2004 10:33 PM
Writing a macro to change external links to manual updating in Excel 2000 John Wirt Links and Linking 5 February 16th, 2004 09:03 AM
Excel Links to files on network not updating Bobbie Jo Worksheet Functions 0 January 13th, 2004 10:02 PM
Broken links don't show old data. Help! Marky Mark Links and Linking 0 October 21st, 2003 02:03 AM


All times are GMT +1. The time now is 05:03 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.