Solved

Linking a Word doc to an Excel spreadsheet - sounds easy huh!?

Posted on 2002-07-22
14
291 Views
Last Modified: 2013-12-28
Hi,

Thanks for reading...I'm trawling through the VB programmers guide and language reference trying to work out how I like part of a Word doc to a cell in an Excel spreadsheet;

ie I have an Excel sheet, with about 10 or so fields, say from C5 to C14, and I want each of these fields to appear in a Word Document I have. Let's say that I export various customer info from a database, convert this into Excel format, then want this to re-appear on a fax I have in Word - get the picture? :)

I'm aware of the OLE potential, and am reading the manual trying to fathom it out...but maybe you have experience in this and can assist?

If you need to know specific info, please ask...

Thanks...

Tricky
0
Comment
Question by:Tricky
  • 6
  • 4
  • 3
  • +1
14 Comments
 
LVL 27

Expert Comment

by:Asta Cu
Comment Utility
This is helpful and explicit in "how to" and related to versions 2000 (Office)
http://office.microsoft.com/assistance/2000/LongWordDocandExcel.aspx
0
 
LVL 27

Expert Comment

by:Asta Cu
Comment Utility
What I posted is specific and gives you all that you need; if you happen to be using a different version; or more specifics are needed, let us know.  Since that is complete in all regards, saw no need to expound further.

Asta
0
 

Author Comment

by:Tricky
Comment Utility
Sorry - I should have specified version numbers etc...

Using MS Word 97 SR 2 and MS Excel 97 SR 2...

Thanks!
0
 
LVL 44

Expert Comment

by:CrazyOne
Comment Utility
This is from old link that doesn't seem to exist anymore. I believe it may even come from the Office help file

"Create a linked object from an existing Microsoft Excel worksheet or chart

1     Open both the Word document and the Microsoft Excel workbook that contains the data you want to create a linked object from.
2     Switch to Microsoft Excel, and then select the entire worksheet, a range of cells, or the chart you want.
3     Click Copy  .
4     Switch to the Word document, and then click where you want to insert the linked object.
5     On the Edit menu, click Paste Special.

6     Click Paste link.
7     In the As box, click the option you want.

If you're not sure which one to choose, click an option and read the description in the Result box.

8     To display the linked worksheet or chart as an icon ¾ for example, if others will view the document online ¾ select the Display as icon check box.
9     To prevent the linked worksheet or chart from being displayed as a drawing object that you can position in front of or behind text and other objects, clear the Float over text check box.

Tip   If you are linking a range of cells and might later want to include additional rows or columns of data, first name the range in Microsoft Excel. Then copy the range and paste the link into Word. If you add more data to the range in Microsoft Excel, you can redefine the range name to include the additional cells. Word automatically adds the new data the next time you update the link."


The Crazy One
0
 
LVL 44

Expert Comment

by:CrazyOne
Comment Utility
Oops looking at the link asta provided I see that proceedure I posted is the same as it is in that link. Sorry asta. :>)
0
 
LVL 27

Expert Comment

by:Asta Cu
Comment Utility
No problem at all, Spence.  2 heads are better than one.
":0) Asta
0
 

Author Comment

by:Tricky
Comment Utility
Hi,

Thanks for both keeping an eye on this one, points will reflect your assistance :)

I need to link one cell in excel to a particular position on a word doc, it's not like a graph or a range of cells unfortunately - that would maybe be a bit simpler!! :/

It's a database export, that I'll tidy up and reformat, to a sheet in excel, the idea being that after doing this, I open the word doc, and it 'auto populates' with fresh info...am I explaining it clearly?

Thanks again... :)

0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 27

Expert Comment

by:Asta Cu
Comment Utility
Hi, I'm at work, little time ... but Office 97 had subsequent patchs and fixes since the SR-1 fix.  More about that here:
http://office.microsoft.com/downloads/9798/sr2off97detail.aspx
http://office.microsoft.com/assistance/9798/sr2faq.aspx

the following gives you the specific issues "fixed" with this release/update.  It would be a shame to see you go through a process only to find that these patches are needed.

http://support.microsoft.com/default.aspx?scid=KB;EN-US;q151020&
0
 
LVL 44

Expert Comment

by:CrazyOne
Comment Utility
The procedure outlined will do that. Just make sure in step 7 that you choose "Microsoft Excel Worksheet Object" listed in the "As:" box. And make sure you seleceted the "Paste Link" option as well.

I just tested this. I opened Excel put some numbers in a cell followed the procdure outlined aboved and pasted it into Word. I went back to Excel and change the numbers in the cell and the change was immediately updated in Word. :>)
0
 
LVL 27

Expert Comment

by:Asta Cu
Comment Utility
A friend of mine participates/runs part of the site below and has some excellent, easy to use and understand "How to" and "tools" for Excel and Word, and more.
http://www.thewordexpert.com/
0
 
LVL 27

Expert Comment

by:Asta Cu
Comment Utility
One excerpt only from the above link; then must get back to work:
Do not use Excel for documents that contain a lot of text. Use Word tables instead. They work very similar to Excel columns, but allow the freedom of entering and viewing a lot more text. If you are convinced that you need Excel because you need to total a column, think again. Calculations can also be done with Word, especially simple ones. Decide what is the priority for the document, having it look nice and text that is easy to manipulate versus extensive calculations.
http://www.thewordexpert.com/excel.htm
0
 

Author Comment

by:Tricky
Comment Utility
Thanks for all your input here Asta, I too am at work, so will have to read through your comments and the various links (thank you) that you have supplied.

I will be back tomorrow, will talk more then...

Thanks again :)
0
 
LVL 1

Accepted Solution

by:
filpost earned 400 total points
Comment Utility
Follow these steps in your Word file,
1. Insert -> Field
2. Set Categories -> 'Links and References'
3. Set Field names -> 'Link'
4. In the text box Field codes write  
LINK WorkSheet "c:\yourpath\filename.xls" Sheet1!R1C1 /a

The above command retrieves the information in the cell A1 of your Excel file. If you want to retrieve another cell you have to set the R(Row) and C(Column) option.Ex: if you want to retrieve the cell B5 you have to write Sheet1!R2C5

The option /a is for auto-update.

Good Luck
0
 

Author Comment

by:Tricky
Comment Utility
Thanks...this is the conclusion I have also come to, but slightly differently.

The code you supplied has been auto-created by using the links option, but what you say is exactly right.

0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Windows Mobile Barcode Scanning These days almost every product has a barcode in some way... amongst there are 1D barcodes en 2D barcodes.. From http://www.barcodeman.com/faq/2d.php I found some handy definitions and insights. 1D barcodes …
If you get continual lockouts after changing your Active Directory password, there are several possible reasons.  Two of the most common are using other devices to access your email and stored passwords in the credential manager of windows.
This video Micro Tutorial explains how to clone a hard drive using a commercial software product for Windows systems called Casper from Future Systems Solutions (FSS). Cloning makes an exact, complete copy of one hard disk drive (HDD) onto another d…
The Task Scheduler is a powerful tool that is built into Windows. It allows you to schedule tasks (actions) on a recurring basis, such as hourly, daily, weekly, monthly, at log on, at startup, on idle, etc. This video Micro Tutorial is a brief intro…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now