Error # 430

[Reggie]

Hi and thanks in advance. I have 3 machines. One running Win NT SP6, one
running XP Prof., and the other running W2K Prof. All are running Office
2K. I'm using automation to send data from queries to excel, format the
excel spreadsheet, and open/display results. On the NT & XP machine's no
problem. On the W2K machine I receive the following error : Error # 430:
Class does not support Automation or does not support expected interface.
I've checked all the references and they are identical on all machines. Has
anyone seen this and if so is there something different I have to do when
using the W2K machine? Thanks for your time!

--
Reggie

"Half this game is 90% mental."

----------

 

[TC]

Show us the code!

TC

 

[Reggie]

TC, Thanks for the reply. The problem occurs on the line
(objWS1.range("A2").CopyFromRecordset rstDetail). One other thing is
puzzling me too. When this fails on the W2K machine, I am prompted to save
the workbook when closing excel and the strange thing is the workbook number
increases each time, but when I do a control-alt-delete to see if excel has
any running instances there aren't any. I have error trapping so that
should an error occur all my references to excel are also destroyed and I've
verified that the process does complete, so I'm not sure why the workbooks
continue to increment. Anyway, hope I made this clear and any help you can
give is appreciated. One other thing, this occurs when the BE is on the
same machine and when placed on the server. Thanks again!

Sub ExportDailySummaryExcel(rstDetail As Recordset, rstSum1 As Recordset, _
rstSum2 As Recordset, rstStat As Recordset,
strTitle As String)
On Error GoTo ExportDailySummaryExcel_Err
Dim fld As Field
Dim objWS1 As Excel.Worksheet 'Detail worksheet
Dim DailyRange As Excel.range
Dim intRowCount As Integer
Dim intColCount As Integer
Dim rwIndex As Integer
Dim colIndex As Integer

'Attemt to launch excel (This calls function shown at bottom)
If CreateExcelObj() Then
gobjExcel.Workbooks.Add

'Creat Daily Worksheet
Set objWS1 = gobjExcel.ActiveSheet
objWS1.Name = "Daily Location Report"
intColCount = 1

'Deatail Recordset - Using field names as column headings
For Each fld In rstDetail.Fields
objWS1.Cells(1, intColCount) = fld.Name
intColCount = intColCount + 1
Next fld

'Send Detail Recordset to Excel
objWS1.range("A2").CopyFromRecordset rstDetail
With objWS1
.Cells.Font.Size = 10
.Columns("A:A").Delete Shift:=xlToLeft
.range("A1").Select
End With


Function CreateExcelObj() As Boolean
On Error GoTo CreateExcelObj_Err
CreateExcelObj = False
'Attempt to Launch Excel
Set gobjExcel = New Excel.Application
CreateExcelObj = True

CreateExcelObj_Exit:
Exit Function

CreateExcelObj_Err:
MsgBox "Couldn't Launch Excel!!", vbCritical, "Warning!!"
CreateExcelObj = False
Resume CreateExcelObj_Exit
End Function

--
Reggie

"Half this game is 90% mental."

----------

Show us the code!

TC

 

[TC]

TC, Thanks for the reply. The problem occurs on the line
(objWS1.range("A2").CopyFromRecordset rstDetail). One other thing is
puzzling me too. When this fails on the W2K machine, I am prompted to save
the workbook when closing excel and the strange thing is the workbook number
increases each time, but when I do a control-alt-delete to see if excel has
any running instances there aren't any. I have error trapping so that
should an error occur all my references to excel are also destroyed and I've
verified that the process does complete, so I'm not sure why the workbooks
continue to increment. Anyway, hope I made this clear and any help you can
give is appreciated. One other thing, this occurs when the BE is on the
same machine and when placed on the server. Thanks again!

Sub ExportDailySummaryExcel(rstDetail As Recordset, rstSum1 As Recordset, _
rstSum2 As Recordset, rstStat As Recordset,
strTitle As String)
On Error GoTo ExportDailySummaryExcel_Err
Dim fld As Field
Dim objWS1 As Excel.Worksheet 'Detail worksheet
Dim DailyRange As Excel.range
Dim intRowCount As Integer
Dim intColCount As Integer
Dim rwIndex As Integer
Dim colIndex As Integer

'Attemt to launch excel (This calls function shown at bottom)
If CreateExcelObj() Then
gobjExcel.Workbooks.Add

'Creat Daily Worksheet
Set objWS1 = gobjExcel.ActiveSheet
objWS1.Name = "Daily Location Report"
intColCount = 1

'Deatail Recordset - Using field names as column headings
For Each fld In rstDetail.Fields
objWS1.Cells(1, intColCount) = fld.Name
intColCount = intColCount + 1
Next fld

'Send Detail Recordset to Excel
objWS1.range("A2").CopyFromRecordset rstDetail
With objWS1
.Cells.Font.Size = 10
.Columns("A:A").Delete Shift:=xlToLeft
.range("A1").Select
End With


Function CreateExcelObj() As Boolean
On Error GoTo CreateExcelObj_Err
CreateExcelObj = False
'Attempt to Launch Excel
Set gobjExcel = New Excel.Application
CreateExcelObj = True

CreateExcelObj_Exit:
Exit Function

CreateExcelObj_Err:
MsgBox "Couldn't Launch Excel!!", vbCritical, "Warning!!"
CreateExcelObj = False
Resume CreateExcelObj_Exit
End Function

--
Reggie

"Half this game is 90% mental."

----------

 

[TC]

Reggie, it seems that lots of people have had this problem, but I can not
find a common cause. Here's what others have written about it.

-----------------

After installing Windows 2000 sp 4, I get an error using the
CopyFromRecordset method of Excel's Range object that I never got before:

Error 430: Class does not support Automation or does not support expected
interface.

I am using CopyFromRecordset in Microsoft Access 2002, with Excel 2002
installed, and the recordset rs is a DAO recordset:
wks.Range("A2").CopyFromRecordset rs

The error does NOT occur on Windows XP machines, whether with or without jet
sp 7 installed, only on Windows 2000 machines with sp4. The error did NOT
occur with sp3 installed.

-----------------

CopyFromRecordset generally works well there are some issues involved with
having just the right version of DAO (it seems that using 3.51 is the most
robust system and if 3.6 is around at all it may cause problems).

-----------------

We have a program that generates a number of reports from our database to
Excel. It works on every computer in our office except one. On that
machine, we get:

Run-time error 430:
Class does not support Automation or does not support expected
interface.

The only place I can find this on Microsoft's Support site is in article
Q246335, which says that you get this error when trying to use the
CopyFromRecordset with Excel 97, which doesn't support the method. Problem
is, we're using Excel 2000 on all desktops, including the one in question.

-- to which someone replied:

We've just fixed a similar problem with this error message by upgrading
Office 2000 to Office 2000 sr1 on the offending machines.

-----------------

Logon to MSDN and locate document Q246335, "HOWTO: Transfer data from an ADO
Recordset to Excel". The accompanying code shows how to do this using Excel
2000 *and* Excel 97. I've only tried the Excel 2000 part and can say the
CopyFromRecordset method is so fast it's almost scary <g>.

Another document you might want to have a look at is Q247412, "INFO: Methods
for Transferring Data to Excel from Visual Basic".

 

[Reggie]

TC, Thanks a lot for the help. When my client gets back in the office on
Monday I'll have him check out a few of the issues discussed in the
messages. If I find anything out I'll post back and let you know. Thanks
again!!

--
Reggie

"Half this game is 90% mental."

----------