• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 507
  • Last Modified:

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.
  • 2
1 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

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

sjgreyAuthor Commented:
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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

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.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now