Solved

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

Posted on 2002-07-22
14
292 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
ID: 7169948
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
ID: 7170444
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
ID: 7171223
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
ID: 7171935
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
ID: 7171941
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
ID: 7171995
No problem at all, Spence.  2 heads are better than one.
":0) Asta
0
 

Author Comment

by:Tricky
ID: 7172026
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
Do email signature updates give you a headache?

Are you constantly making changes to email signatures? Are the images not formatting how you want them to? Want high-quality HTML signatures on all devices, including on mobiles and Macs? Then, let Exclaimer solve all your email signature problems today.

 
LVL 27

Expert Comment

by:Asta Cu
ID: 7172159
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
ID: 7172165
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
ID: 7172166
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
ID: 7172170
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
ID: 7172201
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
ID: 7200623
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
ID: 7200642
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Storage devices are generally used to save the data or sometime transfer the data from one computer system to another system. However, sometimes user accidentally erased their important data from the Storage devices. Users have to know how data reco…
Possible fixes for Windows 7 and Windows Server 2008 updating problem. Solutions mentioned are from Microsoft themselves. I started a case with them from our Microsoft Silver Partner option to open a case and get direct support from Microsoft. If s…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In this video, we discuss why the need for additional vertical screen space has become more important in recent years, namely, due to the transition in the marketplace of 4x3 computer screens to 16x9 and 16x10 screens (so-called widescreen format). …

911 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

19 Experts available now in Live!

Get 1:1 Help Now