Is it possible to reference a Microsoft Excel cell in Word?

[wardnine]

Say I have an Excel worksheet with some cells I want to export to
Word... is it possible to reference those Excel cells from Word? Is it
possible to, for example, give the location of the file, workbook,
worksheet, and cell location somewhere in the Word application and from
that, output whatever is in that cell into the Word document? If
possible, is it possible to do this not using VBA?

The cells I think will always be in a fixed position (e.g., I would
always want to export cells A3, A11, A24, A94, A113, A135, etc.) into
Word, producing an output of whatever is in that field (the fields will
always contain 2-3 sentences worth of words). Would it be possible even
to type in some type of formula directly into the Word document (e.g.,
you can type a cell reference formula like =Sheet2!C11 into an Excel
cell to output the value of C11 from another sheet into that cell) that
allows you to output whatever is in that cell right there? Thanks for
any help...

 

[Jay Freedman]

Open the Excel spreadsheet, select the cells you want, and copy to the
clipboard.

Switch to Word, select Edit > Paste Special, and click the "Paste link"
option button and then OK.

If you change something in the spreadsheet and save it, the copy in Word
will update to match it.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.

 

[Jezebel]

Not only possible, but very easy.

Insert a LINK field: { LINK Excel.Sheet [Filename]
[SheetName!CellReference] }. If you open the spreadsheet, copy the cell you
want, then switch to Word, you can use Edit > PasteSpecial with the 'Paste
link' option to do this automatically.

Check Help for the field switches.

You need to double the backslashes in the file name.

You'll make life easier for yourself in the long run if you use named cells,
rather than using cell references. Then your Word references are more
readable and you can rearrange your worksheet without have to change the
Word document.

 

[wardnine]

Not only possible, but very easy.

Insert a LINK field: { LINK Excel.Sheet [Filename]
[SheetName!CellReference] }. If you open the spreadsheet, copy the cell you
want, then switch to Word, you can use Edit > PasteSpecial with the 'Paste
link' option to do this automatically.

Check Help for the field switches.

You need to double the backslashes in the file name.

You'll make life easier for yourself in the long run if you use named cells,
rather than using cell references. Then your Word references are more
readable and you can rearrange your worksheet without have to change the
Word document.

Thanks for your advice (and also Jay's) as this is what I think I
need for my file. But I'm not sure on the exact syntax to make this
work? I went to Insert->Fields->Links and References->Link. Am I
supposed to type everything in to the white box that appears there at
this point? I typed in:

LINK Excel.Sheet "H:\\Book1.xls" [Sheet1!A1]

EXACTLY as written above (with brackets and quotes and everything) and
got an "Error! Not a valid link." error message. How EXACTLY is this
supposed to be written? Can you tyoe that code directly into the Word
document and get it to work? I am using Word and Excel 2000... thanks
again for any help!!

 

[wardnine]

Not only possible, but very easy.

Insert a LINK field: { LINK Excel.Sheet [Filename]
[SheetName!CellReference] }. If you open the spreadsheet, copy the cell you
want, then switch to Word, you can use Edit > PasteSpecial with the 'Paste
link' option to do this automatically.

Check Help for the field switches.

You need to double the backslashes in the file name.

You'll make life easier for yourself in the long run if you use named cells,
rather than using cell references. Then your Word references are more
readable and you can rearrange your worksheet without have to change the
Word document.

Also, is there any way to, after I know the Excel document won't have
any more changes, somehow convert the Microsoft Excel object within
Word into regular text? I don't think the person I'm giving the Word
file to at the end of the process is interested in viewing the text in
the Word document as a series of Excel objects... is it somehow
possible to eliminate the Excel object and convert them to "regular"
text at the end??

 

[Jay Freedman]

Not only possible, but very easy.

Insert a LINK field: { LINK Excel.Sheet [Filename]
[SheetName!CellReference] }. If you open the spreadsheet, copy the cell you
want, then switch to Word, you can use Edit > PasteSpecial with the 'Paste
link' option to do this automatically.

Check Help for the field switches.

You need to double the backslashes in the file name.

You'll make life easier for yourself in the long run if you use named cells,
rather than using cell references. Then your Word references are more
readable and you can rearrange your worksheet without have to change the
Word document.

Thanks for your advice (and also Jay's) as this is what I think I
need for my file. But I'm not sure on the exact syntax to make this
work? I went to Insert->Fields->Links and References->Link. Am I
supposed to type everything in to the white box that appears there at
this point? I typed in:

LINK Excel.Sheet "H:\\Book1.xls" [Sheet1!A1]

EXACTLY as written above (with brackets and quotes and everything) and
got an "Error! Not a valid link." error message. How EXACTLY is this
supposed to be written? Can you tyoe that code directly into the Word
document and get it to work? I am using Word and Excel 2000... thanks
again for any help!!

If you had followed the advice we both gave to use the Paste Special
command, you wouldn't have to worry about any of this. But now you
might as well learn more about the field code syntax.

To fix the error, replace the square brackets with double-quotes:

LINK Excel.Sheet "H:\\Book1.xls" "Sheet1!A1"

You probably also want to include a couple of the optional switches at
the end:

LINK Excel.Sheet "H:\\Book1.xls" "Sheet1!A1" \a \h

The \a switch turns on automatic updating, so changes in the
spreadsheet are reflected in the Word document without having to click
on the field and press F9. The \h switch says to use HTML formatting,
which means the table in Word will appear the same as the table in
Excel.

Ask the Word help about "LINK field" to find out more about the
syntax. Note that in the syntax diagrams in the help, square brackets
around an item mean that it's optional, but you don't type the
brackets in the actual field code.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.

 

[Jay Freedman]

Not only possible, but very easy.

Insert a LINK field: { LINK Excel.Sheet [Filename]
[SheetName!CellReference] }. If you open the spreadsheet, copy the cell you
want, then switch to Word, you can use Edit > PasteSpecial with the 'Paste
link' option to do this automatically.

Check Help for the field switches.

You need to double the backslashes in the file name.

You'll make life easier for yourself in the long run if you use named cells,
rather than using cell references. Then your Word references are more
readable and you can rearrange your worksheet without have to change the
Word document.

Also, is there any way to, after I know the Excel document won't have
any more changes, somehow convert the Microsoft Excel object within
Word into regular text? I don't think the person I'm giving the Word
file to at the end of the process is interested in viewing the text in
the Word document as a series of Excel objects... is it somehow
possible to eliminate the Excel object and convert them to "regular"
text at the end??

Yes, you can put the cursor on the Excel object and press
Ctrl+Shift+F9. This is called "unlinking" the field, and it changes
the field result into plain text. This works for any field, not just
LINK fields.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.

 

[wardnine]

Not only possible, but very easy.

Insert a LINK field: { LINK Excel.Sheet [Filename]
[SheetName!CellReference] }. If you open the spreadsheet, copy the cell you
want, then switch to Word, you can use Edit > PasteSpecial with the 'Paste
link' option to do this automatically.

Check Help for the field switches.

You need to double the backslashes in the file name.

You'll make life easier for yourself in the long run if you use named cells,
rather than using cell references. Then your Word references are more
readable and you can rearrange your worksheet without have to change the
Word document.

Also, is there any way to, after I know the Excel document won't have
any more changes, somehow convert the Microsoft Excel object within
Word into regular text? I don't think the person I'm giving the Word
file to at the end of the process is interested in viewing the text in
the Word document as a series of Excel objects... is it somehow
possible to eliminate the Excel object and convert them to "regular"
text at the end??

Yes, you can put the cursor on the Excel object and press
Ctrl+Shift+F9. This is called "unlinking" the field, and it changes
the field result into plain text. This works for any field, not just
LINK fields.

It appears as though this step converts the Excel object into a picture
object? Is it possible to remove all objects completely and just have
plain text? Also, if I have, say, 100 of these Excel objects would it
be possible to convert them all to text at once (i.e., without having
to do Ctrl+Shift+F9 100 times - once for each Excel object)...

 

[Jay Freedman]

Jezebel wrote:
Not only possible, but very easy.

Insert a LINK field: { LINK Excel.Sheet [Filename]
[SheetName!CellReference] }. If you open the spreadsheet, copy the cell you
want, then switch to Word, you can use Edit > PasteSpecial with the 'Paste
link' option to do this automatically.

Check Help for the field switches.

You need to double the backslashes in the file name.

You'll make life easier for yourself in the long run if you use named cells,
rather than using cell references. Then your Word references are more
readable and you can rearrange your worksheet without have to change the
Word document.


Also, is there any way to, after I know the Excel document won't have
any more changes, somehow convert the Microsoft Excel object within
Word into regular text? I don't think the person I'm giving the Word
file to at the end of the process is interested in viewing the text in
the Word document as a series of Excel objects... is it somehow
possible to eliminate the Excel object and convert them to "regular"
text at the end??

Yes, you can put the cursor on the Excel object and press
Ctrl+Shift+F9. This is called "unlinking" the field, and it changes
the field result into plain text. This works for any field, not just
LINK fields.

It appears as though this step converts the Excel object into a picture
object? Is it possible to remove all objects completely and just have
plain text? Also, if I have, say, 100 of these Excel objects would it
be possible to convert them all to text at once (i.e., without having
to do Ctrl+Shift+F9 100 times - once for each Excel object)...

I don't know what's happening in your document, but when I do this, I
get an ordinary Word table full of editable text, not a picture.

To recap, this is what I've done:

- Select an area of an Excel worksheet. The cells all contain numbers,
some typed in directly and others calculated from formulas.

- Copy the cells to the clipboard.

- Click in a Word document.

- Select Edit > Paste Special > Paste link > HTML Format > OK. The
area appears in Word like a table, but it's actually a LINK field, as
you can see by pressing Shift+F9 repeatedly to toggle the field codes.

- With the cursor in the field, press Ctrl+Shift+F9. The result is the
editable table, unlinked from Excel.

If you have a lot of them in one document, and if they're the only
fields (or you don't mind unlinking all fields), press Ctrl+A to
select all the text in the document and then press Ctrl+Shift+F9.

If you want to limit the unlinking to only LINK fields, you need a
macro:

Sub UnlinkLinkFields()
Dim fld As Field
For Each fld In ActiveDocument.Fields
If fld.Type = wdFieldLink Then
fld.Unlink
End If
Next
End Sub

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.

 

[wardnine]

On 17 Nov 2006 15:56:28 -0800, (e-mail address removed) wrote:


Jezebel wrote:
Not only possible, but very easy.

Insert a LINK field: { LINK Excel.Sheet [Filename]
[SheetName!CellReference] }. If you open the spreadsheet, copy the cell you
want, then switch to Word, you can use Edit > PasteSpecial with the 'Paste
link' option to do this automatically.

Check Help for the field switches.

You need to double the backslashes in the file name.

You'll make life easier for yourself in the long run if you use named cells,
rather than using cell references. Then your Word references are more
readable and you can rearrange your worksheet without have to change the
Word document.


Also, is there any way to, after I know the Excel document won't have
any more changes, somehow convert the Microsoft Excel object within
Word into regular text? I don't think the person I'm giving the Word
file to at the end of the process is interested in viewing the text in
the Word document as a series of Excel objects... is it somehow
possible to eliminate the Excel object and convert them to "regular"
text at the end??

Yes, you can put the cursor on the Excel object and press
Ctrl+Shift+F9. This is called "unlinking" the field, and it changes
the field result into plain text. This works for any field, not just
LINK fields.

It appears as though this step converts the Excel object into a picture
object? Is it possible to remove all objects completely and just have
plain text? Also, if I have, say, 100 of these Excel objects would it
be possible to convert them all to text at once (i.e., without having
to do Ctrl+Shift+F9 100 times - once for each Excel object)...

I don't know what's happening in your document, but when I do this, I
get an ordinary Word table full of editable text, not a picture.

To recap, this is what I've done:

- Select an area of an Excel worksheet. The cells all contain numbers,
some typed in directly and others calculated from formulas.

- Copy the cells to the clipboard.

- Click in a Word document.

- Select Edit > Paste Special > Paste link > HTML Format > OK. The
area appears in Word like a table, but it's actually a LINK field, as
you can see by pressing Shift+F9 repeatedly to toggle the field codes.

- With the cursor in the field, press Ctrl+Shift+F9. The result is the
editable table, unlinked from Excel.

If you have a lot of them in one document, and if they're the only
fields (or you don't mind unlinking all fields), press Ctrl+A to
select all the text in the document and then press Ctrl+Shift+F9.

If you want to limit the unlinking to only LINK fields, you need a
macro:

Sub UnlinkLinkFields()
Dim fld As Field
For Each fld In ActiveDocument.Fields
If fld.Type = wdFieldLink Then
fld.Unlink
End If
Next
End Sub

Sorry... when I was pasting the link into Word I was pasting as a
Microsoft Worksheet Excel Object, not in HTML format as you specified,
which seems to be why I was getting the Picture Object when I did
Ctrl+Shift+F9. Everything seems to work now, thanks!

My last question would be how do you remove the gridlines that appear
when you copy multiple cells to Word? I tried doing Tools->Options and
then deselecting Gridlines in Excel and pasting but the gridlines still
appear... is there away to make those lines not show in Word?? Thanks
again for your help!!

 

[Jezebel]

Jay Freedman wrote:
On 17 Nov 2006 15:56:28 -0800, (e-mail address removed) wrote:


Jezebel wrote:
Not only possible, but very easy.

Insert a LINK field: { LINK Excel.Sheet [Filename]
[SheetName!CellReference] }. If you open the spreadsheet, copy the
cell you
want, then switch to Word, you can use Edit > PasteSpecial with the
'Paste
link' option to do this automatically.

Check Help for the field switches.

You need to double the backslashes in the file name.

You'll make life easier for yourself in the long run if you use
named cells,
rather than using cell references. Then your Word references are
more
readable and you can rearrange your worksheet without have to
change the
Word document.


Also, is there any way to, after I know the Excel document won't have
any more changes, somehow convert the Microsoft Excel object within
Word into regular text? I don't think the person I'm giving the Word
file to at the end of the process is interested in viewing the text
in
the Word document as a series of Excel objects... is it somehow
possible to eliminate the Excel object and convert them to "regular"
text at the end??

Yes, you can put the cursor on the Excel object and press
Ctrl+Shift+F9. This is called "unlinking" the field, and it changes
the field result into plain text. This works for any field, not just
LINK fields.


It appears as though this step converts the Excel object into a picture
object? Is it possible to remove all objects completely and just have
plain text? Also, if I have, say, 100 of these Excel objects would it
be possible to convert them all to text at once (i.e., without having
to do Ctrl+Shift+F9 100 times - once for each Excel object)...

I don't know what's happening in your document, but when I do this, I
get an ordinary Word table full of editable text, not a picture.

To recap, this is what I've done:

- Select an area of an Excel worksheet. The cells all contain numbers,
some typed in directly and others calculated from formulas.

- Copy the cells to the clipboard.

- Click in a Word document.

- Select Edit > Paste Special > Paste link > HTML Format > OK. The
area appears in Word like a table, but it's actually a LINK field, as
you can see by pressing Shift+F9 repeatedly to toggle the field codes.

- With the cursor in the field, press Ctrl+Shift+F9. The result is the
editable table, unlinked from Excel.

If you have a lot of them in one document, and if they're the only
fields (or you don't mind unlinking all fields), press Ctrl+A to
select all the text in the document and then press Ctrl+Shift+F9.

If you want to limit the unlinking to only LINK fields, you need a
macro:

Sub UnlinkLinkFields()
Dim fld As Field
For Each fld In ActiveDocument.Fields
If fld.Type = wdFieldLink Then
fld.Unlink
End If
Next
End Sub

Sorry... when I was pasting the link into Word I was pasting as a
Microsoft Worksheet Excel Object, not in HTML format as you specified,
which seems to be why I was getting the Picture Object when I did
Ctrl+Shift+F9. Everything seems to work now, thanks!

My last question would be how do you remove the gridlines that appear
when you copy multiple cells to Word? I tried doing Tools->Options and
then deselecting Gridlines in Excel and pasting but the gridlines still
appear... is there away to make those lines not show in Word?? Thanks
again for your help!!

Switch off the gridlines in Excel, before you do the copy.

 

[wardnine]

On 18 Nov 2006 09:59:31 -0800, (e-mail address removed) wrote:


Jay Freedman wrote:
On 17 Nov 2006 15:56:28 -0800, (e-mail address removed) wrote:


Jezebel wrote:
Not only possible, but very easy.

Insert a LINK field: { LINK Excel.Sheet [Filename]
[SheetName!CellReference] }. If you open the spreadsheet, copy the
cell you
want, then switch to Word, you can use Edit > PasteSpecial with the
'Paste
link' option to do this automatically.

Check Help for the field switches.

You need to double the backslashes in the file name.

You'll make life easier for yourself in the long run if you use
named cells,
rather than using cell references. Then your Word references are
more
readable and you can rearrange your worksheet without have to
change the
Word document.


Also, is there any way to, after I know the Excel document won't have
any more changes, somehow convert the Microsoft Excel object within
Word into regular text? I don't think the person I'm giving the Word
file to at the end of the process is interested in viewing the text
in
the Word document as a series of Excel objects... is it somehow
possible to eliminate the Excel object and convert them to "regular"
text at the end??

Yes, you can put the cursor on the Excel object and press
Ctrl+Shift+F9. This is called "unlinking" the field, and it changes
the field result into plain text. This works for any field, not just
LINK fields.


It appears as though this step converts the Excel object into a picture
object? Is it possible to remove all objects completely and just have
plain text? Also, if I have, say, 100 of these Excel objects would it
be possible to convert them all to text at once (i.e., without having
to do Ctrl+Shift+F9 100 times - once for each Excel object)...

I don't know what's happening in your document, but when I do this, I
get an ordinary Word table full of editable text, not a picture.

To recap, this is what I've done:

- Select an area of an Excel worksheet. The cells all contain numbers,
some typed in directly and others calculated from formulas.

- Copy the cells to the clipboard.

- Click in a Word document.

- Select Edit > Paste Special > Paste link > HTML Format > OK. The
area appears in Word like a table, but it's actually a LINK field, as
you can see by pressing Shift+F9 repeatedly to toggle the field codes.

- With the cursor in the field, press Ctrl+Shift+F9. The result is the
editable table, unlinked from Excel.

If you have a lot of them in one document, and if they're the only
fields (or you don't mind unlinking all fields), press Ctrl+A to
select all the text in the document and then press Ctrl+Shift+F9.

If you want to limit the unlinking to only LINK fields, you need a
macro:

Sub UnlinkLinkFields()
Dim fld As Field
For Each fld In ActiveDocument.Fields
If fld.Type = wdFieldLink Then
fld.Unlink
End If
Next
End Sub

Sorry... when I was pasting the link into Word I was pasting as a
Microsoft Worksheet Excel Object, not in HTML format as you specified,
which seems to be why I was getting the Picture Object when I did
Ctrl+Shift+F9. Everything seems to work now, thanks!

My last question would be how do you remove the gridlines that appear
when you copy multiple cells to Word? I tried doing Tools->Options and
then deselecting Gridlines in Excel and pasting but the gridlines still
appear... is there away to make those lines not show in Word?? Thanks
again for your help!!

Switch off the gridlines in Excel, before you do the copy.

I did that... the gridlines still show.