Solved

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

Posted on 2015-01-14
8
306 Views
Last Modified: 2015-01-20
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!
0
Comment
Question by:Hakum
  • 4
  • 2
  • 2
8 Comments
 
LVL 32

Expert Comment

by:Rob Henson
ID: 40548603
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
 
LVL 1

Author Comment

by:Hakum
ID: 40548647
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
 
LVL 76

Accepted Solution

by:
GrahamSkan earned 250 total points
ID: 40548667
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
 
LVL 32

Expert Comment

by:Rob Henson
ID: 40548723
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 1

Author Comment

by:Hakum
ID: 40548728
@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
 
LVL 32

Assisted Solution

by:Rob Henson
Rob Henson earned 250 total points
ID: 40548768
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
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 40548803
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
 
LVL 32

Expert Comment

by:Rob Henson
ID: 40548818
Mail Merge even has the option to generate tailored e-mails if that would be an alternative option!!

Thanks
Rob
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

A few years ago I was very much a beginner at VBA, and that very much remains the case today.  I'll do my best to explain things as I go in the hope that other beginners can follow.  If you just want to check out a tool that creates a Select Case fu…
Microsoft Word is a program we have all encountered at some point, but very few of us have dug deep into its full scope of features, let alone customized it to suit our needs. Luckily making the ribbon (aka toolbar, first introduced in Word 2007) wo…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now