asked on

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.

Richie_Simonetti

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?

ASKER CERTIFIED SOLUTION

bobbit31

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jrspano

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.

jrspano

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.

chandukb

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

Chandu

Richie_Simonetti

i am with jrspano:

its better to use

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

JimKirby

ASKER

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