Excel automation, need little help !!!

[Alain]

Hi,

I am trying to automate an excel workbook with latest info from the internet.
I am getting the "Error429 ActiveX component cannot create object" when i
try to see if Excel is already open

The code I use is :
Dim objExcel As Object 'excel app
Dim objWBooks As Object

'check if Excel is open
Set objExcel = GetObject(, "Excel.Application") <------ error raise here
If Err.Number <> 0 Then
Err.Clear
booOpen = True
Set objExcel = CreateObject("Excel.Application")
End If


What is causing this error ?

Thanks

Al

 

[Dirk Goldgar]

Hi,

I am trying to automate an excel workbook with latest info from the
internet.
I am getting the "Error429 ActiveX component cannot create object" when i
try to see if Excel is already open

The code I use is :
Dim objExcel As Object 'excel app
Dim objWBooks As Object

'check if Excel is open
Set objExcel = GetObject(, "Excel.Application") <------ error raise here
If Err.Number <> 0 Then
Err.Clear
booOpen = True
Set objExcel = CreateObject("Excel.Application")
End If


What is causing this error ?

The error is expected to be raised if Excel isn't already open. The code is
set up to handle the error, but, unless you have executed the following
statement before raising the error:

On Error Resume Next

.... the default error-handling will catch the error and display an error
dialog. I suspect that you don't have that "On Error Resume Next" statement
in your code.

 

[Beetle]

It would appear that you don't have any error handling in your code. Your
code
should look something like;


Private Sub Something_SomeEvent ()
On Error GoTo HandleError

Dim objExcel As Object 'excel app
Dim objWBooks As Object

'check if Excel is open
Set objExcel = GetObject(, "Excel.Application")

'rest of code here

Exit_Here:
Exit Sub

HandleError:
If Err.Number = 429 Then 'Excel is not open
Set objExcel = CreateObject("Excel.Application")
Resume Next
Else
MsgBox Err.Number & "(" & Err.Description & ")"
Resume Exit_Here
End If

End Sub


_________

Sean Bailey

 

[Alain]

Thank you guys,

I do have an error handling but it is set for the entire function i am
creating, not for the Excel part, I just rework it based on your comments and
works fine

Al