Transient use of Excel from within Word (2010)

Posted on 2012-08-14
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
    LVL 76

    Accepted Solution

    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

    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

    LVL 1

    Author Closing Comment

    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


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    I'm writing to share my clumsy experience in using this elegant tool so you can avoid every stupid mistake I made. (I leave it to the authorities to decide if this deserves a place in the Knowledge archives.)  Now that I am on the other side of my l…
    Introduction This tutorial provides instructions on how to properly format your Word document using the inbuilt tools provided. The benefits of using these tools means your documents are more accessible and easily portable to other applications an…
    Learn how to make your own table of contents in Microsoft Word using paragraph styles and the automatic table of contents tool. We'll be using the paragraph styles in Word’s Home toolbar to help you create a table of contents. Type out your initial …
    This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

    760 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

    7 Experts available now in Live!

    Get 1:1 Help Now