CopyFromRecordset Run-time error 430:

[Kevin McCartney]

Hi TWIMC

The error message that I get initiates on the code line
rge.CopyFromRecordset rst. The error message returned is 'Run-time error 430:
Class does not support Automation or does not support expected interface.'
The strange thing is that this procedure has been working on all users for
several months I've just set up a new user but it doesn't work on her
machine. I've check the references within Access and Excel on the selected
references and they are all the same version as my own settings. I've also
checked the data within the two queries and they return correct outputs with
no OLE Objects. Any ideas on how to resolve this issue would be much
appreciated.

And no you can't change the order of References!

Dim xlApp As New Excel.Application
Dim wbk As Workbook
Dim ws As Worksheet
Dim rge As Range
Dim rst As DAO.Recordset
Dim fld As DAO.Field

DoCmd.Hourglass True
Select Case strOpenArgs
Case "BANKER"
Set rst = CurrentDb.OpenRecordset("qselREPORT_BASE_BANKER",
dbOpenSnapshot)
Case "DEAL"
Set rst = CurrentDb.OpenRecordset("qselREPORT_BASE_DEAL",
dbOpenSnapshot)
End Select

Set wbk = xlApp.Workbooks.Add
Set ws = wbk.Sheets.Add
Set rge = ws.Range("A2")
ws.Name = "DATA"

For Each fld In rst.Fields
ws.Cells(1, fld.OrdinalPosition + 1) = fld.Name
Next

rge.CopyFromRecordset rst
xlApp.ActiveWindow.Zoom = 85
ws.Range("A2").Activate
xlApp.ActiveWindow.FreezePanes = True

For Each ws In wbk.Worksheets
If ws.Name <> "DATA" Then wbk.Worksheets(ws.Index).Delete
Next

DoCmd.Hourglass False
xlApp.Visible = True

On Error Resume Next
rst.Close
Set rst = Nothing
Set wbk = Nothing
Set ws = Nothing
Set rge = Nothing

End Function

TIA
KM

 

[Marshall Barton]

The error message that I get initiates on the code line
rge.CopyFromRecordset rst. The error message returned is 'Run-time error 430:
Class does not support Automation or does not support expected interface.'
The strange thing is that this procedure has been working on all users for
several months I've just set up a new user but it doesn't work on her
machine. I've check the references within Access and Excel on the selected
references and they are all the same version as my own settings. I've also
checked the data within the two queries and they return correct outputs with
no OLE Objects. Any ideas on how to resolve this issue would be much
appreciated.

And no you can't change the order of References!

Dim xlApp As New Excel.Application
Dim wbk As Workbook
Dim ws As Worksheet
Dim rge As Range
Dim rst As DAO.Recordset
Dim fld As DAO.Field

DoCmd.Hourglass True
Select Case strOpenArgs
Case "BANKER"
Set rst = CurrentDb.OpenRecordset("qselREPORT_BASE_BANKER",
dbOpenSnapshot)
Case "DEAL"
Set rst = CurrentDb.OpenRecordset("qselREPORT_BASE_DEAL",
dbOpenSnapshot)
End Select

Set wbk = xlApp.Workbooks.Add
Set ws = wbk.Sheets.Add
Set rge = ws.Range("A2")
ws.Name = "DATA"

For Each fld In rst.Fields
ws.Cells(1, fld.OrdinalPosition + 1) = fld.Name
Next

rge.CopyFromRecordset rst
xlApp.ActiveWindow.Zoom = 85
ws.Range("A2").Activate
xlApp.ActiveWindow.FreezePanes = True

For Each ws In wbk.Worksheets
If ws.Name <> "DATA" Then wbk.Worksheets(ws.Index).Delete
Next

[...]


The references are probably the issue so double check that
the version of Excel on that user's machine is the same as
the version in your references list. If it isn't, then you
either have to update Excel on that machine or modify your
code to use Late Binding (so you can remove the reference).

I don't think(?) this applies in this case, but it might be
worth checking if the user is using AXP or A03 and has
applied the recent service pack:
http://support.microsoft.com/kb/904953/en-us