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

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

0
sjgrey
Asked:
sjgrey
  • 3
  • 2
1 Solution
 
NorieCommented:
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

0
 
sjgreyAuthor Commented:
Thanks

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?
0
 
NorieCommented:
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.
0
 
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?
0
 
NorieCommented:
You didn't mention transferring code, for which you need the extensibility library and trust acccess to the VBA project set to yes.
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

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