Transient use of Excel from within Word (2010)

Posted on 2012-08-14
Medium Priority
Last Modified: 2012-08-15
I realise that this is a complex query and I'll be grateful for any assistance even if it's just to tell me where to look for reference material or to go back to the drawing board.

Ideally, I would like to:

1.  Open an Excel file from within Word, pass it a small amount of data, have it open on the screen as long as the user wants then have it close and delete the excel file.

2.  Have both spreadsheet formulae, control buttons and VBA in the Excel file.

And for the icing on the cake

3.  Not rely on templates or pre-existing Excel files, so that anyone who has the Word file can use this without needing to set up special templates or files in known locations.

I've found indications of how to start Excel, including opening a named file which I would rather not do, pass it data and I think I saw how to close it but I haven't seen any discussion of setting up formulae, buttons or, even better, VBA in an blank Excel file opened by Word.  

For instance, can I keep a slab of VBA in my Word VBA and place it into the Excel file to run there, set up macro buttons, spreadsheet formulae and so on in Excel but using Word or VBA transferred to Excel by Word?

Alternatively, if my whole concept is fundamentally flawed, is there another way?

The Excel pop up is intended to present quantitative analysis of the values passed to it using a mix of graphics and text based on the data's characteristics, e.g. "The gap between these three values is unrealistically small, could it be that ...List of alternatives I want the user to think about.
Question by:sjgrey
  • 2
LVL 76

Accepted Solution

GrahamSkan earned 2000 total points
ID: 38295260
This small Word macro shows that you can create a new workbook, and add some code to it. I am aware that there is a lot more to your question, but I hope this is enough to get you started.

It uses early binding, so you need to set references to the Microsoft Excel Object and the Microsoft Visual Basic for Applications Extensibility Libraries. You will also need to  allow access to the VBA project Object model in the Trust Centre settings for both applications.
Sub ProcessExcel()
    Dim xlApp As Excel.Application
    Dim xlWbk As Excel.Workbook
    Dim xlWks As Excel.Worksheet
    Dim vbProjWrd As VBIDE.VBProject
    Dim vbProjXl As VBIDE.VBProject
    Dim vbComp As VBComponent
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True
    Set xlWbk = xlApp.Workbooks.Add
    Set vbProjWrd = ThisDocument.VBProject
    Set vbProjXl = xlWbk.VBProject
    Set vbComp = vbProjWrd.VBComponents("Module2")
    vbComp.Export Environ("Temp") & "\MyFile.bas"
    vbProjXl.VBComponents.Import Environ("Temp") & "\MyFile.bas"

End Sub

Open in new window

LVL 76

Expert Comment

ID: 38295577
Here is a link to more advice on using the VBA Extensibility facility:

And this is a bit more code to show how to add a Forms button and to assign a macro to it.
    Dim db As Button
    Set xlWks = xlWbk.Worksheets(1)
    Set db = xlWks.Buttons.Add(300, 100, 80, 40)
    db.OnAction = "OKMacro"
    db.Caption = "OK"

Open in new window


Author Closing Comment

ID: 38296005
That's fantastic - I actually think I can follow it!

This has given me the backbone of what I need and I believe I'll be able to build on it


Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

850 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