Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-22-2006, 03:25 AM   #1
pman78x
Guest
 
Posts: n/a
CopyFromRecordSet gives 430 run-time error

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 Ducth 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.

Van anybody help me?

Part of the code:

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_Matrix")
Set rs = qd.OpenRecordset
Const Template = "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("Matrix")
objSht.Name = "Matrix"

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

rs.Close
qd.Close
db.Close

  Reply With Quote
Old 07-22-2006, 05:46 AM   #2
PeterF
Newly Registered User
 
Join Date: Jun 2006
Posts: 290
Thanks: 2
Thanked 45 Times in 45 Posts
PeterF is on a distinguished road
Try naming the the worksheet different, I believe "matrix" is a reserved word in the dutch version. What is the name of the function??
PeterF is offline   Reply With Quote
Old 07-22-2006, 10:15 AM   #3
pman78x
Guest
 
Posts: n/a
I just mentioned 'Matrix' here to shorten the code.

The actual values are:
Const Template = "Function Task Matrix.xlt"
Set qd = db.QueryDefs("qry_Function_Task_Matrix_Crosstab_wi th_SODs")
Set objSht = objWkb.Worksheets("Function-Task Matrix")
objSht.Name = "Task-Function Matrix"

Appologies for the confusion.

The code is run on the on click event of a command button. It starts en ends with:
Private Sub cmdFunctionTaskExcelwithSODs_Click()
End Sub

  Reply With Quote
Old 07-23-2006, 01:40 AM   #4
pman78x
Guest
 
Posts: n/a
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

  Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Run Time Error jamphan General 4 06-02-2006 02:05 PM
Run Time Error 424 "Object Required" paulS30berks General 2 05-18-2006 02:59 AM
Run Time Error AN60 General 2 04-04-2004 09:55 AM
Email Problem (Run time error 2287) Johnkl Modules & VBA 3 10-06-2003 12:19 AM
3042 Run Time Error LynnaM Modules & VBA 2 06-03-2002 10:40 AM




All times are GMT -8. The time now is 04:07 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World