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
|
|||
|
|||
contains one or more links that cannot be updated...
Ok here it goes...
Since 2000 (running Excel 97 SP2 in Windows NT on a large corporate network) we have been using the same set of excel files that link together to create our budget. We recently have updated to Office XP but the problem I'm writing about cropped up before the upgrade. The portion that it messing up is basically made up of 2 types of files. The first is the "Global Data.xls" and it contains 3 sets of cells for supplying the rest of the spreadsheets with info: - cell A2="Date"; B2 = the function Today(). It used to be an actual date but the users wanted it change to always be the current date. - cell A3="Department"; B3="Transportation and Works" - cell A4="Division"; B4="Administration" The second file is the cost center file. There are many of these files and they all reference "Global Data.xls" to get the 3 items in that file. They reference it using the formula: =VLOOKUP("department",'X:\budget 2004\Sample 1\DU1\Global Data.xls'!itemlist,2,FALSE) or =VLOOKUP("division",'X:\budget 2004\Sample 1\DU1\Global Data.xls'!itemlist,2,FALSE) We arrange the files for different decision units (groups of cost center files) into a single folder of their own. In each decision unit folder, along with the cost center files there is a single Global Data file that the files in that folder draw from. So in the example above the cost center file with these formulae are in the 'X:\budget 2004\Sample 1\DU1\' along with the Global Data file. The Decision Unit folder(s) is inside a division folder to keep things organized. This has been working fine for 3 years. This year a single pair of users (who share the probem files) on another part of our network have started getting "Workbook contains one or more links that cannot be updated" errors in *some* files when they open them and answer "Yes" to "update links". The cost center workbook has alot of VBA code for doing different thing. One is to recreate these links by putting the VLOOKUP formulae back into the necessary cells. sGlobalsFile = ActiveWorkbook.Path & "\Global Data.xls" Range("global_data_dept").Formula = "=VLOOKUP(" & Chr(34) & "department" & Chr(34) & ",'" & sGlobalsFile & "'!itemlist,2,false)" If the users run this bit of code the links return to normal and everything is fine. But then a while later they'll open the workbooks again and it will have the error again! Now this is the grey area that I wish I could find out more about from the users... who's opening these files and what are they doing with them (moving them around?) They say they are doing nothing different then they were doing with any other of their files. They may have a folder with 10 cost centers & 1 Global Data and they will "all of a sudden" start getting this error on a single cost center file. So once they get this error and click "Continue" to get to their workbook (in Excel 97 the would get an open file dialog box to indicate that Global Data counldn't be found), the cells that have the link to Global Data now have a formula like this: =VLOOKUP("department",'C:\WINNT\Profiles\gjones\Ap plication Data\Microsoft\Excel\XLSTART\X9TRANWORK\X9WATERWRK \X9WAW01\Global Data.xls'!itemlist,2,FALSE) What I notice in this new formula is that the normal "X:" drive has been lost and now Excel is trying to find it in ...XLSTART. I have MANY other users on our network that have not encountered this problem with their hundreds of files. Does anyone have a slight clue as to what the problem is? Or even how I might try to reproduce it ! Thanks, Gord |
Thread Tools | |
Display Modes | |
|
|