Access 2003- vba runtime error 429 activex componentcant create object 2

[justagrunt]

Hi,
Using Access 2003 service pack 1.
This code is part of a private module attached to a form.

Private Sub Open_Excel()
'Find and open the Excel workbook with
'the pricing for the variuos Selector sizes.
'The Workbook does not have to be visible.

'set up variables for working with excel.
Dim xlapp As excel.Application 'Excel application Object
Dim xlbook As excel.Workbook 'Excel object-workbook
Dim xlsheet As excel.Sheets 'excel worksheet within the workbook

'create an Excel instance i.e set up an active instance
'use an existing instance if there is one;otherwise
'create a new instance

Set xlapp = GetObject(, "Excel.application")
If Err.Number = 429 Then
'excel isn't running: create Excel Instance
Set xlapp = CreateObject("Excel.Application")
Err.Clear
End If


'Open relevant workbook and page
xlapp.Visible = False
xlbook = xlapp.Workbook.Open("C:\My Documents\2WAYPRICES.xls")
xlapp.Sheets(2).Select

End Sub

Compiles ok but traps at error 429 activex component cant create object at line,

Set xlapp = GetObject(, "Excel.application")

Even if Dim xlapp as object etc is used I get the same error.

Any suggestions.
Perplexed.
Regards
bill

 

[RoyVidar]

I believe the 429 in this case, just means there isn't an open instance of Excel to get. So it should go ahead creating it, just include:

[tt]on error resume next
Set xlapp = GetObject(, "Excel.application")
If Err.Number = 429 Then
'excel isn't running: create Excel Instance
Err.Clear
Set xlapp = CreateObject("Excel.Application")
End If
on error goto <your error handler>[/tt]

Roy-Vidar

 

[JeffTullin]

You aren't using error trapping correctly.
On the Set.... line, an error is raised because there is no Excel running.

However, you are not 'handling' the error, which is why Access stops dead.

Instead, you need an 'on error' construct, like this:

    On Error GoTo errtrap
    
        Set xlapp = GetObject(, "Excel.application")

Exit Sub
errtrap:

 If Err.Number = 429 Then
  'excel isn't running: create Excel Instance
  Set xlapp = CreateObject("Excel.Application")
 else
  'something else went wrong  
 End If
        
 Resume Next
End Sub

 

[justagrunt]

Thanks Gentlemen,
Again I appreciate the valued assistance.
Kind Regards
bill