updating links in a macro

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Hi. I'm attempting to write a basic macro that imports from an excel file
(called template) information into a spreadsheet. Once imported, I need to
copy and paste that information in as values, so that they don't change once
the template file changes. This is all working o.k., except that it appears
that the copy and paste as values is happening before all the links are
finished updating, so sometimes the last couple of cells show #REF#. This is
especially true if the template file is not open when the macro is running,
but also sometimes happening even if it is open. Can anyone suggest code to
ensure all links have finished updating before progressing with the macro.
See example code below.

There is a whole row of cells that have formulas like the one below entered
in...
ActiveCell.Formula = "=[Template.xls]Process Evaluation'!$L$9"
ActiveWorkbook.UpdateLink , xlLinkTypeOLELinks
ActiveWorkbook.Save
Range(Selection, Cells(ActiveCell.Row, 1)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Selection.Cells(ActiveCell.Row, 1).Select
Application.CutCopyMode = False

I have Excel 2003 on Windows XP. Thanks in advance!

.

Relevant Pages

  • RE: Import file to excel
    ... normally use (imports correctly) and on the another pc it's also the same but ... If so open the file using Notepad a look at the data ... Was a macro used to import the data. ... Here's my problem...When I import using the pc the excel file was created on ...
    (microsoft.public.excel.programming)
  • variant on copying excel data into word table
    ... I have 2 PCs, PCa & PCb, which share a common file system. ... On PCa I have an excel file linking to an external data source - ... so I cannot open the excel file from the word macro since the links in the ... template to paste values into my word file. ...
    (microsoft.public.word.vba.general)
  • Excel Reports from MS Access
    ... But I need to use a template ... associate it to the new excel file from Access. ... Also I need to use a macro to do some formatting. ...
    (microsoft.public.excel.worksheet.functions)
  • Excel Report
    ... But I need to use a template ... associate it to the new excel file from Access. ... Also I need to use a macro to do some formatting. ...
    (microsoft.public.excel.programming)
  • Formulas on a template
    ... excel template that has been created - that imports the data via ... macro from access?? ...
    (microsoft.public.excel.worksheet.functions)