CopyFromRecordSet gives 430 run-time error

[pman78x]

I am trying to export data from MS Access (2003) to MS Excel (2003). For some reason this works perfectly in the English version of MS Office 2003, but not in the Dutch version of Office 2003.

I get the following error message when the export routine gets to the CopyFromRecordSet statement: Run time error 430 Class does not support automation . or does not support expected interface.

Can anybody help me?

The code is run on the on click event of a command button. Part of the code:

Private Sub cmdFunctionTaskExcelwithSODs_Click()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qd As QueryDef
Dim objXL As Object
Dim objWkb As Object
Dim objSht As Object
Dim intMaxRow As Integer

Set db = CurrentDb()
Set qd = db.QueryDefs("qry_Function_Task_Matrix_Crosstab_with_SODs")
Set rs = qd.OpenRecordset
Const Template = "Function Task Matrix.xlt"
Const StartRow = 8


If rs.RecordCount > 0 Then
rs.MoveLast: rs.MoveFirst
intMaxRow = rs.RecordCount
intMaxCol = rs.Fields.Count
Set objXL = CreateObject("Excel.Application")
With objXL
.Visible = True
Set objWkb = .Workbooks.Open(DbaseDir$ & Template)
Set objSht = objWkb.Worksheets("Function-Task Matrix")
objSht.Name = "Task-Function Matrix"

With objSht
.Range(.Cells(StartRow, 2), .Cells(StartRow + intMaxRow, 2)) _
.CopyFromRecordset rs
End With
End If

rs.Close
qd.Close
db.Close

End sub

 

[Zion7]

do you have the excel object library, referenced?

 

[pman78x]

I compared the references in the Engelish and Dutch version: both are the same and the references are in the same order (priority). The following references are checked:
Visual Basic For Applications
Microsoft Access 11.0 Object Library
OLE Automation
Microsoft DAO 3.6 Object Library
Microsoft ActiveX Data Objects 2.1 Library
Microsoft Windows Common Controls 6.0 (SP6)

I also tried to add the Microsoft Excel 11.0 Object Library, but that makes no difference: the error remains.

 

[pman78x]

Problem is solved!

I re-registered the DAO 3.6 dll using the following command:
Regsvr32.exe "C:\Program Files\Common Files\Microsoft Shared\DAO\DAO360.DLL"

Then it worked!

See also:

http://www.thescripts.com/forum/thread211069.html