Reply
 
Thread Tools Display Modes
  #1   Report Post  
Posted to microsoft.public.word.docmanagement
DoubleZ DoubleZ is offline
external usenet poster
 
Posts: 2
Default Updating number of rows in linked excel object

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.
  #2   Report Post  
Posted to microsoft.public.word.docmanagement
Peter Jamieson Peter Jamieson is offline
external usenet poster
 
Posts: 4,582
Default Updating number of rows in linked excel object

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.


  #3   Report Post  
Posted to microsoft.public.word.docmanagement
DoubleZ DoubleZ is offline
external usenet poster
 
Posts: 2
Default Updating number of rows in linked excel object

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" wrote:

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.



  #4   Report Post  
Posted to microsoft.public.word.docmanagement
Peter Jamieson Peter Jamieson is offline
external usenet poster
 
Posts: 4,582
Default Updating number of rows in linked excel object

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 }


--
Peter Jamieson
http://tips.pjmsn.me.uk

"DoubleZ" wrote in message
...
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" wrote:

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.




Reply
Thread Tools
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel Linked Object Formating Tried and blue Page Layout 1 November 11th 07 10:27 AM
Loss of formatting with Excel linked object itaudit Page Layout 0 October 18th 07 11:24 PM
Linked object not updating mstone Microsoft Word Help 0 July 12th 07 11:06 PM
REALLY automatic updating linked object #DIV/0 Microsoft Word Help 2 January 26th 07 12:03 PM
Embedded Linked Excel object Reggie New Users 2 July 12th 05 03:37 AM


All times are GMT +1. The time now is 11:59 AM.

Copyright ©2000 - 2023, Jelsoft Enterprises Ltd.
Copyright 2004-2023 Microsoft Office Word Forum - WordBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Word"