Solved

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

Posted on 2015-01-14
8
313 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
Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

 
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
 
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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
which modules are active in VB6 project? 6 11
Excel Spacing Anomaly 4 23
Msbbox Notice (4 days) 27 58
Merging spreadsheets 8 40
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

773 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