Solved

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

Posted on 2002-07-22
14
297 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Don't Cry: How Liquid Web is Ensuring Security

WannaCry is just the start. Read how Liquid Web is protecting itself and its customers against new threats.

 
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
 
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

Easy, flexible multimedia distribution & control

Coming soon!  Ideal for large-scale A/V applications, ATEN's VM3200 Modular Matrix Switch is an all-in-one solution that simplifies video wall integration. Easily customize display layouts to see what you want, how you want it in 4k.

Question has a verified solution.

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

No single Antivirus application (despite claims by manufacturers) will catch or protect you from all Virus / Malware or Spyware threats. That doesn't stop you from further protecting yourself however - and this article is to show you how.
The Windows functions GetTickCount and timeGetTime retrieve the number of milliseconds since the system was started. However, the value is stored in a DWORD, which means that it wraps around to zero every 49.7 days. This article shows how to solve t…
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…
The viewer will learn how to successfully create a multiboot device using the SARDU utility on Windows 7. Start the SARDU utility: Change the image directory to wherever you store your ISOs, this will prevent you from having 2 copies of an ISO wit…

695 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