Re: CopyFromRecordset Run-time error 430:

Kevin McCartney wrote:
>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 Work***
> 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

--
Marsh
MVP [MS Access]
.