Solved

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

Posted on 2015-01-14
8
329 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Office 365 is currently available in five editions. Three of them are for business use: Office 365 Business Essentials, Office 365 Business, and Office 365 Business Premium. Two of them are for home/personal use: Office 365 Home and Office 365 Perso…

623 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