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. |
|
|
Thread Tools | Display Modes |
|
#1
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|
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 08:03 AM |
Excel Links to files on network not updating | Bobbie Jo | Worksheet Functions | 0 | January 13th, 2004 09:02 PM |
Broken links don't show old data. Help! | Marky Mark | Links and Linking | 0 | October 21st, 2003 02:03 AM |