Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.links
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.links
|
|||
|
|||
Links disappear
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 | |
#3
Posted to microsoft.public.excel.links
|
|||
|
|||
Links disappear
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 | |
#4
Posted to microsoft.public.excel.links
|
|||
|
|||
Links disappear
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 | | | | |
#5
Posted to microsoft.public.excel.links
|
|||
|
|||
Links disappear
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 | | | | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
One or More Links cannot be updated Error | Excel Discussion (Misc queries) | |||
options to update automatic links | Excel Worksheet Functions | |||
Prompt to update links | Links and Linking in Excel | |||
Deleting links to other spreadsheets | Excel Worksheet Functions | |||
can't update links...can't find links | Excel Discussion (Misc queries) |