Reply
 
Thread Tools Display Modes
  #1   Report Post  
Posted to microsoft.public.word.docmanagement,microsoft.public.word.vba.general,microsoft.public.word.vba.userforms,microsoft.public.word.vba.beginners,microsoft.public.word.tables
TomorrowsMan TomorrowsMan is offline
external usenet poster
 
Posts: 4
Default 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

  #2   Report Post  
Posted to microsoft.public.word.docmanagement,microsoft.public.word.tables,microsoft.public.word.vba.beginners,microsoft.public.word.vba.general
Dallas64 Dallas64 is offline
external usenet poster
 
Posts: 17
Default Index Match in Word Table, or Embedded Excel?

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


  #3   Report Post  
Posted to microsoft.public.word.docmanagement,microsoft.public.word.tables,microsoft.public.word.vba.beginners,microsoft.public.word.vba.general
TomorrowsMan TomorrowsMan is offline
external usenet poster
 
Posts: 4
Default Index Match in Word Table, or Embedded Excel?

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



  #4   Report Post  
Posted to microsoft.public.word.docmanagement,microsoft.public.word.tables,microsoft.public.word.vba.beginners,microsoft.public.word.vba.general
Dallas64 Dallas64 is offline
external usenet poster
 
Posts: 17
Default Index Match in Word Table, or Embedded Excel?

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




Reply
Thread Tools
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Word 97 in Windows XP to maintain formatting Charlie''s Word VBA questions Microsoft Word Help 22 May 20th 23 08:51 PM
Converting WordPerfect 12 files to Word 2003 Curious New Users 4 May 19th 23 02:48 PM
Why dont MS just f**king re-write Word from scratch? Its dogsh*t Word Hater Microsoft Word Help 33 May 5th 23 02:52 PM
Index Match in Word Table, or Embedded Excel? TomorrowsMan Microsoft Word Help 3 September 21st 06 08:38 PM
Autoresize a copied Excel table lincoln Tables 1 April 7th 06 09:17 AM


All times are GMT +1. The time now is 07:40 PM.

Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 Microsoft Office Word Forum - WordBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Word"