Error trying to open new Excel file from Word 2010 using VBA

Posted on 2012-08-26
Last Modified: 2012-08-26
I've seen several references saying that a new Excel file can be opened using VBA in Word.  I'm using Office 2010 and trying to open an Excel file to display some information that the Word application will dump into Excel but I get a compile error "User-defined type not defined" on the line "Dim xlApp As Excel.Application" below.

I'm at a loss to know how to proceed as all examples look just like this but mine doesn't work.  The context is that a Word 2010 .docm file will be open and the macro will run from there.  Excel might or might not be running and I need to open a blank workbook that will be closed without saving after it has done its job.

Sub ProcessExcel()

Dim xlApp As Excel.Application
Dim xlRangeDisplay 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 xlRangeDisplay = xlApp.Workbooks.Add

End Sub

Open in new window

Question by:sjgrey
    LVL 33

    Expert Comment

    You have 2 choices:

    1 Add a reference to Microsoft Excel X.0 Object Library via Tools>References... (the X in X.0 is dependent in version).

    2 Change the declarations in the code to this.

    Dim xlApp As Object 'Excel.Application
    Dim xlRangeDisplay As Object ' Excel.Workbook
    Dim xlWks As Object ' Excel.Worksheet

    Open in new window

    LVL 1

    Author Comment


    Suggestion #1 works

    Will this setting be carried with the file if I pass it to a colleague or will I have to give them instructions to make this setting before using the code?
    LVL 33

    Expert Comment

    Does the second suggestion not work?

    That's the one I would have recommended using if this is going to be sent to other people.

    The reference in the first suggestion should carry over but if the other user has a different version of Excel there could be problems.
    LVL 1

    Author Comment

    I just undid the link to the Excel object library and tried the second suggestion which does work

    In both cases I had to also select Microsoft Visual Basic for Applications Extensibility to get the second part of the code, to transfer some VBA into the Excel  file, to work.

    Is there a way round that as well?
    LVL 33

    Accepted Solution

    You didn't mention transferring code, for which you need the extensibility library and trust acccess to the VBA project set to yes.

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
    The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
    This video shows where to find templates, what they are used for, and how to create and save a custom template using Microsoft Word.
    Office 365 is currently available in five editions. Three of them are for business use: Office 365 Business Essentials, Office 365 Business, and Office 365 Business Premium. Two of them are for home/personal use: Office 365 Home and Office 365 Perso…

    745 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

    14 Experts available now in Live!

    Get 1:1 Help Now