Transient use of Excel from within Word (2010)
Posted on 2012-08-14
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.