Updating number of rows in linked excel object

[DoubleZ]

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.

 

[Peter Jamieson]

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 }

 

[DoubleZ]

So I have my range in Excel defines so that it only includes the rows that
have data in them. However, I don't know exactly how to paste that into
Word. I tried pasting a few rows, then using alt+F9 I essentially changed
the link code from:

{ LINK Excel.Sheet.8 "C:\\Users\\pjj.DOMAINX\\Documents\\My Data
Sources\\mytestdata.xlsx" "Sheet1!R1C1:R50C3" \a \f 4 \r }

to

{ LINK Excel.Sheet.8 "C:\\Users\\pjj.DOMAINX\\Documents\\My Data
Sources\\mytestdata.xlsx" "myrange" \a \f 4 \r }

When I enter alt+F9 again, the table hasn't updated. When I go to the 'home
button', then 'prepare', then 'edit links to files' and try to update the
file, it says

"Word is unable to create a link to the object you specified. Please insert
the object directly into your file without creating a link." Then by table
is replaced by a message reading, "ERROR! NOT A VALID LINK."

Now that I have my Excel range the way I need it, how do I paste the defined
range, rather than a number range? Obviously I haven't done it correctly so
far.

Thanks.

 

[Peter Jamieson]

In theory you ought to be able to define your range as a workbook range,
then do what you are doing.

Unfortunately, it does not work that way: you have to name the sheet that
the range is actually on, and the range name's scope must either be
"workbook" or the sheet name that the range is on.

e.g.

{ LINK Excel.Sheet.8 "C:\\Users\\pjj.DOMAINX\\Documents\\My Data
Sources\\mytestdata.xlsx" "Sheet1!myrange" \a \f 4 \r }