Using embedded spreadsheets

[cycle_simon]

Hi, first time here. Not sure if this is a real Word tables question or not.

I am setting up a questionnaire in in Word document. A number of the
questionnaires request input of data (rows and coulmns of data). I would like
to use the data enetered in one table to populate certain cells of a
subsequent table.

I thought I could use an embedded spreadsheet and use the different tabs
(worksheets) of the spreadsheet to capture the data and have formulas between
the worksheets to automatically populate the cells.

My problem is that I cannot find a way to link or reference one embedded
spreadsheet to another.

Any suggestions would be greatly appreciated.

 

[Henk57]

Hi Simon:
Wldnt it easier to process everyhting in Excel and just link the output
tables to the Word file? You can dynamically link the Excel tables in
Word, so any change will be reflected in Word automatically.
HTH - Henk

 

[cycle_simon]

Hi Henk57:

Thanks for the reply. The challenge is that the questionnaire is
distributed to respondents and so it is much more convenient to have it
contained in a single file. Trying to do it this way is also imposed by a
constraint to have the hardcopy look identical to the "electronic" copy that
respondents willl complete.

What I have come to learn is that when the link to the embedded spreadsheet
object is first created, the field (displayed with Alt F9) has the corrrect
path & filename for the Word doc. For example:

{Link Excel.Sheet.8 "c:\\test questionnaire.doc"
"_1242205181!Sheet1!R1C1:R3C3" \a \p }

The first time the link is selected and you right click to open the link,
the speadsheet opens but upon returning to the Word document, the field
changes from the actual file name to:

{Link Excel.Sheet.8 "Worksheet in test questionnaire.doc" Sheet1!R1C1:R3C3"
\a \p }

When you then try to open this linked object again, Word says the the
filename is invalid or the link is corrupt.

I have tried to replace the file name with the "filename \p" field, but Word
overwrites this as well.

Any suggestions???

 

[Peter Jamieson]

I wouldn't try to link two embedded spreadsheets in the same Word document
for the reasons you mention, among others. I don't think there is any way
around the problems you have encountered.

Possibilities:
a. do the whole thing as an Excel application (including any necessary
layout). I don't know how feasible that is, but it sounds as if your users
must have Excel.
b. use embedded spreadsheets, but write Word VBA code to automate them as a
mechanism for transferring values from one to the other. I've never done
that and wouldn't particularly want to, but suspect it is feasible. But your
users will definitely need Excel on their systems to do that, and in most
cases you will probably run into the problem where they are not allowed to
run macros.
c. try to use the much more basic calculation and referencing facilities
available within Word tables. If you have static text and date data (i.e.
data the user should not modify) you can use bookmarks or SET fields to set
it, and REF fields to reference it. You can do that for variable data as
well, but you run the risk that users will delete the bookmarks on which
everything relies. For numeric data, things are slightly easier because
Word's (clumsy) { = } formula field lets you reference cells in the same or
different tables. Sort of.
d. Do (c), but with VBA. Same problem with sending to users who can't run
VBA.
e. use something else altogether.

Sorry that's probably not very helpful but it may save you a bit of time
trying to force Word to do something it can't really do.

Peter Jamieson