When you insert a link, Word creates a LINK field that either specifies a
fixed range of cells (e.g. Sheet1!R1C1:R50C5) or a range name, (e.g.
Sheet1!myrange ) if you happen to select an area in the Excel worksheet that
corresponds to a range name.
So if you have full control over the worksheet and can redefine the range
name so that it always corresponds to the area you want to include, you can
use that approach. ideally there would be a range name that defines the
"UsedRange" but I don't think there is - probably worth checking with an
Excel person.
Otherwise, you have to know how many rows and columns you need and alter
that R1C1:R50C5 name. However, if you always need the same columns but do
not know how many rows, you can /try/ Sheet1!C1:C5 - I know it "works", but
what I do not know is how it behaves if you have blank rows and so on.
Further, if you paste special-insert link and use either the Microsoft
Office Excel Worksheet Object or any of the graphical formats, I don't think
they can ever span more than one page. You probably need to use Formatted
text (RTF) if you want something in Word table format, or Unformatted
text/Unformatted Unicode text for a plain list.
To modify the link, use Alt-F9 to reveal what you inserted - e.g.
{ LINK Excel.Sheet.8 "C:\\Users\\pjj.DOMAINX\\Documents\\My Data
Sources\\mytestdata.xlsx" Sheet1!R1C1:R50C3 \a \f 4 \r }
then change the reference as suggested above, e.g.
{ LINK Excel.Sheet.8 "C:\\Users\\pjj.DOMAINX\\Documents\\My Data
Sources\\mytestdata.xlsx" Sheet1!C1:C3 \a \f 4 \r }
--
Peter Jamieson
http://tips.pjmsn.me.uk
"DoubleZ" wrote in message
...
In Word 2007 I have a linked excel object. The excel sheet is going to
greatly vary in length from application to application. Is it possible
for
the object in Word to change length based upon how many rows are filled in
the excel file? This would be nice because otherwise I will have to paste
the maximum amount of rows that can be populated so that way it ensures I
will never be missing any data in Word. This means that anytime I am not
using the max amount I will empty cells showing in Word.
Thanks.