#### Links to closed files not opening.

I have a customer with a set of rather convoluted mish-mash of spreadsheets,
basically running an entire company.  One spreadsheet  (SSa)  has a number
of links to 4 others (SSb,c,d).  Everything works as expected if SSb,c,d are
open, & then SSa is opened, but if SSa is opened before SSb,c & d, cells in
SSa display #VALUE!.  I have tried the same set of files on several
machines, running Excel 2000,XP & 2003, all act in the same manner.  I have
tried to recreate the problem with a set of files linked in a similar way,
but in any case, the links were able to access data in closed files without
problem.

Any pointers on how to troubleshoot this problem would be greatly
appreciated!

Mal Osborne
MCSE MVP (not excel)  Mensa

 0
malcolmo (1)
1/19/2004 1:24:42 PM
excel 39879 articles. 2 followers.

2 Replies
354 Views

Similar Articles

[PageSpeed] 14

I think the problem is either the way the links were created in the
destination spreadsheet, or the settings some of the workbook-specific
options.

First, in SSa.xls, go to Tools>Options>Calculation and make sure that
'Update remote references' is checked.  This option is saved with the
workbook.

If that doesn't work, in SSa.xls, click Edit>Links...  This will bring
up the Links dialog (surprisingly).  Select one of the links, and
click change source. Navigate to the same file and click 'OK'.  Repeat
for the other links.  Make sure 'Automatic' updating is selected at
the bottom of the dialog for all of the links.

You may have to rebuild the links manually, which should be a last
resort.  Check the formula in the linked cells in SSa.xls - it should
be something like:

=[SSb.xls]sheet1!A1

If these suggestions don't work, well, I have run out of Excel ideas,
but here is a creative workaround.  Create a batch file that will open
the sheets in the right order.  Create a new file in a text editor:

start SSd.xls
start SSc.xls
start SSb.xls
start SSa.xls

Now save it as SSa.bat.  If they are using Windows 2000 or XP, the
command window will close after the last sheet opens.  On
Windows95/98/ME, create a shortcut to the file and check the 'Close on
Exit' setting.  When they use this shortcut, all the files will open,
with SSa.xls being the active file.  Not the best solution, but it
will work till we can straighten out the links problem.

-Mike

On Mon, 19 Jan 2004 21:24:42 +0800, "Mal Osborne"
<malcolmo@silverfern.com.au> wrote:

>I have a customer with a set of rather convoluted mish-mash of spreadsheets,
>basically running an entire company.  One spreadsheet  (SSa)  has a number
>of links to 4 others (SSb,c,d).  Everything works as expected if SSb,c,d are
>open, & then SSa is opened, but if SSa is opened before SSb,c & d, cells in
>SSa display #VALUE!.  I have tried the same set of files on several
>machines, running Excel 2000,XP & 2003, all act in the same manner.  I have
>tried to recreate the problem with a set of files linked in a similar way,
>but in any case, the links were able to access data in closed files without
>problem.
>
>Any pointers on how to troubleshoot this problem would be greatly
>appreciated!
>
>Mal Osborne
>MCSE MVP (not excel)  Mensa
>

Mike Argy
Custom Office Solutions
and Windows/UNIX applications

Please post on-topic responses to the newsgroup

To e-mail me, remove nospam from the address in the headers
 0
mikeargy (62)
1/19/2004 11:53:20 PM
I've seen some posts that say that if the workbooks are old (created in an
earlier version than what you're running now), then excel will want to
recalculate the workbook on opening.

If you answer no to the links, then you'll get that #value! error.

Any chance this describes your problem.

If it does, then just Edit|Links|Update Links.

====
And I've gotten #ref! errors with links to closed workbooks--if the links were
embedded in =indirect()'s.  But that doesn't seem to quite fit your scenario.

Mal Osborne wrote:
>
> I have a customer with a set of rather convoluted mish-mash of spreadsheets,
> basically running an entire company.  One spreadsheet  (SSa)  has a number
> of links to 4 others (SSb,c,d).  Everything works as expected if SSb,c,d are
> open, & then SSa is opened, but if SSa is opened before SSb,c & d, cells in
> SSa display #VALUE!.  I have tried the same set of files on several
> machines, running Excel 2000,XP & 2003, all act in the same manner.  I have
> tried to recreate the problem with a set of files linked in a similar way,
> but in any case, the links were able to access data in closed files without
> problem.
>
> Any pointers on how to troubleshoot this problem would be greatly
> appreciated!
>
> Mal Osborne
> MCSE MVP (not excel)  Mensa

--

Dave Peterson
ec35720@msn.com
 0
ec35720 (10082)
1/19/2004 11:58:09 PM

Similar Artilces:

Why can't I convert quatro pro .wb3 file in Excel #2
I have trid everything suggested in Excel and on-line but can't convert my *.wb3 file in Excel 2003. Downloaded the Office converter file and ran it, still nothing appropriate in add-ins. What am I doing wrong? Thanks, Tom ...

Opening Email #2
I am using "owa" to log into my email at work. Only on my home computer, once I log in, I can get into my email box, move from box to box, i.e., sent, draft, etc., however, if I click on an email to read it nothing happens. And interestingly enough I can check and email and delete it, but I can't read it. Any thoughts on what he problem is? This just started yesterday, prior to this everything was fine Thanks, John ...

Opening Outlook notes in Word
I used to use my Palm software to crease "Memos." Now that I am synching my Treo with Outlook my memos are now "Notes." Problem is, now I can't view and edit my notes in MSWord. How can I convert an individual Outlook note into a Word document? It was simple with Palm: just drag it to the Word button. Please help! Copy and paste the the text into Word and then copy it back again when done editing. -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -Backup and Restore -Create an Office XP CD slipstreamed with Service Pack 3 ----- &quo...

PWA site not opening in MSPS 2010
Hi i have installed MSPS 2010 with MOSS 2010. The setup was sucessful and even Sharepoint configuration was sucessful and created a PWA site also. But i am facing an issue with the site as i am not able to open the site. Also, since when the PWA site is created, the IIS default website is also giving HTTP 500 error. I am getting this error whenever i am accessing the PWA site: "This problem can be caused by a variety of issues, including: ?Internet connectivity has been lost. ?The website is temporarily unavailable. ?The Domain Name Server (DNS) is not reachable. ?The Domai...

Outlook
Can anyone help me? I just realized that my Outlook/hotmail account has been sending attachment attachments as *.dat files. This occurs randomly, and I don't how to prevent it. I read the article, "How to Prevent the Winmail.dat File from Being Sent to Internet Users" http://support.microsoft.com/?kbid=138053. But, I don't understand how to configure my personal address book. I don't even use it. I have contacts set up in Outlook, but I had no idea this was affecting the way their email messages were sent. I use my hotmail account to sent out my resumes...

Copying files to disc with pictures
Hi, I have tried to copy my publisher files from my hard drive to a cd but when I open the files from the cd on a different computer the pictures do not show up. Anyone know how I copy so that the pictures are included as well? Thanks, Brian If the images are linked rather than imbedded, the images will not copy. Otherwise Publisher does not do well opening from a removable disk. Drag the file to your hard drive and open again. -- Mary Sauer MS MVP http://office.microsoft.com/ http://www.msauer.mvps.org/ news://msnews.microsoft.com "Brian" <anonymous@discussions.microsoft...

When I close Outlook it swaps my connection settings back from LAN
I have recently set up a wireless connection for my laptop and have changed the settings in Outlook's e-mail connection tab to "Connect using my LAN. When I close and re-open Outlook, it changes the setting back to the dial-up one . I am use two laptops that I do not use simultaneously.Grateful for any enlightenment . ...

Open a file 03-18-08
Sorry, I posted this in a different group because it forgot to cross post: I'm trying to create a button that will open a file in access 2000. The location of the file is D:\Test\label.xff. If I run this in the command prompt, it opens the file with no problems. If I create an event on a button to open the file I get an error. The VB code is this: Private Sub RunForms_Click() Dim RetVal RetVal = Shell("D:\Test\label.xff", 1) End Sub Why won't this work if it works in the command prompt? Thanks, Jasper It doesn't work because Shell requires the name of a...

Excel 2007
Hi, I wanted to post a fix to a common problem I've seen. There are many posts on the internet concerning this but I've only seen the true fix once. Here's the problem, someone installs Office 2007 and then when they try to double click on an Excel file, it opens Excel but the Excel file itself does not open. So, you are left looking at the Excel 2007 screen, but it is blank with no workbooks open (not even the default one that usually opens). Here is the official Microsoft solution: http://support.microsoft.com/kb/924074 - however, this only fixes about 20% of the case...

Creating forms in Access or Excel that link to Visio Documents
Does anybody have any experience with creating forms in access or excel that are integrated with Visio? How would I go about doing this? Access by definition can store 'objects', which can include visio drawings. If prompted, don't select display icon a.) create table add oledb field add text field for name b.) open table select oledb field and right click => insert new microsoft visio edit the new drawing and close it c.) create a form based on new table drag the oledb field onto the form d.) open the form what are you attempting to do? al &quo...

link transactions to "bills and deposits" #2
I've now set up my "bills and deposits" and everything seems to be working except for one thing. The "bills and deposits" is not picking up the downloaded "apay" transactions. So, "bills and deposits" is telling me that my electric bill is overdue, even though the apay transaction was downloaded. Is the "bills and deposits" component supposed to submit a transaction on my checking account so I can link it to the downloaded transaction? Thanks, DB01 ...

Linking POP to SY03900
Can anyone tell me what table the NOTEINDX is in for POP (Purchases)? I can find the comment in SY03900 and it's associated NOTEINDX, but I can't find where the NOTEINDX is for the actual POP. Thanks. Hello, The following fields on the POP10100 table contain the Noteindex that is linked to the SY03900 table. Note Beside the PO Number is stored in Field PONOTIDS_1 Note beside the Buyer ID is stored in field PONOTIDS_2 Thanks Don Craig "The Phone Guy" wrote: > Can anyone tell me what table the NOTEINDX is in for POP (Purchases)? I can > find the comment in...

Runtime Error When Closing Outlook
When i close Micrsoft Outlook 2000, I receive the following error: Microsoft Visual C++ Runtime Library (Path to Outlook.exe) Abnormal Program Termination. I tried to turn off the Exchang Scan in the Add-In Manager. What else can I do? I am getting the same error that Linda has described. This just started two weeks ago. I have tried reinstalling Outlook twice without success. Does anyone have a solution to this annoying problem? ...

csv file drops zeros at beginning of a set of Numbers
I have programs that only import the csv format. When I create the file with a text field using number such as 0101, 0321 if I close and reopen the file it drops the zero. How do I correct this? Lisa Three ways 1) If all the 'numbers' are the same length (4 characters) then via Format>Cells...Number>Custom, type 0000 2) Pre-format the cells to text and it will retain the leading zeroes. 3) rename the file with no extension or a *.txt one and then when you open it Excel will invoke the import wizard, choose delimited, comma and then in the third stage of the wizard, mar...

How to Sort navigation links on entity form?
Hi, Is it possible to sort or group the navigation links on the entity form? Thanks ...

Outlook 2000 opens slowly
When opening Outlook 2000 it takes approix. 1 and a half min. to open. I am using MS Exchange server and have tried it with multiple logins. It is only slow on one computer. It will also open emails slowly. I pinged the server and it was still normal. ...

Open files while login, Pls help
Hello Everybody, I have many files that open while login to the the system using Start-All programs-Startup. However, I have noticed that whatever files that has been saved at the end of day are not reflect the same(whatever data i have saved) when i login to the system a very next morning. Could you pls help on this issue Thanks and Regards Ashfaq Noorie. PS: i'm using version 2007 excel. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree&...

Accidental Saved over Excel File
I was clicking on a help topic and realized as it was loading the help screen, that I needed to click on the other topic. I clicked several times and then somehow it clicked something in the background in the worksheet, my worksheet is now blank, and all the data is gone. Is there ANYWAY at all to get the workbook again in the condition it was before the accidental last save??? Sorry to Say, but AFAIK there is no way to recover your files except of course if you have made a back up copy. "Tom" <wilsontb@bellsouth.net> wrote in message news:00b001c38fb8$69c6da60$a00128...

PDF or Image file transfer to Excel
Is there any possible way to pull the information off an image onto Excel? For example, Blue Company receives a remittance advice each month from Red Customer. This remittance advice is received in PDF format and is hundreds of pages. Is there anyway to take this remittance advice in PDF format and translate it to Excel? That way all the invoices on this remittance advice do not have to be manually keyed? Any help would be great!!! Thanks!!! Ryan You can convert the pdf to an html file, and then open it up in Excel. Of course you'll lose formatting but you can reconstruct it ...

getting VERY close in getting RPC_over_HTTP right.OWA warning is gone
Hi guys (Exc2003 and outlook 2003) My first warning is gone after changing my url to be the same as certificate name.Thanks to oliver and andy.If i now try and connect by entering "outlook.exe /rpcdiag" in the run box i keep on getting the box where i must enter credentials. If i ping "servername.externaldomain.co.za" then i get a reply. However the box that keeps on popping up after i put in my credentials is trying to connect to "servername.thedomainthatweusewithinournetwork.local". If i try and ping this i get a request timed out which tells me it tryin...

Setting Report field properties on open
I am trying to set a field so it does not get printed in a report when it opens. I used to OpenARG statement and it passes the variable fine. However when trying to turn the field visiblity to false I get an error. Strange????? Can you change report fields visibility so certain fields do not get printed on the fly like this? I tried this with no luck. Private Sub Report_Open(Cancel As Integer) If Not IsNull(Me.OpenArgs) Then InputArgs = Me.OpenArgs Select Case InputArgs Case "NoValues" txtUM_QTY.Report.Visible = False '&l...

File Save in network
My application has Open,Save,SaveAs menu for its configuration file. Generally configuration files are created in network server so that they can be shared and I have trouble in saving an existing file to network server. I mean, it can create and save as a new file but if I open an existing file and try to save, then I got warning msg like "Access to \\server\folder1\folder2\conf.bin was denied"(I have permission to write to the folder). Do you think this is an application problem? Just for test, I tried saving in local folder and it works. // // Open // void CMyApp::OnFileOpen() { ...

Cannot open Publisher file #2
I created a newsletter in Publisher last week and went to open it yesterday to finish it, but I keep getting a message saying Publisher cannot open the file. I have been working on it all week and had no problems, but suddenly it won't open. I haven't a clue as to what is wrong with the file or what could have happened. I use Publisher 2000 right now and have never had this happen before. I can't even find any backup for the file, which I usually have. Has anyone ever experienced this problem and if so what caused it? If I have to recreate this whole thing I am going to h...

File being "used by another user"
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I created a file named Onebook several days ago. When I went to open it again, I got an error message that there was a problem with the text. Now when I to open it, I get a message that say it is in use by another user. <br><br>I've tried to delete it, but I cannot because it is &quot;in use.&quot; <br><br>Anyone have a clue how this happened or how to correct it? To correct it, restart the computer. If the indication is true (the file really is in use...) then some hidd...

MSN Explorer using Money file on hard drive?
I am a computer consultant and was hired by a new client this past week to solve his Money issues. I am a little bewildered by what I have seen. Point by point... 1. He and said that things he entered were "not sticking". He is VERY elderly and not that savvy with PCs, so I took this with a grain of salt...BUT... 2. He is using MSN Explorer to manage his portfolio, so I thought it was all online. 3. Upon looking at his portfolio and then closing MSN Explorer, we got a message on the screen that his "file was being compressed". That sent up a big red flag to ...