Solved

Starting Excel via CreateObject

Posted on 2001-06-21
7
603 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
[X]
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
7 Comments
 
LVL 16

Expert Comment

by:Richie_Simonetti
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?
0
 
LVL 18

Accepted Solution

by:
bobbit31 earned 200 total points
ID: 6215603
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
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
else
   run property
endif
this happens each time a property or function is used of the xl object.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 3

Expert Comment

by:jrspano
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.
0
 
LVL 2

Expert Comment

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

Chandu
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 6216277
i am with jrspano:

its better to use

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

0
 

Author Comment

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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

730 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