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

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

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

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