Starting Excel via CreateObject

I want to start up and display an instance of Excel 97 from my VB 5.0 project. Should be easy, right? I've been trying to use the same Automation concept with Excel that has always worked OK with Word 97, like this:

   (Reference to MS Excel 8.0 Library established first)
   Dim xl As Excel.Application
   Set xl = CreateObject("Excel.Application")
   xl.Visible = True
   'More code to manipulate xl . . .
   xl.Quit
   Set xl = Nothing
   
PROBLEM: The code stops on the "CreateObject" line with, "Run-Time Error 429: ActiveX component can't create object."

BUT, if I change the Set Statement to use the New keyword instead of CreateObject:

     Set xl = New Excel.Application

then Excel starts, but minimized (I have to use xl.WindowState=xlNormal to get it displayed. Seems strange, but I could live with it).

Anybody got any ideas why the CreateObject approach is not working with Excel? Microsoft dox say it outta work.
JimKirbyAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
bobbit31Connect With a Mentor Commented:
it works for me...

search your registry for "Excel.Application"

you might also want to try:
createobject("Excel.Application.8")

btw:

Dim xl as new excel.application is early binding

whereas

dim xl as object

set xl = CreateObject(...) is late binding

the difference is:

Late Binding
When you declare a variable As Object or As Variant, Visual Basic cannot determine at compile time what sort of object reference the variable will contain. Therefore, Visual Basic must use late binding to determine at run time whether the actual object has the properties and methods you call using the variable.

Note   Late binding is also used for variables declared As Form or As Control.

Each time you invoke a property or method with late binding, Visual Basic passes the member name to the GetIDsOfNames method of the object?s IDispatch interface. GetIDsOfNames returns the dispatch ID, or DispID, of the member. Visual Basic invokes the member by passing the DispID to the Invoke method of the IDispatch interface.

For an out-of-process component, this means an extra cross-process method call, essentially doubling the call overhead.

Note   You cannot call the methods of the IDispatch interface yourself, because this interface is marked hidden and restricted in the Visual Basic type library.

Early Binding
If Visual Basic can tell at compile time what object a property or method belongs to, it can look up the DispID or vtable address of the member in the type library. There?s no need to call GetIDsOfNames.

When you declare a variable of a specific class ? for example, As Widget ? the variable can only contain a reference to an object of that class. Visual Basic can use early binding for any property or method calls made using that variable.

---> This is the recommended way to declare object variables in Visual Basic components and applications.

Important   Whether early or late binding is used depends entirely on the way variables are declared. It has nothing to do with the way objects are created.

Tip   Early binding dramatically reduces the time required to set or retrieve a property value, because call overhead is a significant fraction of the total call time.


so basically, it's recommended that you use:

dim xl as new excel.application

good luck

0
 
Richie_SimonettiIT OperationsCommented:
I did paste your code directly on a form and works without any problem.
Do you have Excel installed on machine that has this behaviour?
0
 
jrspanoCommented:
so basically, it's recommended that you use:

dim xl as new excel.application

its better to use

dim xl as excel.application
set xl = new excell.application

if you just use the as new excell.application the app has to check each time any funciton or property is used for xl.

it is equivalient to writing

if xl = nothing then
   make new xl
   run property
else
   run property
endif
this happens each time a property or function is used of the xl object.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
jrspanoCommented:
from msdn
Dim tdfOrders As New TableDef
tdfOrders.Name = "Orders"

Note   Using variables declared using the New keyword can slow your application. Every time Visual Basic encounters a variable declared using New, it must test whether or not an object reference has already been assigned to the variable.

For example, you could assign a reference to an Excel object to either of the following variables:

Dim xlApp1 As Excel.Application
Set xlApp1 = New Excel.Application

Dim xlApp2 As Object
Set xlApp2 = CreateObject("Excel.Application")

Code that uses variable xlApp1 is early-bound and will execute faster than code that uses variable xlApp2, which is late-bound.
0
 
chandukbCommented:
Did u referenced the Excel Object Library, that should do it.

Chandu
0
 
Richie_SimonettiIT OperationsCommented:
i am with jrspano:

its better to use

dim xl as excel.application
set xl = new excel.application

0
 
JimKirbyAuthor Commented:
Thanks, Bobbit31! Excel is in my Registry as "Excel.Application.8".  Everything runs OK with the ".8" added.
0
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.