Web Development

Ask something
Discussion Locked

Opening Excel in VB6 using GetObject

0 helpful
7 replies

by johnleonard

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I am a wannabe VB programmer, and as such, am still learning the ropes. I am writing a little app to help a colleague generate reports on an excel spreadsheet he uses daily.

Have a look at the code below:

On Error Resume Next
Set objXl = GetObject(, "Excel.Application")
If Err.Number = 0 Then
blnXlOpen = True
Else
Set objXl = CreateObject("Excel.Application")
blnXlOpen = False
objXl.Visible = True
End If

It uses getobject to see if excel is already open. if so, then it sets a flag, if not, it sets the flag differently and opens excel. I 'recycled' the getobject code from the MSDN library, but here's my problem.
When I try and run the code from within the IDE (i'm using visual studio), I get a 'Run-time error 429 - ActiveXcomponent can't create object'. It highlights the Getobject line as the cause. but.... if I compile the code into an executable, it runs OK!
I reinstalled Office, thinking that its file associations were causing the problem, but that hasn't fixed it. I'm using Office XP.

Any ideas?

7 replies

by sl-campbell

Try using
Set xlObj = GetObject(, "Excel.Application.9")

and/or setting a reference to the "Microsoft Excel 9.0 Object Library"
under the menu item: Project | Reference

by johnleonard

Tried that suggestion, but it still doesn't work... I'm using Office XP, anyway, so I thought the object would be "excel.application.10". anyway, changing that to 9 or 10 doesn't work! adding the reference - nice thought, but that doesn't work either! thanks for your comments.

by sl-campbell

If you set a reference to the "Microsoft Excel 9.0 Object Library, try:
Dim xlObj As Excel.Application
Set xlObj = New Excel.Application

by ruuda

Hi,
That your code execution returns an error when you are in the IDE, eventhough the statement says "On Error Resume Next", means that the option "break on all errors" is selected in the options of your IDE.When compiled, the code runs as intended. Change the option in your IDE and the problem "26 - activeX component cannot create object" will not happen.

by johnleonard

Thanks very much - well spotted! I had also neglected to mention that this code worked on another machine I had with the same IDE - when I looked at the 'break on all errors' option it had been set differently.

Thanks very much for your help.

This discussion is closed to new replies.