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
|
|||
|
|||
Links
I have a worksheet which has links to another workbook.
When I copy these links with code, something happens which I do not understand. I am using Copy - Pastespecial to copy the cells containing the links. When the links are to files in the same directory as the workbook, the links are copied but them take an age to refresh, before the next column of links are copied and so on. When the links are to workbooks in a different directory, the links are copied, but then a model dialog box appears asking where the linked file is. If I click Cancel the code proceeds and when the workbooks recalculates all the links are correct. Can anyone explain this and tell me how I can avoid my code being stopped and a user input required? PWS |
#2
|
|||
|
|||
Links
Paul W Smith wrote:
When the links are to files in the same directory as the workbook, the links are copied but them take an age to refresh, before the next column of links are copied and so on. Presumably the source files are closed. Excel has to do quite a lot of work to find the source data for each of those formulas, involving reading the closed file. If the source files were open the copying would be much faster, but that may not be practical. When the links are to workbooks in a different directory, the links are copied, but then a model dialog box appears asking where the linked file is. That suggests to me that the source file is not present. What happens if you Edit / Links / select such a file / Open Source Do you need the links to be present in the cells you are copying the formulas to, or could they simply link back to the cells you are copying them from? Or could they just be values where you are copying them to? Or, if you are copying to a different sheet, consider copying the entire sheet (Edit / Move or Copy Sheet / Create a copy) and then deleting what you don't want. If you need to come back, please mention which version of Excel you are using. Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup |
#3
|
|||
|
|||
Links
Paul W Smith wrote:
When I am closing them I am closing them without saving them. (WBobj.close,,True) That looks like you are closing them and routing them to the next recipient. More importantly what can I do to speed this process up? Does it make a difference if you uncheck Tools / Options / Calculation / Save external link values in workbook A? Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup |
#4
|
|||
|
|||
Links
Yvette Thomas wrote:
Is there any other workaround\fix for this which will help Excel extract the data from the linked sources? Opening source files first will always achieve the best results. If you have Excel 2002 you can specify (Edit / Links / Startup) that links should not be updated when you open the workbook; then you can run a macro to open the sources and update the links after the workbook has been opened. If you have Excel 2000 without any service releases it would be a good idea to apply the service releases since at least one includes an improvement to opening files with links. Depending on the link formulas you might be able to cut the time down by having names defined in the source workbooks with their values being the data you want to access; then your formulas can just reference those names (e.g. ='OtherBook.xls'!MyName ) Or you could look at other ways of storing the data (e.g. in a database table which you query on opening the workbook). Bill Manville MVP - Microsoft Excel, Oxford, England No email replies please - respond to newsgroup |
Thread Tools | |
Display Modes | |
|
|