Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Starting Excel via CreateObject

Posted on 2001-06-21
Medium Priority
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 . . .
   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.
Question by:JimKirby
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 16

Expert Comment

ID: 6215574
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?
LVL 18

Accepted Solution

bobbit31 earned 800 total points
ID: 6215603
it works for me...

search your registry for "Excel.Application"

you might also want to try:


Dim xl as new excel.application is early binding


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


Expert Comment

ID: 6215849
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
   run property
this happens each time a property or function is used of the xl object.

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.


Expert Comment

ID: 6215858
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.

Expert Comment

ID: 6215865
Did u referenced the Excel Object Library, that should do it.

LVL 16

Expert Comment

ID: 6216277
i am with jrspano:

its better to use

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


Author Comment

ID: 6216523
Thanks, Bobbit31! Excel is in my Registry as "Excel.Application.8".  Everything runs OK with the ".8" added.

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
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 process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

721 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