Links disappear

Hello everyone

I posted this question a few days ago and got no response - I'm hoping
this will be a clearer explanation.

I client has presented me with a number of Excel files that he wants
linked to a master spreadsheet. These are all *.xls files, and are
about 11k in size, and are updated periodically from Access.

When I link a new workbook (target) to one of these files (source),
the link works until I close the target. Then, if I try to update the
link (EditLinksUpdate Now) I get an "Unable to read file" message.

If I save the target, the file size jumps from 11k to 19k, and the
links work perfectly. At first I thought this might be a version
issue, but running Activeworkbook.Fileformat in VBA on the original
file comes back with -4143 (Excel 97/2000). I'm running Excel 97, so
I tried it on a friend's system with Excel 2003, got the same
results.

I'm running out of ideas, any insight into this would be greatly
appreciated.

Regards,

DaveU

Hi Dave,

Sorry, you can't do that ...

If your new workbook is closed without ever being saved, then there's nothing left for the other one to link to. Hence the error.

Cheers

--
macropod
[MVP - Microsoft Word]


"Dave" wrote in message ups.com...
| Hello everyone
|
| I posted this question a few days ago and got no response - I'm hoping
| this will be a clearer explanation.
|
| I client has presented me with a number of Excel files that he wants
| linked to a master spreadsheet. These are all *.xls files, and are
| about 11k in size, and are updated periodically from Access.
|
| When I link a new workbook (target) to one of these files (source),
| the link works until I close the target. Then, if I try to update the
| link (EditLinksUpdate Now) I get an "Unable to read file" message.
|
| If I save the target, the file size jumps from 11k to 19k, and the
| links work perfectly. At first I thought this might be a version
| issue, but running Activeworkbook.Fileformat in VBA on the original
| file comes back with -4143 (Excel 97/2000). I'm running Excel 97, so
| I tried it on a friend's system with Excel 2003, got the same
| results.
|
| I'm running out of ideas, any insight into this would be greatly
| appreciated.
|
| Regards,
|
| DaveU
|

Hi macropod

Thanks for your reply - sorry, that was a typo (too many 3:00AM
sessions lately) - that paragraph should have read:

"When I link a new workbook (target) to one of these files (source),
the link works until I close the SOURCE file. Then, if I try to
update the link (EditLinksUpdate Now) I get an "Unable to read file"
message. "

And yet, the complete path and file name show up correctly in the
formula bar (of the target).

And oddly enough, if I open the SOURCE file, and then save and close
it, everything is as it should be. The size of the SOURCE file grows
from 11k to 19k, so something has changed here. And I know that this
sounds like an obvious solution, but the client has over 100 of these
files, and they're being updated regularly by Access over a network -
so I'm a bit hesitant to make unnecessary changes to these files.
That will be my 'last resort' strategy.

I've work with linking files quite frequently, and I've never
encountered this problem before. Again, any insight into this will be
greatly appreciated.

Regards

DaveU


On Feb 2, 8:28 pm, "macropod" wrote:
Hi Dave,

Sorry, you can't do that ...

If your new workbook is closed without ever being saved, then there's nothing left for the other one to link to. Hence the error.

Cheers

--
macropod
[MVP - Microsoft Word]

"Dave" wrote in oglegroups.com...

| Hello everyone
|
| I posted this question a few days ago and got no response - I'm hoping
| this will be a clearer explanation.
|
| I client has presented me with a number of Excel files that he wants
| linked to a master spreadsheet. These are all *.xls files, and are
| about 11k in size, and are updated periodically from Access.
|
| When I link a new workbook (target) to one of these files (source),
| the link works until I close the target. Then, if I try to update the
| link (EditLinksUpdate Now) I get an "Unable to read file" message.
|
| If I save the target, the file size jumps from 11k to 19k, and the
| links work perfectly. At first I thought this might be a version
| issue, but running Activeworkbook.Fileformat in VBA on the original
| file comes back with -4143 (Excel 97/2000). I'm running Excel 97, so
| I tried it on a friend's system with Excel 2003, got the same
| results.
|
| I'm running out of ideas, any insight into this would be greatly
| appreciated.
|
| Regards,
|
| DaveU
|

Hi Dave,

Was the source file created by another app (eg Access)? If so, read:
http://support.microsoft.com/kb/824172/en-us
which says you need to open all the linked Excel workbooks before you open the workbook that contains the links.

Cheers

--
macropod
[MVP - Microsoft Word]


"Dave" wrote in message oups.com...
| Hi macropod
|
| Thanks for your reply - sorry, that was a typo (too many 3:00AM
| sessions lately) - that paragraph should have read:
|
| "When I link a new workbook (target) to one of these files (source),
| the link works until I close the SOURCE file. Then, if I try to
| update the link (EditLinksUpdate Now) I get an "Unable to read file"
| message. "
|
| And yet, the complete path and file name show up correctly in the
| formula bar (of the target).
|
| And oddly enough, if I open the SOURCE file, and then save and close
| it, everything is as it should be. The size of the SOURCE file grows
| from 11k to 19k, so something has changed here. And I know that this
| sounds like an obvious solution, but the client has over 100 of these
| files, and they're being updated regularly by Access over a network -
| so I'm a bit hesitant to make unnecessary changes to these files.
| That will be my 'last resort' strategy.
|
| I've work with linking files quite frequently, and I've never
| encountered this problem before. Again, any insight into this will be
| greatly appreciated.
|
| Regards
|
| DaveU
|
|
| On Feb 2, 8:28 pm, "macropod" wrote:
| Hi Dave,
|
| Sorry, you can't do that ...
|
| If your new workbook is closed without ever being saved, then there's nothing left for the other one to link to. Hence the
error.
|
| Cheers
|
| --
| macropod
| [MVP - Microsoft Word]
|
| "Dave" wrote in oglegroups.com...
|
| | Hello everyone
| |
| | I posted this question a few days ago and got no response - I'm hoping
| | this will be a clearer explanation.
| |
| | I client has presented me with a number of Excel files that he wants
| | linked to a master spreadsheet. These are all *.xls files, and are
| | about 11k in size, and are updated periodically from Access.
| |
| | When I link a new workbook (target) to one of these files (source),
| | the link works until I close the target. Then, if I try to update the
| | link (EditLinksUpdate Now) I get an "Unable to read file" message.
| |
| | If I save the target, the file size jumps from 11k to 19k, and the
| | links work perfectly. At first I thought this might be a version
| | issue, but running Activeworkbook.Fileformat in VBA on the original
| | file comes back with -4143 (Excel 97/2000). I'm running Excel 97, so
| | I tried it on a friend's system with Excel 2003, got the same
| | results.
| |
| | I'm running out of ideas, any insight into this would be greatly
| | appreciated.
| |
| | Regards,
| |
| | DaveU
| |
|
|

Hi macropod

That's precisely what's going on here, Access is exporting to Excel.
Now that I realize what's going on, I can look for an alternate
(albeit slower) approach.

Thanks so much for this,

regards,

Dave


On Feb 4, 1:52 am, "macropod" wrote:
Hi Dave,

Was the source file created by another app (eg Access)? If so, read:http://support.microsoft.com/kb/824172/en-us
which says you need to open all the linked Excel workbooks before you open the workbook that contains the links.

Cheers

--
macropod
[MVP - Microsoft Word]

"Dave" wrote in ooglegroups.com...

| Hi macropod
|
| Thanks for your reply - sorry, that was a typo (too many 3:00AM
| sessions lately) - that paragraph should have read:
|
| "When I link a new workbook (target) to one of these files (source),
| the link works until I close the SOURCE file. Then, if I try to
| update the link (EditLinksUpdate Now) I get an "Unable to read file"
| message. "
|
| And yet, the complete path and file name show up correctly in the
| formula bar (of the target).
|
| And oddly enough, if I open the SOURCE file, and then save and close
| it, everything is as it should be. The size of the SOURCE file grows
| from 11k to 19k, so something has changed here. And I know that this
| sounds like an obvious solution, but the client has over 100 of these
| files, and they're being updated regularly by Access over a network -
| so I'm a bit hesitant to make unnecessary changes to these files.
| That will be my 'last resort' strategy.
|
| I've work with linking files quite frequently, and I've never
| encountered this problem before. Again, any insight into this will be
| greatly appreciated.
|
| Regards
|
| DaveU
|
|
| On Feb 2, 8:28 pm, "macropod" wrote:
| Hi Dave,
|
| Sorry, you can't do that ...
|
| If your new workbook is closed without ever being saved, then there's nothing left for the other one to link to. Hence the
error.
|
| Cheers
|
| --
| macropod
| [MVP - Microsoft Word]
|
| "Dave" wrote in oglegroups.com...
|
| | Hello everyone
| |
| | I posted this question a few days ago and got no response - I'm hoping
| | this will be a clearer explanation.
| |
| | I client has presented me with a number of Excel files that he wants
| | linked to a master spreadsheet. These are all *.xls files, and are
| | about 11k in size, and are updated periodically from Access.
| |
| | When I link a new workbook (target) to one of these files (source),
| | the link works until I close the target. Then, if I try to update the
| | link (EditLinksUpdate Now) I get an "Unable to read file" message.
| |
| | If I save the target, the file size jumps from 11k to 19k, and the
| | links work perfectly. At first I thought this might be a version
| | issue, but running Activeworkbook.Fileformat in VBA on the original
| | file comes back with -4143 (Excel 97/2000). I'm running Excel 97, so
| | I tried it on a friend's system with Excel 2003, got the same
| | results.
| |
| | I'm running out of ideas, any insight into this would be greatly
| | appreciated.
| |
| | Regards,
| |
| | DaveU
| |
|
|