I have six spreadsheets. Each links to 6 different data spreadsheets. I
just moved all of these into a couple different folders for easier
maintenance. Now I want to update the links in the main spreadsheets. I
can open any of these 42 spreadsheets no problem.

When I try to update the links, I get "unable to read file." Status for
the link becomes "error:source not found"

The source IS there. I browse to it.
The source is perfectly fine, not corrupt, full rights, all that jazz.
IF I open the data spreadsheets then update the links, all works.

Any idea why Bill is doing this to me?


Phil

Which version of Excel?
Have you used Edit / Links / Change Source to redirect the links, and
saved the result?
If so, what shows in the Edit / Links dialog, and what is the source of
the file?

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

2003, sp3 is the beast. Edit Links Change source is what I am trying to
use. I hit change source, browse to the new source file in question,
hit OK, and it responds with "unable to open file." At which point, the
status of the link in the main dialog box becomes "Error: Source not found."

But I can open that exact same file in Excel anytime I want, no
problems. Filenames vary, obviously, but are along the lines of
"Customer Report Card-ItemType-1 to G.xls"

I can also manually modify the link in the formulas, (all of them takes
quiet a while but I did it for one spreadsheet) and all works fine. It
gets the fresh new data. However, if I then go into Edit Links and hit
Check Status, they all change to "Error: Source not found."






Bill Manville wrote:

Which version of Excel?
Have you used Edit / Links / Change Source to redirect the links, and
saved the result?
If so, what shows in the Edit / Links dialog, and what is the source of
the file?

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

Are the files buried deep in a directory structure?
- I'm wondering if the total length of path + filename is longer than
Excel can cope with when the source file is closed.
- what is the path you are using?
- (and if using a drive letter, what is the UNC version)

Are the source files on a network drive?
- If so I wonder if there is an access rights problem with the higher
levels of the path

I have searched for other references to this problem on Google Groups
and in the MS KnowledgeBase without success - so it doesn't seem to be
a known or common problem.

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

s:\sales\reports\reportcards\reportcarddata\Custom er Report Card-Brand-1
to G.xls

81 characters. Some may add as many as ten more characters. Thatis
pretty long, any ideas what the limit is?

It is on a network drive, but not a rights issue. Full admin rights on
my side, no difference.
I too searched google, and didn't find a thing. The path/filename
length may be the thing, I will experiment...



Bill Manville wrote:

Are the files buried deep in a directory structure?
- I'm wondering if the total length of path + filename is longer than
Excel can cope with when the source file is closed.
- what is the path you are using?
- (and if using a drive letter, what is the UNC version)

Are the source files on a network drive?
- If so I wonder if there is an access rights problem with the higher
levels of the path

I have searched for other references to this problem on Google Groups
and in the MS KnowledgeBase without success - so it doesn't seem to be
a known or common problem.

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

Well I moved them to c:\rc\rcd, and cut the name of the file down,and
still getting the same thing. Here is an example.
c:\rc\rcd\CustNAME-1toG.xls

now the funny thing is, of six sheets I am trying to relink, not all of
them fail, but the ones that do fail are consistent. These are all
generated by the "output query" macro command from MS Access. Could
there be something about the structure of these worksheets? Note that I
can open them up through the "open source" option from the edit|link
command no problem.









Bill Manville wrote:

Are the files buried deep in a directory structure?
- I'm wondering if the total length of path + filename is longer than
Excel can cope with when the source file is closed.
- what is the path you are using?
- (and if using a drive letter, what is the UNC version)

Are the source files on a network drive?
- If so I wonder if there is an access rights problem with the higher
levels of the path

I have searched for other references to this problem on Google Groups
and in the MS KnowledgeBase without success - so it doesn't seem to be
a known or common problem.

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

Phil wrote:
These are all
generated by the "output query" macro command from MS Access.

That's it!!
I remember seeing this as an issue before - I think the symptom was
different but the reason was the same.
Can you do the querying the other way round, getting Excel to pull the
data in from Access rather than pushing the data out from Access?

Or maybe you could just copy and paste the data from the Access
workbook to a new worksheet and save that??

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

Using a cut and paste won't work. There are about 30 worksheets I would
have to cut and paste, each time we want to update this report, wich
could be a couple of times per day some days. The idea is to fill in
some dates, hit a button, and have the end user get access to the
information they seek.

I have not really looked into Excel pulling data directly from Access,
but if it meant effectively running a query for each piece, it would be
time prohibitive. Each "view" would take a couple of minutes to update.