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

[LeslyeE]

Hi All,


I am trying to Accomplish the following:

1. Create a form that has a search function to search an item list but allow the user to also search through the item list using a bar to move up and down through the list.
2. allow the user to edit only 1 column in the list (qty)
3. list is stored in a table named "tblWireRack"
4. in a command button, ******* event I would like the command button to run a modual:I am currently using a Macro to accomplish this (openvisualbasicsmodual >Modual Name: "Module1" > Procedure Name: "sCopyRSToNamedRange") [This is only opening the Modual. I need a command to run the modual]
5. The Modual is giving me a Compile error User-defined type not defined on VBA line Set objXL = New Excel.Application in the following Code:

    [/FONT][FONT=arial]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 primary issue right now is the Compile error. If you have any advice on any of the other needs please share.
   
   Thank you!!
   
   
   
   

 

[Norie]

Why are you creating 2 instances of Excel?

You create the first one here,

Set objXL = CreateObject("Excel.Application")

then try and create another one here.

Set objXL = New Excel.Application

Try removing the above code, if you do that the code should compile and you won't have any problems with multiple instances of Excel.

 

[LeslyeE]

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

Set objWkb = CreateObject("Excel.Workbook")

in the following code.

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

 

[LeslyeE]

I am trying 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.

 

[Norie]

You don't need that line either because late on in the code you have this,

    Set objWkb = .Workbooks.Open(conWKB_NAME)

which opens the specified workbook and creates reference, obkWkb, to it.

You also don't need this.

Set objSht = CreateObject("Excel.Worksheet")

 

[LeslyeE]

Bothhave been removed. Now I am reciving :

runtime error 3001 invalid argument
on line:

Set rs = db.OpenRecordset(tblWireRack, dbWebADIItemManagment)

Here is the updated 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

I found this link for resolution but I can't quite identify the change needed to make this work.
https://www.google.com/webhp?source...argument+ms+access+Set+rs+=+db.OpenRecordset(

 

[Norie]

When using OpenRecordset the name of the table/query should be in quotes.