[Ripperdan]

Running Windows XP and Office XP.

I am working on a project for an Electrical Utility Company where they want a 3 ring binder book generated with several sections with a maim Table of Contents and Sub Table of Contents for each section. Most of the book will be Word documents but some of the sections currently have some Excel spreadsheets with charts that plot curves depending on the data entered. Is there a way to eliminate the Excel spreadsheets or imbed the Excel spreadsheets into Word so that entering data in a Word table will do automatic calculations and generate a chart (which shows different curves, depending on the data entered). In addition, we would like the Word cells to change color depending on the calculations, like “Conditional Formatting” in Excel.

I am pretty good at writing Word Macros and some Visual Basic but not to good at Excel Macros.

[etaf]

you should be able to bed the spreadsheet into the word document and also any updates to the spreadsheet will be reflected into the word document

I had a 60 Page word document in 2001 that had a large number of spreadsheets and graphs embedded and then each month everything updated and The word document was sent to exec team,,,

it was quite straight forward in word, I'll try and see if i can remember, how we embedded the object

EDIT

Yep just did it
2003 version
Insert
Object
Create from file
Browse for the file
also click on Link to file

that will bring in the spreadsheet

now if you want to update to latest data
right click on the object in word
update

and it will update

[Ripperdan]

I have seen that method you suggested on a couple of other forums but it isn't exactly what I want to do. After re-reading my post, I see I didn't explain it properly.

I don't want the people using the Word document to ever have to open an Excel spreadsheet. I want them to enter the data in a Word Table and then have a chart in Word automatically updated based on that data, just like it does in Excel.

If I simply paste the Excel data cells and the chart into Word, I can change the values in the Word cells but the chart is no longer linked to those cells, so nothing changes in the Word chart.

I don’t know if it will work but I am going to try to make an Excel sheet with the same file name as the Word document (except with the “xls” at the end) with the required cells and chart. I’ll make this Excel file HIDDEN so the people doing the work won’t know it’s there. I’ll link my cells in Word to theHIDDEN Excel sheet (Like you suggested), which will automatically update the Chart in Excel. I’ll have that chart in Excel linked back to the chart in Word, which, I hope will automatically update.

I haven’t had time to try it yet. If you are interested, I’ll let you know if it works.

[The Villan]

Feedback is very important.

[etaf]

yes would be very interested , you may need some kind of trigger to update the graph after the data is entered

[Zack Barresse]

I recommend you just use an Excel file. That is the purpose of them and you're trying to fit a square peg in a round hole. I'd just live with the way it is. I realize this may sound harsh, but I think it's more making the most of how the applications work. Hope you understand.

[bengalsjim]

You would have your Excel workbook with its various tabs and named areas, and you would create your Word Document, except instead of making tables, you would embed the area from the Excel spreadsheet. It would look just like the Word table.

If someone has the rights to edit the embedded Excel object, it updates in the word doc as well as the the original Excel sheet.

If there's a summary page in Word that feeds from the Excel sheet, then whatever changes have been made would be reflected there.

Or, you could just do it all in Excel. You can make a sheet that would look just like the Word doc that would be fed data from other parts of the Excel workbook..