Class does not support automation or does not support expected interfa

[RivetHed]

Hi all,

The error message above is getting thrown up in the following code:

Set rs2 = db.OpenRecordset("TblDefMailshot&quot

Set XL = CreateObject("Excel.Application&quot
Set wb = XL.Workbooks.Add
Set ws = wb.Worksheets.Add

For i = 0 To rs2.Fields.Count - 1
ws.Cells(1, i + 1) = rs2.Fields(i).Name
Next i

ws.Range("A2&quot.CopyFromRecordset rs2

Set rs2 = Nothing

It's the copyfromrecordset line that's causing the problem but it's only happening on one computer, it works fine on others. This has got to be with a client tomorrow and i can't let this same error appear on their system so can anyone please shed some light on this.

Thanks a lot

 

[rdodge]

Not sure if this would help, but set the bookmarker on the recordset to the first record via the MoveFirst Method:

rs2.MoveFirst

Then do your CopyFromRecordset Method.

Help file on the CopyFromRecordset Method states as follows:

Copying begins at the current row of the Recordset object. After copying is completed, the EOF property of the Recordset object is True.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[RivetHed]

Afraid that doesn't do it, I'm pretty sure that the recordset would already be at the beginning anyway since it's just been set, tried movefirst anyway but makes no difference

 

[rdodge]

I'm not sure which version of Office you are using, but you may also want to double check that you have the appropriate "Microsoft Excel x.0 Object Library" checkmarked within your application, of which there should only be one anyhow on the system.

If you are using Excel 97, be sure you are referencing to DAO 3.5. Excel 2000 and later uses DAO 3.6.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[RivetHed]

Yup everything up to date DAO 3.6 Xl 9.0 obj libraries referenced.
It's only one machine it doesn't work on and the code and reference libs are all exactly the same on all machines

 

[rdodge]

Ohter than maybe an article that I located, but don't think it would be much help, which is:

http://support.microsoft.com/default.aspx?scid=kb;en-us;243394&Product=xlw2K

I'm basically out of ideas other than to double check on that user's system to be sure that the "Microsoft DAO 3.6 Object Library" checkbox is checkmarked.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[RivetHed]

I triple checked everything along those lines straight off, I've seen other articles on this problem saying just to install the most recent version of MDAC which has already been done so I'm now completely stuck.
Anyone have any ideas?

 

[nat1967]

Hi RivetHead,

I have beat myself silly with this same error message.

After MONTHS (literally), i discovered that the problem resided in my trying to move a memo field over to an Excel cell. Excel cells apparently can only handle 255 characters. My memo field ran over that amount in some instances thereby creating this error. If the user had less than 255 in all their memo fields, the problem never occured. If a particular user like to put a lot of notes, then BLAMO!! application errored.....

Needless to say, I am still recovering from the beating.

Dont know if that is your problem but I thought I'd give my two cents since I have been in your position before.

HTH

Have A Great Day!!!,

Nathan
Software Testing Lead

http://www.skylineprints.com

 

[rdodge]

What Nathan has mentioned about the 255 (2^8-1) characters being the limit, that is true for Excel 97, but for Excel 2000 and later, it's limit is 32,767 (2^16-1). Oh, speaking of limitations, be sure that your recordset doesn't contain more than the number of rows that's available to paste into Excel (65536 - RowNum of the upper left cell of Range + 1)

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[nat1967]

Hi rdodge,

THANK YOU!!! there is the last piece to my puzzle. i could never figure out what the exact limitation was in Excel.

Thank you!!!

Have A Great Day!!!,

Nathan
Software Testing Lead

http://www.skylineprints.com

 

[rdodge]

If in doubt of limitations, the resource I normally turn to is the Specification help file.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[RivetHed]

Thanks for the pointers guys, all of the stuff getting exported to XL is quite small though and as I said the error only rears it's ugly head on one machine so it's not an integral fault. Seems to work on the clients system so far though so I guess I'm just going to have to hope for the best.

 

[nat1967]

RivetHed,

i have seen this on one of my users. they reminded me today about it. to solve his machines issues, we had to completely re-install Office 2000. whatever it was, got fixed when we did that.



Have A Great Day!!!,

Nathan
Software Testing Lead

http://www.skylineprints.com

 

[rdodge]

An additional note about reinstalling, be sure to uninstall the Office program completely, then you may have to end process on the one remaining file, which I think it's OSA.exe (This is not on Office XP or 2003), but not completely sure. Anyhow, then you delete the entire Microsoft Office Folder (unless you have MS Project installed, though wouldn't hurt to reinstall that at the same time should you have that too). It's in this deletion process that you will find the one remaining file that you can't delete cause it's running in the background, which shows up in the Task Manager under Processes tab.

Reboot the system

Reinstall Office like normal, and be sure you have all of the necessary options that your user and anyone else that may need to use or check something on the system for.

Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000

 

[RivetHed]

OK, I guess I'l try that if this error occurs anywhere client side it hasn't yet though so fingers crossed.
Looking back through this forum and others this doesn't seem to be an uncommon problem, I'm surprised there isn't a proper fix available for it.
Thanks for the help guys.