updating links in a macro
 

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!

updating links in a macro
 

ActiveWorkbook.Save
should force recalculation (assuming you have not turned recalculation off)
and you should not have any problem...

Try to comment out the Copy/Paste Special code and see whether you get the
errors... I suspect you would.

"hnyb1" wrote:

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!

updating links in a macro
 

I verified that I have automatic calculation enabled. I commented out the
copy/paste special code and ran a couple of times. Each time the links would
be added in and would show as #REF even through the save, but would update
moments after completion of the macro.

Does it matter that these workbooks are loaded into a SharePoint site?

"Sheeloo" wrote:

ActiveWorkbook.Save
should force recalculation (assuming you have not turned recalculation off)
and you should not have any problem...

Try to comment out the Copy/Paste Special code and see whether you get the
errors... I suspect you would.

"hnyb1" wrote:

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!

updating links in a macro
 

I don't know enough about SharePoint to say anything...

You may separate the Copy/Paste Special macro and run it after the links
have been updated...

You can also try ActiveSheet.Calculate



"hnyb1" wrote:

I verified that I have automatic calculation enabled. I commented out the
copy/paste special code and ran a couple of times. Each time the links would
be added in and would show as #REF even through the save, but would update
moments after completion of the macro.

Does it matter that these workbooks are loaded into a SharePoint site?

"Sheeloo" wrote:

ActiveWorkbook.Save
should force recalculation (assuming you have not turned recalculation off)
and you should not have any problem...

Try to comment out the Copy/Paste Special code and see whether you get the
errors... I suspect you would.

"hnyb1" wrote:

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!