Microsoft Word/Word tables
QUESTION: Hi, I have a client who set up their quotations in Word 2003 tables. Their quotes are usually a two column table which extends over 2 or 3 pages - so a description in column A, with prices in column B, and then a subtotal, tax value, and total and the bottom. The numbers are all entered as a calculation (eg: =540300)so that they could get the format of two decimals. It is a hugely time wasting procedure of editing each formula to enter each number; then ... to calculate the tax formulae they count up how many rows they have used so that they can type the formula for the tax value (eg: =B23*.14) and finally the total (eg: B22 +b23). This is the way they've done it for years but now they have some computers with Office 2010/13 and suddenly things don't work anymore. When they retrieve an old quote to update, the values get all scrambled. I have discovered that the main problem is the number format they chose for the numbers which they're putting in calculations viz # ##0.00.
The new quotes they do in more recent versions are not the problem - it is the old ones that get scrambled and they seem to need to revive many of their old quotes - hence the problem. I'm now trying to find an easy way for them to use these old quotes without retyping everything. Does anyone have any ideas?
Could anyone confirm that the Insert Table, Excel Spreadsheet option doesn't appear to extend over more than one page?
ANSWER: An embedded sheet (i.e an object) would only appear on one page - because it is one object - but it MAY be that the values in the sheet are ok - if so, copying to excel, then pasting back again MAY be a workaround. The easiest fix I can think of, though it's cheating slightly, is to find a machine with office 2003 on and write a macro to open each file in turn, copy the entire contents and paste special, values, then re-save - this would remove the field codes and therefore remove the issue when opening in later versions - though it WOULD remove all formulas. I'd be INCLINED to use a custom form in VBA to do the calculation work.
Not sure if this provides the answers you need - happy to help further!
---------- FOLLOW-UP ----------
QUESTION: Thank you for your advice. Please could you give me more details of the custom form you mentioned for the calculation work. In the meantime, I will trythe macro as a fix and then they'd just have to use a calculator. Seems crazy they don't want to change to Excel!
A custom form is the method I use for all my document completion tasks - it uses VBA to display on open (or new if a template) a box containing all the questions they might have to answer - in this case a duplication of the table in the document - but without the calculated fields - it would then do the necessary calculations and populate the document. A basic example of a template with macro and user form is at http://www.aidanheritage.byethost3.com/word/aidan%20blank%20letter.dot
- in your case, it would need to reference the cells of a table rather than bookmarks, but the principle holds good - happy to help further if needed (I can create an example form for you if you need it - if it helps, my direct email is email@example.com )
About Microsoft Word
This topic answers questions related to Microsoft Word stand-alone or Microsoft Office Word including Word 2003, Word 2007, Office 2000, and Office XP. You can get Word help on formatting text, tables, tabs, fonts, styles, general Word layouts, bullets, headings, and outlines, using templates, toolbar modifications, and using Track Changes. You may also find tips on linking Word and Excel embedded objects including charts. This site does not provide a general Word tutorial nor the basics of using a word processor. It provides specific answers to using Microsoft Word only. If you do not see your Word question answered in this area then please ask a Word question here