VBA - Use embeded excel in word to calculate and insert into a userform text box in word

Hi Guys,

I'm trying to figure out a way to do calculations in a word userform, its a very complex calculation so it would be best if it was possible to embed a excel document in the word document, do the calculation and then send back the values into the textboxes from the cells... is this possible and how? or is there a better way to do this?

please advise, thanks alot in advance!
LVL 1
HakumAsked:
Who is Participating?
 
GrahamSkanConnect With a Mentor RetiredCommented:
You can use code like this to read the data.
Sub GetExcelData()
    Dim xlWbk As Excel.Workbook
    Dim xlWks As Excel.Worksheet
    Set xlWbk = ActiveDocument.InlineShapes(1).OLEFormat.Object
    Set xlWks = xlWbk.Sheets(1)
    
    UserForm1.TextBox1.Text = xlWks.Cells(1, 1).Value
    UserForm1.Show
End Sub

Open in new window

0
 
Rob HensonFinance AnalystCommented:
It is obviously possible to embed an Excel document into a Word document and with more recent versions of Office this works very well.

Why would would you want the values from the Excel sheet to onward populate some text boxes? How about just formatting the embedded Excel sheet such that it becomes part of the Word document and shows the relevant values in the right places.

Thanks
Rob H
0
 
HakumAuthor Commented:
the reason is that the same value will be used on different pages in the word document and i would like to populate it with bookmarks in the document, i'm find a hard time figuring out how i would do that with inserting a excel object in the document, or is there something that i'm missing?
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Rob HensonFinance AnalystCommented:
Thinking outside the box, is this a document that will be sent to multiple people with different calculations for each person?

If so, consider Mail Merge; do the calculations for each person in an Excel workbook and refer to calculated fields the same as you would with static fields ie people's details.

Thanks
Rob H
0
 
HakumAuthor Commented:
@Rob - yes it is, and thought of that but we would very much like to have a single document which the users should use instead of distributing multiple documents

@Graham - Hi! Awesome, i'm a bit unaware how it reads the embedded document should it be embedded as a Fileobject or a excel object in the word document?
0
 
Rob HensonConnect With a Mentor Finance AnalystCommented:
The mail merge would be a single Word document, a template effectively, with an associated excel document; same as you have now.

When the users then run the merge, it will generate multiple documents for distribution to the recipients.

Thanks
Rob H
0
 
GrahamSkanRetiredCommented:
Harsh,
I tested this by Inserting the file so; Insert tab, Text group, Object button, Object... item, Create from File, Browse...

However, do consider Rob's suggestion of Mail Merge. You would have a central 'Main' document, from which mail merge would generate you produce copies with each copy individually tailored for each user.

It normally doesn't require any VBA code.
0
 
Rob HensonFinance AnalystCommented:
Mail Merge even has the option to generate tailored e-mails if that would be an alternative option!!

Thanks
Rob
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.