asked on

Transient use of Excel from within Word (2010)

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.

ASKER CERTIFIED SOLUTION

GrahamSkan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

GrahamSkan

Here is a link to more advice on using the VBA Extensibility facility:
http://www.cpearson.com/excel/vbe.aspx

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

sjgrey

ASKER

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

Thanks