compile error User-defined type not defined Set objXL = New Excel.Application

Hi All,

I am fairly new to Access VBA. In Access I am trying to set a command button to run a Module. I am currently using a Macro to accomplish this (openvisualbasicsmodual >Modual Name: "Module1" > Procedure Name: "sCopyRSToNamedRange")

This Macro is only opening the Module. I need a command to run the module

I need the following code to export the table data to an existing, saved excel file. It would have to start inserting in Column C Row 3 and insert the table rows down from there.

The Module is giving me a Compile error User-defined type not defined on VBA line Set objXL = New Excel.Application in the following Code:

Sub sCopyRSToNamedRange()
'Copy records to a named range
'on an existing worksheet on a
'workbook
'
'Dim objXL As Excel.Application
Dim objXL As Object
Set objXL = CreateObject("Excel.Application")
'Dim objWkb As Excel.Workbook
Dim objWkb As Object
Set objWkb = CreateObject("Excel.Workbook")
'Dim objSht As Excel.Worksheet
Dim objSht As Object
Set objSht = CreateObject("Excel.Worksheet")
Dim db As Database
Dim rs As Recordset
Const conMAX_ROWS = 20000
Const conSHT_NAME = "MySheet"
Const conWKB_NAME = "c:\temp\Test1.xls"
Const conRANGE = "RangeForRS"

  Set db = CurrentDb
  Set objXL = New Excel.Application
  Set rs = db.OpenRecordset(tblWireRack, dbWebADIItemManagment)
  With objXL
    .Visible = True
    Set objWkb = .Workbooks.Open(conWKB_NAME)
    On Error Resume Next
    Set objSht = objWkb.Worksheets(conSHT_NAME)
    If Not Err.Number = 0 Then
      Set objSht = objWkb.Worksheets.Add
      objSht.Name = conSHT_NAME
    End If
    Err.Clear
    On Error GoTo 0
    objSht.Range(conRANGE).CopyFromRecordset rs
  End With
  Set objSht = Nothing
  Set objWkb = Nothing
  Set objXL = Nothing
  Set rs = Nothing
  Set db = Nothing
End Sub

Thank you for Any help you can give on this.

Thank You, that did work however now I receive Run time error '429': ActiveX component can't create object:

Option Compare Database

Sub sCopyRSToNamedRange()
'Copy records to a named range
'on an existing worksheet on a
'workbook
'
'Dim objXL As Excel.Application
Dim objXL As Object
Set objXL = CreateObject("Excel.Application")
'Dim objWkb As Excel.Workbook
Dim objWkb As Object
Set objWkb = CreateObject("Excel.Workbook")
'Dim objSht As Excel.Worksheet
Dim objSht As Object
Set objSht = CreateObject("Excel.Worksheet")
Dim db As Database
Dim rs As Recordset
Const conMAX_ROWS = 20000
Const conSHT_NAME = "MySheet"
Const conWKB_NAME = "c:\temp\Test1.xls"
Const conRANGE = "RangeForRS"

  Set db = CurrentDb
  'Set objXL = New Excel.Application
  Set rs = db.OpenRecordset(tblWireRack, dbWebADIItemManagment)
  With objXL
    .Visible = True
    Set objWkb = .Workbooks.Open(conWKB_NAME)
    On Error Resume Next
    Set objSht = objWkb.Worksheets(conSHT_NAME)
    If Not Err.Number = 0 Then
      Set objSht = objWkb.Worksheets.Add
      objSht.Name = conSHT_NAME
    End If
    Err.Clear
    On Error GoTo 0
    objSht.Range(conRANGE).CopyFromRecordset rs
  End With
  Set objSht = Nothing
  Set objWkb = Nothing
  Set objXL = Nothing
  Set rs = Nothing
  Set db = Nothing
End Sub

The end goal of this module will be to get the excel workbook to open from a specified location and insert the Access table data into the excel sheet1 in text format starting at Column C Row 3.