Solved

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

Posted on 2015-01-14
8
318 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 33

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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 33

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 33

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 33

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial well show you how to find and replace special characters in Microsoft Word. This is similar to carriage returns to convert columns of values from Microsoft Excel into comma separated lists.
In a previous video Micro Tutorial here at Experts Exchange (http://www.experts-exchange.com/videos/1358/How-to-get-a-free-trial-of-Office-365-with-the-Office-2016-desktop-applications.html), I explained how to get a free, one-month trial of Office …

807 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