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

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

Who is Participating?
NorieConnect With a Mentor VBA ExpertCommented:
You didn't mention transferring code, for which you need the extensibility library and trust acccess to the VBA project set to yes.
NorieVBA ExpertCommented:
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

sjgreyAuthor Commented:

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?
NorieVBA ExpertCommented:
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.
sjgreyAuthor Commented:
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?
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.

All Courses

From novice to tech pro — start learning today.