Macro to insert Linked Excel Range

[dave.cuthill]

I am trying to figure out the best method of inserting an excel named
range into a word document. I have determined that using the LINK field
works well when using a specific range but I need to be able to
accomplish the same thing using vba since the name of the file is
dynamic.

{LINK Excel.Sheet.8 "C:\\PES EOB Design Sheet_test.xls" Data!Print_Area
\a \f 0 \p}

How do I replicate this functionality within vba? Recording a macro
does not seem to accomplish the same result - I end up with ...

Selection.PasteSpecial Link:=True, DataType:=wdPasteOLEObject,
Placement:= wdInLine, DisplayAsIcon:=False

even though when I view the codes the end result seems to be the same
within the document.

David

 

[Cindy M -WordMVP-]

I am trying to figure out the best method of inserting an excel named

range into a word document. I have determined that using the LINK field
works well when using a specific range but I need to be able to
accomplish the same thing using vba since the name of the file is
dynamic.

{LINK Excel.Sheet.8 "C:\\PES EOB Design Sheet_test.xls" Data!Print_Area
\a \f 0 \p}

Off the top of my head, so syntax errors are possible:

ActiveDocument.Fields.Add Type:=wdFieldLink, _
Text:="Excel.Sheet.8 " & Chr(34) & "C:\\PES EOB Design
Sheet_test.xls" & Chr(34) & " Data!Print_Area \a \f 0 \p", _
PreserveFormatting:=False

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail