Linking Text from Excel to Word

[heyz]

Hi

I have an excel sheet that has some database and which will keep getting
updated. I have another Word file that is based on the database of the excel
sheet. The purpose of using word is that I will have content written for each
of the database element from excel. I am wondering if i could link excel and
word in such a way that Any update in excel will automatically make the
update in word. I tried Inserting Excel as an object but that inserts the
sheet as a whole. What i am looking to achieve is inserting cells in
different places of the word document.

Let me illustrate my situation

Say my excel sheet is like
=========
LIST ITEMS
=========
List Item 1
List Item 2
List Item 3

And my word Document is

=========
List Item 1
=========
Blah Blah Blah Blah Blah Blah Blah Blah Blah Blah Blah Blah Blah Blah Blah
Blah Blah Blah Blah Blah Blah Blah Blah Blah Blah Blah Blah Blah Blah Blah
Blah Blah Blah Blah Blah Blah Blah Blah Blah Blah Blah Blah Blah Blah Blah
Blah Blah Blah Blah Blah Blah Blah Blah Blah

=========
List Item 2
=========
Blah Blah Blah Blah Blah Blah Blah Blah Blah Blah Blah Blah Blah Blah Blah
Blah Blah Blah Blah Blah Blah Blah Blah Blah Blah Blah Blah Blah Blah Blah
Blah Blah Blah Blah Blah Blah Blah Blah Blah Blah Blah Blah Blah Blah Blah
Blah Blah Blah Blah Blah Blah Blah Blah Blah

=========
List Item 3
=========
Blah Blah Blah Blah Blah Blah Blah Blah Blah Blah Blah Blah Blah Blah Blah
Blah Blah Blah Blah Blah Blah Blah Blah Blah Blah Blah Blah Blah Blah Blah
Blah Blah Blah Blah Blah Blah Blah Blah Blah Blah Blah Blah Blah Blah Blah
Blah Blah Blah Blah Blah Blah Blah Blah Blah

Obviously, am gonna have lots of these List Items which is why I am trying
to automate it.

Is this possible? Or is it wishful thinking.
Thanks for any suggestion.

 

[Jezebel]

Select and copy the cell in Excel. Switch to Word and use Edit >
PasteSpecial, with the 'Paste Link' option checked.

 

[heyz]

Hey Jezebel

Thanks so much for that! It simplifies things a lot.
However, I have managed to find myself in one more problem: I tested this
method and found that if I add rows or columns in such a way to change the
position of the original cell then the linked field in Word does not
automatically catch that change (but linked cells in other sheets within
excel seem to catch the position change and properly reflect it) Is there a
way to get around that?

And thanks again.

heyz

 

[Jezebel]

Press Alt-F9 to display field codes. You'll see the the link looks something
like this --

{ LINK Excel.Sheet.8 "d:\\username\\Temp\\Book1.xls" "Sheet1!R3C3" \a \t }

If you give the Excel cell a name, you can insert that name in place of the
"Sheet1!R3C3" part. Then the link will reference the cell with that name,
wherever it is.

 

[heyz]

Hi jezebel

Thanks again. Yes that works. I have discovered today with ur help.
Unfortunately I assess that to achieve what i had in mind I'm going to have
to work on automating too many things. And tat might be more work than doing
what I intend to do without all the automation.
For example if i inserted a new list my word file wouldn't correspond to
that change. The content under one list item would show up under a previous
list item. In short more work developing all this.
Thanks a lot though. I am sure i'l be using what i learnt someway or the other

heyz

 

[Ed]

What if you drive all of this from Excel? I'm assuming that one of daunting
parts is automating all the "Blah Blah" stuff? I can name an entire range
in Excel and paste a link into a Word doc - any changes to the range,
including inserting and deleting rows are reflected in the link in Word. If
you run all of this as
List Item 1
Blah Blah
List Item 2
Blah Blah
etc. in Excel, you can link that column into Word and any adjustments are
reflected in the doc.

You could also do something like putting your List Item in Column A (in
Excel) and your Blah Blah in Column B, then put a check mark in Column C for
every one you want to include, then run a macro in Excel that would generate
a Word doc based on whatever template, letterhead, etc you desire with only
the checked items. Although that sounds a lot like trying to imitate an
Access report.

Ed

 

[heyz]

Hi Ed.

Firstly thanks for chiping in.
Your suggestiong would work but then I want to introduce the text, the blah
blah in word. This is not just a little bit of blah blah but different kinds
of blah blahs n lots of it, and Word will be best suited for all that word
processing. So i want to source headings (List Items) from Excel (whcih could
change, which might get added) from excel but work on the content in word.

- I tried pasting the Excel range into Word and then inserted text between
one List item and another but if i update the field the text I insert is
lost.
- I have tried pasting cells one by one as separate fields, and inserting
text inbetween. This is pointless as it does not automate anything for me.

Actually am trying to manage content for a new website that is being
developed, whcih will have lots of pages, and hierarchy of pages, and new
pages might get added during development.

So say the list items refer to pages and the pages are

About us
Product AA
Product AB
Product AC
Locations
(at this point we have some 84 pages, and we just started)

And i paste each of these List Items from Excel as fields into word and
inserted all the content between these fields it does not help in a case where
A new page PRODUCT XY gets added between Product AA and ProductAB. The
content written under Product AB would now show up under PRODUCT XY!

I thought about all this and I wondered if I would end up working more in
developing a system than actually doing it without the automation.

If there is actually an answer to this and am just ignorant then that is so
welcome. I am just thrilled by what all this integration of applications can
do.

Thanks guys.

heyz

 

[PureEvil]

Thank you for putting the Alt-F9 in this post. Out of the blue my links
started displaying field codes. Never having seen this before I was stuck.
Even though this post doesn't relate to my problem it helped me.

Thanks!
Pamala

 

[Kesbutler]

Your idea below about running a macro to generate a word doc might actually
be what I am looking for. How would I do this. I need to generate a word doc
based of off which headings I choose.

 

[Doug Robbins - Word MVP]

I am thinking that this thread was started several years ago. It might be
best if you started a new thread and explained in it exactly what it is that
you want to do.

Where are the headings that you might choose? With what is each individual
heading associated?

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP