Index Match in Word Table, or Embedded Excel?

Hello there,

Is it possible to use Index/Match in a Word Table, or otherwise return
the formula result from an embedded Excel table to a word document
bookmark/formfield?

I have had a hard time figuring out how to take Word document bookmark
values, get them into some form of embedded Excel workbook, have
calculations run on them, then return the results to other bookmark
fields in the Word document.

I also tried embedding the Excel data in a userform (using the Excel
10.0 object) and retrurning it that way, but was simply baffled by the
vba syntax; for instance, I tried this:

excelsheet1.Spreadsheet1.Sheets("Sheet1").Range("A 25").Text = _
ActiveDocument.FormFields("EEIndex").Range.Text
msgbox excelsheet1.Spreadsheet1.Sheets("Sheet1").Range("A 25").Text

The msgbox pops up with the correct value, assuring me I coded the
first part correctly, but the bookmark ("EEIndex") does not populate
with the value.

Any direction or assistance would be greatly appreciated....!

Chris

I use a slightly easier way to do this. I paste links using the Edit/Paste
Special function. You can paste a link from your MS Word document into a
cell in MS Excel, then Paste your result back in a different location in the
MS Word document.

1. Save both documents (in your case, the MS Word document and the Excel
Document).
2. Select the data you want to manipulate from your source document, then
copy it (I used CTRL+C, but there are several other ways).
3. Go to your location in the destination document, and put your cursor
there.
4. Go to Edit, Paste Special. In the window, the default is Paste. Change
that to Paste Link. Then select a "Paste As" option. For what you are
describing, Text or Unformatted Text would probably work best.
5. After you have put all your links into your Excel Spreadsheet and
performed your calculations, do the reverse process to link the results back
to your Word document.
6. Save both documents again.

Be careful about "circular references," which mean you link something one
way, and the return link goes back to the original location. This usually
just happens in spreadsheets, but can potentially happen with this process.
Computers do not like this! Just make sure when you paste the return link,
you do it in a different location in the document than your source.

The best thing about this process is that MS Office does all of the coding
for you. Another tip - this is not limited to Word-Excel. You can use the
Paste Special function with most MS Office products, and even use it within
the same document.

One drawback. Every time you open one of these linked documents, MS will
ask you whether you want to update the links. I have not found a way to get
around the error message, but the amount of work that one click saves seems
worth it to me.

"TomorrowsMan" wrote:

Hello there,

Is it possible to use Index/Match in a Word Table, or otherwise return
the formula result from an embedded Excel table to a word document
bookmark/formfield?

I have had a hard time figuring out how to take Word document bookmark
values, get them into some form of embedded Excel workbook, have
calculations run on them, then return the results to other bookmark
fields in the Word document.

I also tried embedding the Excel data in a userform (using the Excel
10.0 object) and retrurning it that way, but was simply baffled by the
vba syntax; for instance, I tried this:

excelsheet1.Spreadsheet1.Sheets("Sheet1").Range("A 25").Text = _
ActiveDocument.FormFields("EEIndex").Range.Text
msgbox excelsheet1.Spreadsheet1.Sheets("Sheet1").Range("A 25").Text

The msgbox pops up with the correct value, assuring me I coded the
first part correctly, but the bookmark ("EEIndex") does not populate
with the value.

Any direction or assistance would be greatly appreciated....!

Chris

Dallas,

It sounds like a great idea; however, I gave it a shot and just got a
#VALUE! error in Excel. Two questions about this:

1. The value in the Word doc is in a bookmark; should this be
formatted a certain way? Or, do bookmarks not work with the Paste Link
option?

2. The Excel spreadsheet is embedded in the Word doc; however, I
suspect #1 is the issue.

I'll keep poking around with it....thanks!

Chris



Dallas64 wrote:
I use a slightly easier way to do this. I paste links using the Edit/Paste
Special function. You can paste a link from your MS Word document into a
cell in MS Excel, then Paste your result back in a different location in the
MS Word document.

1. Save both documents (in your case, the MS Word document and the Excel
Document).
2. Select the data you want to manipulate from your source document, then
copy it (I used CTRL+C, but there are several other ways).
3. Go to your location in the destination document, and put your cursor
there.
4. Go to Edit, Paste Special. In the window, the default is Paste. Change
that to Paste Link. Then select a "Paste As" option. For what you are
describing, Text or Unformatted Text would probably work best.
5. After you have put all your links into your Excel Spreadsheet and
performed your calculations, do the reverse process to link the results back
to your Word document.
6. Save both documents again.

Be careful about "circular references," which mean you link something one
way, and the return link goes back to the original location. This usually
just happens in spreadsheets, but can potentially happen with this process.
Computers do not like this! Just make sure when you paste the return link,
you do it in a different location in the document than your source.

The best thing about this process is that MS Office does all of the coding
for you. Another tip - this is not limited to Word-Excel. You can use the
Paste Special function with most MS Office products, and even use it within
the same document.

One drawback. Every time you open one of these linked documents, MS will
ask you whether you want to update the links. I have not found a way to get
around the error message, but the amount of work that one click saves seems
worth it to me.

"TomorrowsMan" wrote:

Hello there,

Is it possible to use Index/Match in a Word Table, or otherwise return
the formula result from an embedded Excel table to a word document
bookmark/formfield?

I have had a hard time figuring out how to take Word document bookmark
values, get them into some form of embedded Excel workbook, have
calculations run on them, then return the results to other bookmark
fields in the Word document.

I also tried embedding the Excel data in a userform (using the Excel
10.0 object) and retrurning it that way, but was simply baffled by the
vba syntax; for instance, I tried this:

excelsheet1.Spreadsheet1.Sheets("Sheet1").Range("A 25").Text = _
ActiveDocument.FormFields("EEIndex").Range.Text
msgbox excelsheet1.Spreadsheet1.Sheets("Sheet1").Range("A 25").Text

The msgbox pops up with the correct value, assuring me I coded the
first part correctly, but the bookmark ("EEIndex") does not populate
with the value.

Any direction or assistance would be greatly appreciated....!

Chris

Bookmarks should not be a problem. In fact, when you use the paste link
function, Microsoft will automatically add a bookmark to the source document
with a name like OLE_LINK4. If your data is already bookmarked, it will
paste that bookmark name as part of the link. For example, when I created a
test for this, I had a 7, and bookmarked it as "seven" in a Word document
named "Document." In Excel, the link is labeled:
Word.Document.8|'\\Document.doc'!'!Seven'

Formatting also should not matter, especially if you choose "Text" or
"Unformatted" text when you select a Paste As.

You might want to try this with a non-embedded Excel document. You can
paste not only a single cell back as a link, you can link multiple cells.
This is probably another way to do what you were doing with the embedded
document anyway.

I was going to try what you did with an embedded Excel file, but suddenly
discovered that was disabled on my Word. I will have to check into that when
I have more time.

"TomorrowsMan" wrote:

Dallas,

It sounds like a great idea; however, I gave it a shot and just got a
#VALUE! error in Excel. Two questions about this:

1. The value in the Word doc is in a bookmark; should this be
formatted a certain way? Or, do bookmarks not work with the Paste Link
option?

2. The Excel spreadsheet is embedded in the Word doc; however, I
suspect #1 is the issue.

I'll keep poking around with it....thanks!

Chris



Dallas64 wrote:
I use a slightly easier way to do this. I paste links using the Edit/Paste
Special function. You can paste a link from your MS Word document into a
cell in MS Excel, then Paste your result back in a different location in the
MS Word document.

1. Save both documents (in your case, the MS Word document and the Excel
Document).
2. Select the data you want to manipulate from your source document, then
copy it (I used CTRL+C, but there are several other ways).
3. Go to your location in the destination document, and put your cursor
there.
4. Go to Edit, Paste Special. In the window, the default is Paste. Change
that to Paste Link. Then select a "Paste As" option. For what you are
describing, Text or Unformatted Text would probably work best.
5. After you have put all your links into your Excel Spreadsheet and
performed your calculations, do the reverse process to link the results back
to your Word document.
6. Save both documents again.

Be careful about "circular references," which mean you link something one
way, and the return link goes back to the original location. This usually
just happens in spreadsheets, but can potentially happen with this process.
Computers do not like this! Just make sure when you paste the return link,
you do it in a different location in the document than your source.

The best thing about this process is that MS Office does all of the coding
for you. Another tip - this is not limited to Word-Excel. You can use the
Paste Special function with most MS Office products, and even use it within
the same document.

One drawback. Every time you open one of these linked documents, MS will
ask you whether you want to update the links. I have not found a way to get
around the error message, but the amount of work that one click saves seems
worth it to me.

"TomorrowsMan" wrote:

Hello there,

Is it possible to use Index/Match in a Word Table, or otherwise return
the formula result from an embedded Excel table to a word document
bookmark/formfield?

I have had a hard time figuring out how to take Word document bookmark
values, get them into some form of embedded Excel workbook, have
calculations run on them, then return the results to other bookmark
fields in the Word document.

I also tried embedding the Excel data in a userform (using the Excel
10.0 object) and retrurning it that way, but was simply baffled by the
vba syntax; for instance, I tried this:

excelsheet1.Spreadsheet1.Sheets("Sheet1").Range("A 25").Text = _
ActiveDocument.FormFields("EEIndex").Range.Text
msgbox excelsheet1.Spreadsheet1.Sheets("Sheet1").Range("A 25").Text

The msgbox pops up with the correct value, assuring me I coded the
first part correctly, but the bookmark ("EEIndex") does not populate
with the value.

Any direction or assistance would be greatly appreciated....!

Chris