Solved

Starting Excel via CreateObject

Posted on 2001-06-21
7
580 Views
Last Modified: 2012-06-22
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.
0
Comment
Question by:JimKirby
7 Comments
 
LVL 16

Expert Comment

by:Richie_Simonetti
Comment Utility
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
 
LVL 18

Accepted Solution

by:
bobbit31 earned 200 total points
Comment Utility
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
 
LVL 3

Expert Comment

by:jrspano
Comment Utility
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 3

Expert Comment

by:jrspano
Comment Utility
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
 
LVL 2

Expert Comment

by:chandukb
Comment Utility
Did u referenced the Excel Object Library, that should do it.

Chandu
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
Comment Utility
i am with jrspano:

its better to use

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

0
 

Author Comment

by:JimKirby
Comment Utility
Thanks, Bobbit31! Excel is in my Registry as "Excel.Application.8".  Everything runs OK with the ".8" added.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

728 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

9 Experts available now in Live!

Get 1:1 Help Now