Creating Excel objects causes Automation Error

[Ange T]

Hi there,

I'm having pain with the VB behind an Access form. The form is used to
create reports in Excel based on the details entered in the form. This
has always worked without error on my machine (NT4, Access 2k),
however as soon as I attempt to create anything on another machine
(NT4, Access 2k) which most users will be working from, I receive an
automation error.

The problem line with the code is:
Set objXL = New Excel.Application

and any line that attempts to access any Excel objects created will
also cause an automation error.

The declarations include:
Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet

and the references for the VB on both machines are:
Access 9.0 Object Library
ActiveX Data Objects 2.1 Library
DAO 3.6 Object Library
Excel 9.0 Object Library
OLE Automation
ActiveX Data Objects Recordset 2.5 Library

In break mode, as soon as the code hits any line referencing the
object (ie. either objXL, objWkb or objSht) it errors.

I would appreciate any advice that can be offered on this! It's so
frustrating that it works perfectly on one machine, but not on another
with a similar configuration. I am more than happy to post more
details if they'd be useful to help solve this...

Thanks in advance! Angela

[Ange T]

Hi again,

TC - I can definitely open Excel normally, it is used quite regularly
with no problems.

Steve - what seems to happen with your line on the offending machine
is this: When it hits the line set objXL = GetObject(,
"Excel.Application"), it errors and goes to the error handler. The
error is #429 as expected (since Excel is not open), however in then
attempting to execute Set objXL= CreateObject("Excel.Application") in
the error handler, an error occurs again. The message is Err #13 Type
Mismatch.

Thus, again the problem seems to be with setting objXL. Any other
ideas?

I have also tried using the declaration Dim objXL as Object and then
using the lines suggested. This works most of the time (on the
offending machine), however the CopyfromRecordset action (as well as
several others) cannot be used which is a crucial action for me.

Also, Steve the lines you suggested work perfectly on my machine, but
not the problematic machine (same as original problem).
Thanks again, I really appreciate your help! Angela
sg******@srs.gov (Steve Tahan) wrote in message news:<56*************************@posting.google.c om>...

I have an app that opens an existing Excel WB (a template) with the
following code I swiped from Helen Feddema's Access Archon article:

Dim objXL as Excel.Application

set objXL = GetObject(, "Excel.Application")

' If Excel is running, it uses the current running version.
' If not, it generates an error that is dealt with in the error
handler.

Error_Handler

If err.Number = 429 Then
Set objXL= CreateObject("Excel.Application")
Resume Next
Else
...

This works well for me.

Steve Tahan, ITS
Westinghouse Savannah River Co.

[TC]

Ange

Try the following an the bad pc. Do not set any error handling. What exactly
is the error number & message you get?

dim objXL as object
set objXL = createobject ("excel.application")

(If that works consistently, try again, this time dim'ming objXL as
Excel.Application.)

TC

"Ange T" <sm*************@yahoo.com> wrote in message
news:b7**************************@posting.google.c om...

Hi again,

TC - I can definitely open Excel normally, it is used quite regularly
with no problems.

Steve - what seems to happen with your line on the offending machine
is this: When it hits the line set objXL = GetObject(,
"Excel.Application"), it errors and goes to the error handler. The
error is #429 as expected (since Excel is not open), however in then
attempting to execute Set objXL= CreateObject("Excel.Application") in
the error handler, an error occurs again. The message is Err #13 Type
Mismatch.

Thus, again the problem seems to be with setting objXL. Any other
ideas?

I have also tried using the declaration Dim objXL as Object and then
using the lines suggested. This works most of the time (on the
offending machine), however the CopyfromRecordset action (as well as
several others) cannot be used which is a crucial action for me.

Also, Steve the lines you suggested work perfectly on my machine, but
not the problematic machine (same as original problem).
Thanks again, I really appreciate your help! Angela
sg******@srs.gov (Steve Tahan) wrote in message

news:<56*************************@posting.google.c om>...

I have an app that opens an existing Excel WB (a template) with the
following code I swiped from Helen Feddema's Access Archon article:

Dim objXL as Excel.Application

set objXL = GetObject(, "Excel.Application")

' If Excel is running, it uses the current running version.
' If not, it generates an error that is dealt with in the error
handler.

Error_Handler

If err.Number = 429 Then
Set objXL= CreateObject("Excel.Application")
Resume Next
Else
...

This works well for me.

Steve Tahan, ITS
Westinghouse Savannah River Co.

[TC]

Ange, it sounds to me as if you have not selected the Excel object library
in Tools:References. Try opening any module in design view, go to
Tools:References, and make sure that the entry for "Microsoft Excel 8.0
Object Library" is ticked. (8.0 might be different, depending on your Excel
version.)

HTH,
TC
Ange T <sm*************@yahoo.com> wrote in message
news:b7*************************@posting.google.co m...

Hi TC,

When I dim objXL as object - there is no error in creating the object
using set objXL = ...

(However I have deliberately tried to avoid using this approach as it
doesn't allow me to use functions such as copyfromrecordset etc.)

When I dim objXL as Excel.Application, the error at the line set objxl
= ... is error # 13 "Type mismatch".

Looking forward to hearing from you, Ange.
"TC" <a@b.c.d> wrote in message news:<1063774302.471686@teuthos>...

Ange

Try the following an the bad pc. Do not set any error handling. What exactly is the error number & message you get?

dim objXL as object
set objXL = createobject ("excel.application")

(If that works consistently, try again, this time dim'ming objXL as
Excel.Application.)

TC

[TC]

Hi Ange

I need to collect & review my thoughts on this.

Is this correct:

- You have the library "Microsoft Excel 9.0 Object Library" selected in
Tools:References.

- This code works ok:
dim objXL as object
set objXL = createobject ("excel.application")

Can you please repeat the following two tests, & say if they work ok, or
not; & if not, what is the error, & on what line:

(a)
dim objXL as Excel.Application
set objXL = createobject ("excel.application")

(b)
dim objXL as Excel.Application
set objXL = new excel.application

Sorry to be repetetive, but I'm losing track of what works, & what doesn't.

TC
"Ange T" <sm*************@yahoo.com> wrote in message
news:b7**************************@posting.google.c om...

Hi again TC,

I have checked again, and I definitely have the library "Microsoft
Excel 9.0 Object Library" selected.

Any other ideas?

I really appreciate your help - Ange.
"TC" <a@b.c.d> wrote in message news:<1064198533.565247@teuthos>...

Ange, it sounds to me as if you have not selected the Excel object library in Tools:References. Try opening any module in design view, go to
Tools:References, and make sure that the entry for "Microsoft Excel 8.0
Object Library" is ticked. (8.0 might be different, depending on your Excel version.)

HTH,
TC

[Ange T]

Hi TC,

Comments below:

Is this correct:

- You have the library "Microsoft Excel 9.0 Object Library" selected in
Tools:References.
Yes

- This code works ok:
dim objXL as object
set objXL = createobject ("excel.application")
Yes

Can you please repeat the following two tests, & say if they work ok, or
not; & if not, what is the error, & on what line:

(a)
dim objXL as Excel.Application
set objXL = createobject ("excel.application")
No. At line 'set objXL = createobject ("excel.application")', error
#13 "Type Mismatch".

(b)
dim objXL as Excel.Application
set objXL = new excel.application
No. At line 'set objXL = new excel.application', error #-2147221163
"Automation error: Interface not recognised"

Sorry to be repetetive, but I'm losing track of what works, & what doesn't.

Don't apologise - I really appreciate your time... Ange

[TC]

Ange, I am researching this & will get back to you within the hour
(perhaps), or tomorrow (Wednesday, where I live).

TC
Ange T <sm*************@yahoo.com> wrote in message
news:b7**************************@posting.google.c om...

Hi TC,

Comments below:

Is this correct:

- You have the library "Microsoft Excel 9.0 Object Library" selected in
Tools:References.
Yes

- This code works ok:
dim objXL as object
set objXL = createobject ("excel.application")

Yes

Can you please repeat the following two tests, & say if they work ok, or
not; & if not, what is the error, & on what line:

(a)
dim objXL as Excel.Application
set objXL = createobject ("excel.application")

No. At line 'set objXL = createobject ("excel.application")', error
#13 "Type Mismatch".

(b)
dim objXL as Excel.Application
set objXL = new excel.application

No. At line 'set objXL = new excel.application', error #-2147221163
"Automation error: Interface not recognised"

Sorry to be repetetive, but I'm losing track of what works, & what

doesn't.
Don't apologise - I really appreciate your time... Ange

[TC]

Ange

According to this article:
http://support.microsoft.com/support...es/q186063.asp

the text for error number -2147221163 is "Interface not REGISTERED" (my
emphasis). I think you probably just re-read it.

I am no expert in COM (the Microsoft technology behind your problem), but I
believe the problem might be, that Excel is not properly registered on the
bad PC(s). ("Registration" is the process whereby a product sets up certain
values in the system Registry on the PC.")

In general, as I understand it, there are three ways to fix a registration
problem:

(1) Uninstall & reinstall the product;

(2) Run the product executable with a special command-line switch that says,
"please reg-register yourself", or

(3) Run the regsvr32 program to re-register the product manually.

I'm not sure of the details of (2) & (3), so I will do more research
tomorrow & reply again.

In the meantime, if it was convenient to >>uninstall then re-install the
whole Office suite on the bad PC(s)<<, that is what I would try next.

Will get back to you further,
TC
TC <a@b.c.d> wrote in message news:1064300029.809767@teuthos...

Ange, I am researching this & will get back to you within the hour
(perhaps), or tomorrow (Wednesday, where I live).

TC
Ange T <sm*************@yahoo.com> wrote in message
news:b7**************************@posting.google.c om...

Hi TC,

Comments below:

Is this correct:

- You have the library "Microsoft Excel 9.0 Object Library" selected in Tools:References.

Yes

- This code works ok:
dim objXL as object
set objXL = createobject ("excel.application")

Yes

Can you please repeat the following two tests, & say if they work ok, or not; & if not, what is the error, & on what line:

(a)
dim objXL as Excel.Application
set objXL = createobject ("excel.application")

No. At line 'set objXL = createobject ("excel.application")', error
#13 "Type Mismatch".

(b)
dim objXL as Excel.Application
set objXL = new excel.application

No. At line 'set objXL = new excel.application', error #-2147221163
"Automation error: Interface not recognised"

Sorry to be repetetive, but I'm losing track of what works, & what

doesn't.

Don't apologise - I really appreciate your time... Ange

[TC]

Ange

Here is how to re-register Excel. Try this on the bad PC(s). Let me know
what happens.

Go to Start:Run, and type:

C:\path_to_excel\Excel.exe /regserver

replacing path_to_excel with the appropriate path.

This will cause Excel to rewrite all of its registry keys with their default
values, then quit. When you've done that, double-check that the reference
you have selected in Tools:References (in your database), is the right one
for the latest version of Excel on that PC. Then try your bad code again.

HTH,
TC
"TC" <a@b.c.d> wrote in message news:1064302351.902015@teuthos...

Ange

According to this article:
http://support.microsoft.com/support...es/q186063.asp

the text for error number -2147221163 is "Interface not REGISTERED" (my
emphasis). I think you probably just re-read it.

I am no expert in COM (the Microsoft technology behind your problem), but I believe the problem might be, that Excel is not properly registered on the
bad PC(s). ("Registration" is the process whereby a product sets up certain values in the system Registry on the PC.")

In general, as I understand it, there are three ways to fix a registration
problem:

(1) Uninstall & reinstall the product;

(2) Run the product executable with a special command-line switch that says, "please reg-register yourself", or

(3) Run the regsvr32 program to re-register the product manually.

I'm not sure of the details of (2) & (3), so I will do more research
tomorrow & reply again.

In the meantime, if it was convenient to >>uninstall then re-install the
whole Office suite on the bad PC(s)<<, that is what I would try next.

Will get back to you further,
TC
TC <a@b.c.d> wrote in message news:1064300029.809767@teuthos...

Ange, I am researching this & will get back to you within the hour
(perhaps), or tomorrow (Wednesday, where I live).

TC
Ange T <sm*************@yahoo.com> wrote in message
news:b7**************************@posting.google.c om...

Hi TC,

Comments below:

> Is this correct:
>
> - You have the library "Microsoft Excel 9.0 Object Library" selected in > Tools:References.

Yes

>
> - This code works ok:
> dim objXL as object
> set objXL = createobject ("excel.application")

Yes

>
> Can you please repeat the following two tests, & say if they work

ok,

or > not; & if not, what is the error, & on what line:
>
> (a)
> dim objXL as Excel.Application
> set objXL = createobject ("excel.application")

No. At line 'set objXL = createobject ("excel.application")', error
#13 "Type Mismatch".

>
> (b)
> dim objXL as Excel.Application
> set objXL = new excel.application

No. At line 'set objXL = new excel.application', error #-2147221163
"Automation error: Interface not recognised"

>
> Sorry to be repetetive, but I'm losing track of what works, & what

doesn't.

Don't apologise - I really appreciate your time... Ange

[Ange T]

Hi TC,

Thank you SO much for all the suggestions, sorry I haven't had the
chance to reply to you more quickly.

I did the re-registering thing you suggested below, and it (mostly)
worked! There is no longer the error "Automation error: interface not
registered" when using Set objXL = new excel.app which feels like a
spectacular breakthrough!

So, it is creating the Excel object fine using the Dim objXL as
excel.application.

However, now as soon as I get to using copyfromrecordset, I get the
error #430 "Class does not support Automation or does not support
expected interface". From this article:
http://support.microsoft.com/default...b;en-us;246335
it seems that such an error msg means I am passing an ADO recordset...
However my two declarations for the recordset being passed are:

Dim db As DAO.Database
Dim rs As DAO.Recordset

Have I just exchanged one problem for another??

Also, just as an aside, I tried to open and run everything for a third
completely different machine, and it all worked perfectly first time.
Unofortunately I cannot have Excel reinstalled on the offending
machine, as the IT bureucracy in my organisation won't let this happen
beofre 2008!

Thanks so much, Ange

"TC" <a@b.c.d> wrote in message news:<1064392611.160292@teuthos>...

Ange

Here is how to re-register Excel. Try this on the bad PC(s). Let me know
what happens.

Go to Start:Run, and type:

C:\path_to_excel\Excel.exe /regserver

replacing path_to_excel with the appropriate path.

This will cause Excel to rewrite all of its registry keys with their default
values, then quit. When you've done that, double-check that the reference
you have selected in Tools:References (in your database), is the right one
for the latest version of Excel on that PC. Then try your bad code again.

HTH,
TC

[Ange T]

Cannot find any evidence of having multiple versions of Excel...

I did a search for excel.exe and all there was was Excel 2000 (the one I'm using).
"TC" <a@b.c.d> wrote in message news:<1064302976.930969@teuthos>...

Also, do you have multiple versions of Excel on the bad PC(s)?

If so, are you sure that you have selected the correct reference in Tools :
References?

It the selected reference was for *other-than the latest version*, this
would maybe explain it.

HTH,
TC

[TC]

Hi Ange

It's great that we got a result :-)

You have thanked me for the suggestions - but also, I appreciate that you
perservered with this. There is nothing more frustrating than trying to help
with a problem, but the person stops responding in the middle of the
process, so I don't know whether I helped (or not), or whether they solved
the problem (& if so, how).

Ange, I literally have to get off this PC *right now*. I will get back to
your other problem tomorrow. Alternatively, if anyone else can jump in, that
would be fine.

Cheers,
TC

Ange T <sm*************@yahoo.com> wrote in message
news:b7**************************@posting.google.c om...

Hi TC,

Thank you SO much for all the suggestions, sorry I haven't had the
chance to reply to you more quickly.

I did the re-registering thing you suggested below, and it (mostly)
worked! There is no longer the error "Automation error: interface not
registered" when using Set objXL = new excel.app which feels like a
spectacular breakthrough!

So, it is creating the Excel object fine using the Dim objXL as
excel.application.

However, now as soon as I get to using copyfromrecordset, I get the
error #430 "Class does not support Automation or does not support
expected interface". From this article:
http://support.microsoft.com/default...b;en-us;246335
it seems that such an error msg means I am passing an ADO recordset...
However my two declarations for the recordset being passed are:

Dim db As DAO.Database
Dim rs As DAO.Recordset

Have I just exchanged one problem for another??

Also, just as an aside, I tried to open and run everything for a third
completely different machine, and it all worked perfectly first time.
Unofortunately I cannot have Excel reinstalled on the offending
machine, as the IT bureucracy in my organisation won't let this happen
beofre 2008!

Thanks so much, Ange

"TC" <a@b.c.d> wrote in message news:<1064392611.160292@teuthos>...

Ange

Here is how to re-register Excel. Try this on the bad PC(s). Let me know
what happens.

Go to Start:Run, and type:

C:\path_to_excel\Excel.exe /regserver

replacing path_to_excel with the appropriate path.

This will cause Excel to rewrite all of its registry keys with their default values, then quit. When you've done that, double-check that the reference you have selected in Tools:References (in your database), is the right one for the latest version of Excel on that PC. Then try your bad code again.
HTH,
TC

[TC]

Ange T <sm*************@yahoo.com> wrote in message
news:b7**************************@posting.google.c om...

(snip)

However, now as soon as I get to using copyfromrecordset, I get the
error #430 "Class does not support Automation or does not support
expected interface". From this article:
http://support.microsoft.com/default...b;en-us;246335
it seems that such an error msg means I am passing an ADO recordset...
However my two declarations for the recordset being passed are:
Can you show me all the lines of code so I can see the error in context?

Also, just as an aside, I tried to open and run everything for a third
completely different machine, and it all worked perfectly first time.
Unofortunately I cannot have Excel reinstalled on the offending
machine, as the IT bureucracy in my organisation won't let this happen
beofre 2008!

Good grief! Could you get around this by saying that you are not
"re-installing" it, you are "re-registering" it to fix an error in the
existing installation?

HTH,
TC

[Ange T]

Hi again TC,

Thanks for all your messages, and sorry for not responding quicker.
Here is the CopyFromRecordset line in contex...

Private Sub CreateSummary(strFullPath as String)
Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim x As Integer
Dim intTotal as Integer
Dim strSQL as String

On Error GoTo ReportErr

'Set objXL = GetObject(, "Excel.Application")
Set objXL = New Excel.Application

'objXL.Visible = True

With objXL
Set objWkb = .Workbooks.Open("\\Server1\Report 1.xls")

Set db = CurrentDb

Set objSht = objWkb.Worksheets("Portfolio Summary")
objSht.Activate
.Range("USD").Value = varUSD

strSQL = "Select Name, Value, from Query1"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
rs.MoveLast
intTotal = rs.RecordCount
rs.MoveFirst

For x = 1 To intTotal
.Rows("10:10").Select
.Selection.Insert Shift:=xlDown
Next x

.Range("Range1").CopyFromRecordset rs

Set rs = Nothing

End With

objWkb.SaveAs (strFullPath)
objXL.Quit

Set objSht = Nothing
Set objWkb = Nothing
Set objXL = Nothing

Exit Sub

ReportErr:
'If Err.Number = 429 Then
' Set objXL = CreateObject("Excel.Application")
' Resume Next
'End If

objXL.Quit

Set objSht = Nothing
Set objWkb = Nothing
Set objXL = Nothing

End Sub

Can you show me all the lines of code so I can see the error in context?
Good grief! Could you get around this by saying that you are not
"re-installing" it, you are "re-registering" it to fix an error in the
existing installation?

I might try my luck with this - will let you know how it goes!

Thanks again, Ange.

[TC]

Hi Ange

Your previous posts have disappeared from my newsserver, & unfortunately I
can't remember what error you got on that line. (It is always good to repeat
the relevant information, just in case this happens.) But let's try some
ideas, anyhow.

First, I'm wondering whether the Excel CopyFromRecordset method would
require an Excel VBA recordset, not an Access VBA recordset. Try Dim'ing rs
"as object", not "as DAO.recordset", & see if that helps.

Second - and I think that this is probably it - your statement:

.Range("Range1").CopyFromRecordset rs
is referring to objXL - not to objWkb, or objSht. "Unqualified" dot
references do not reference the preceding "set" object - they reference the
preceing "with" statement's object, which in your case, is objXL; and I
doubt that the Excel object has a Range property or method?

HTH,
TC
Ange T <sm*************@yahoo.com> wrote in message
news:b7**************************@posting.google.c om... Hi again TC,

Thanks for all your messages, and sorry for not responding quicker.
Here is the CopyFromRecordset line in contex...

Private Sub CreateSummary(strFullPath as String)
Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim x As Integer
Dim intTotal as Integer
Dim strSQL as String

On Error GoTo ReportErr

'Set objXL = GetObject(, "Excel.Application")
Set objXL = New Excel.Application

'objXL.Visible = True

With objXL
Set objWkb = .Workbooks.Open("\\Server1\Report 1.xls")

Set db = CurrentDb

Set objSht = objWkb.Worksheets("Portfolio Summary")
objSht.Activate
.Range("USD").Value = varUSD

strSQL = "Select Name, Value, from Query1"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
rs.MoveLast
intTotal = rs.RecordCount
rs.MoveFirst

For x = 1 To intTotal
.Rows("10:10").Select
.Selection.Insert Shift:=xlDown
Next x

.Range("Range1").CopyFromRecordset rs

Set rs = Nothing

End With

objWkb.SaveAs (strFullPath)
objXL.Quit

Set objSht = Nothing
Set objWkb = Nothing
Set objXL = Nothing

Exit Sub

ReportErr:
'If Err.Number = 429 Then
' Set objXL = CreateObject("Excel.Application")
' Resume Next
'End If

objXL.Quit

Set objSht = Nothing
Set objWkb = Nothing
Set objXL = Nothing

End Sub

Can you show me all the lines of code so I can see the error in context?

Good grief! Could you get around this by saying that you are not
"re-installing" it, you are "re-registering" it to fix an error in the
existing installation?

I might try my luck with this - will let you know how it goes!

Thanks again, Ange.

[Ange T]

Hi again,

Unofrtunately neither of the suggestions worked =(( Dim'ing the
recordset as Object didn't change the error, and qualifying each of
the .Range lines didn't change the error thing.

So the problem is that when you get to the line:

..Range("Range1").CopyFromRecordset rs

I get the error #430 "Class does not support Automation or does not
support
expected interface".

Referring back to a previous post where I had this error, I wrote:

From this article:
http://support.microsoft.com/default...b;en-us;246335
it seems that such an error msg means I am passing an ADO recordset...
However my two declarations for the recordset being passed are: Dim db As DAO.Database
Dim rs As DAO.Recordset Have I just exchanged one problem for another??
Sorry if it feels I'm going in circles... I know I feel like I am!!

But thanks so much for your help, Ange.
"TC" <a@b.c.d> wrote in message news:<1065086096.896394@teuthos>... Hi Ange

Your previous posts have disappeared from my newsserver, & unfortunately I
can't remember what error you got on that line. (It is always good to repeat
the relevant information, just in case this happens.) But let's try some
ideas, anyhow.

First, I'm wondering whether the Excel CopyFromRecordset method would
require an Excel VBA recordset, not an Access VBA recordset. Try Dim'ing rs
"as object", not "as DAO.recordset", & see if that helps.

Second - and I think that this is probably it - your statement:

.Range("Range1").CopyFromRecordset rs

is referring to objXL - not to objWkb, or objSht. "Unqualified" dot
references do not reference the preceding "set" object - they reference the
preceing "with" statement's object, which in your case, is objXL; and I
doubt that the Excel object has a Range property or method?

HTH,
TC
Ange T <sm*************@yahoo.com> wrote in message
news:b7**************************@posting.google.c om...

Hi again TC,

Thanks for all your messages, and sorry for not responding quicker.
Here is the CopyFromRecordset line in contex...

Private Sub CreateSummary(strFullPath as String)
Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim x As Integer
Dim intTotal as Integer
Dim strSQL as String

On Error GoTo ReportErr

'Set objXL = GetObject(, "Excel.Application")
Set objXL = New Excel.Application

'objXL.Visible = True

With objXL
Set objWkb = .Workbooks.Open("\\Server1\Report 1.xls")

Set db = CurrentDb

Set objSht = objWkb.Worksheets("Portfolio Summary")
objSht.Activate
.Range("USD").Value = varUSD

strSQL = "Select Name, Value, from Query1"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
rs.MoveLast
intTotal = rs.RecordCount
rs.MoveFirst

For x = 1 To intTotal
.Rows("10:10").Select
.Selection.Insert Shift:=xlDown
Next x

.Range("Range1").CopyFromRecordset rs

Set rs = Nothing

End With

objWkb.SaveAs (strFullPath)
objXL.Quit

Set objSht = Nothing
Set objWkb = Nothing
Set objXL = Nothing

Exit Sub

ReportErr:
'If Err.Number = 429 Then
' Set objXL = CreateObject("Excel.Application")
' Resume Next
'End If

objXL.Quit

Set objSht = Nothing
Set objWkb = Nothing
Set objXL = Nothing

End Sub

Can you show me all the lines of code so I can see the error in context?

Good grief! Could you get around this by saying that you are not
"re-installing" it, you are "re-registering" it to fix an error in the
existing installation?

I might try my luck with this - will let you know how it goes!

Thanks again, Ange.

[TC]

Ange,

Post the modified code :-)

TC
Ange T <sm*************@yahoo.com> wrote in message
news:b7**************************@posting.google.c om...

Hi again,

Unofrtunately neither of the suggestions worked =(( Dim'ing the
recordset as Object didn't change the error, and qualifying each of
the .Range lines didn't change the error thing.

So the problem is that when you get to the line:

.Range("Range1").CopyFromRecordset rs

I get the error #430 "Class does not support Automation or does not
support
expected interface".

Referring back to a previous post where I had this error, I wrote:

From this article:
http://support.microsoft.com/default...b;en-us;246335
it seems that such an error msg means I am passing an ADO recordset...
However my two declarations for the recordset being passed are:

Dim db As DAO.Database
Dim rs As DAO.Recordset

Have I just exchanged one problem for another??

Sorry if it feels I'm going in circles... I know I feel like I am!!

But thanks so much for your help, Ange.
"TC" <a@b.c.d> wrote in message news:<1065086096.896394@teuthos>...

Hi Ange

Your previous posts have disappeared from my newsserver, & unfortunately I can't remember what error you got on that line. (It is always good to repeat the relevant information, just in case this happens.) But let's try some
ideas, anyhow.

First, I'm wondering whether the Excel CopyFromRecordset method would
require an Excel VBA recordset, not an Access VBA recordset. Try Dim'ing rs "as object", not "as DAO.recordset", & see if that helps.

Second - and I think that this is probably it - your statement:

.Range("Range1").CopyFromRecordset rs

is referring to objXL - not to objWkb, or objSht. "Unqualified" dot
references do not reference the preceding "set" object - they reference the preceing "with" statement's object, which in your case, is objXL; and I
doubt that the Excel object has a Range property or method?

HTH,
TC
Ange T <sm*************@yahoo.com> wrote in message
news:b7**************************@posting.google.c om...

Hi again TC,

Thanks for all your messages, and sorry for not responding quicker.
Here is the CopyFromRecordset line in contex...

Private Sub CreateSummary(strFullPath as String)
Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim x As Integer
Dim intTotal as Integer
Dim strSQL as String

On Error GoTo ReportErr

'Set objXL = GetObject(, "Excel.Application")
Set objXL = New Excel.Application

'objXL.Visible = True

With objXL
Set objWkb = .Workbooks.Open("\\Server1\Report 1.xls")

Set db = CurrentDb

Set objSht = objWkb.Worksheets("Portfolio Summary")
objSht.Activate
.Range("USD").Value = varUSD

strSQL = "Select Name, Value, from Query1"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
rs.MoveLast
intTotal = rs.RecordCount
rs.MoveFirst

For x = 1 To intTotal
.Rows("10:10").Select
.Selection.Insert Shift:=xlDown
Next x

.Range("Range1").CopyFromRecordset rs

Set rs = Nothing

End With

objWkb.SaveAs (strFullPath)
objXL.Quit

Set objSht = Nothing
Set objWkb = Nothing
Set objXL = Nothing

Exit Sub

ReportErr:
'If Err.Number = 429 Then
' Set objXL = CreateObject("Excel.Application")
' Resume Next
'End If

objXL.Quit

Set objSht = Nothing
Set objWkb = Nothing
Set objXL = Nothing

End Sub
> Can you show me all the lines of code so I can see the error in context?

> Good grief! Could you get around this by saying that you are not
> "re-installing" it, you are "re-registering" it to fix an error in the > existing installation?

I might try my luck with this - will let you know how it goes!

Thanks again, Ange.

[Ange T]

Sorry - I always forget to do that!!

Private Sub CreateSummary(strFullPath as String)
Dim objXL As Excel.Application
Dim objWkb As Excel.Workbook
Dim objSht As Excel.Worksheet
Dim db As DAO.Database
Dim rs As DAO.Recordset 'Tried it with Dim rs as Object but it made no
difference (still same error)
Dim x As Integer
Dim intTotal as Integer
Dim strSQL as String

On Error GoTo ReportErr

'Set objXL = GetObject(, "Excel.Application")
Set objXL = New Excel.Application

'objXL.Visible = True

With objXL
Set objWkb = .Workbooks.Open("\\Server1\Report 1.xls")

Set db = CurrentDb

Set objSht = objWkb.Worksheets("Portfolio Summary")
objSht.Activate
objSht.Range("USD").Value = varUSD

strSQL = "Select Name, Value, from Query1"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
rs.MoveLast
intTotal = rs.RecordCount
rs.MoveFirst

For x = 1 To intTotal
objSht.Rows("10:10").Select
.Selection.Insert Shift:=xlDown
Next x

objSht.Range("Range1").CopyFromRecordset rs

Set rs = Nothing

End With

objWkb.SaveAs (strFullPath)
objXL.Quit

Set objSht = Nothing
Set objWkb = Nothing
Set objXL = Nothing

Exit Sub

ReportErr:
'If Err.Number = 429 Then
' Set objXL = CreateObject("Excel.Application")
' Resume Next
'End If

objXL.Quit

Set objSht = Nothing
Set objWkb = Nothing
Set objXL = Nothing
End Sub

The error I get is at this line (above):
objSht.Range("Range1").CopyFromRecordset rs

and the error is:
#430 "Class does not support Automation or does not support expected
interface".

Thanks, Ange
"TC" <a@b.c.d> wrote in message news:<1065238709.67634@teuthos>...

Ange,

Post the modified code :-)

TC
Ange T <sm*************@yahoo.com> wrote in message
news:b7**************************@posting.google.c om...

Hi again,

Unofrtunately neither of the suggestions worked =(( Dim'ing the
recordset as Object didn't change the error, and qualifying each of
the .Range lines didn't change the error thing.

So the problem is that when you get to the line:

.Range("Range1").CopyFromRecordset rs

I get the error #430 "Class does not support Automation or does not
support
expected interface".

Referring back to a previous post where I had this error, I wrote:

From this article:
http://support.microsoft.com/default...b;en-us;246335
it seems that such an error msg means I am passing an ADO recordset...
However my two declarations for the recordset being passed are: Dim db As DAO.Database
Dim rs As DAO.Recordset Have I just exchanged one problem for another??

Sorry if it feels I'm going in circles... I know I feel like I am!!

But thanks so much for your help, Ange.
"TC" <a@b.c.d> wrote in message news:<1065086096.896394@teuthos>...

Hi Ange

Your previous posts have disappeared from my newsserver, & unfortunately I can't remember what error you got on that line. (It is always good to repeat the relevant information, just in case this happens.) But let's try some
ideas, anyhow.

First, I'm wondering whether the Excel CopyFromRecordset method would
require an Excel VBA recordset, not an Access VBA recordset. Try Dim'ing rs "as object", not "as DAO.recordset", & see if that helps.

Second - and I think that this is probably it - your statement:

> .Range("Range1").CopyFromRecordset rs

is referring to objXL - not to objWkb, or objSht. "Unqualified" dot
references do not reference the preceding "set" object - they reference the preceing "with" statement's object, which in your case, is objXL; and I
doubt that the Excel object has a Range property or method?

HTH,
TC