Macro to insert Linked Excel Range

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

dave.cuthill@computalog.com - 04 Jul 2005 20:19 GMT

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

Reply to this Message

Cindy M -WordMVP- - 05 Jul 2005 09:03 GMT

> 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
[quoted text clipped - 4 lines]
> {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 :-)

Reply to this Message