471,089 Members | 1,285 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,089 software developers and data experts.

problem with CopyFromRecordset Access>Excel

Apologies in advance for this newbie question!

I have an Access form with an unbound embedded Excel chart on it(Called, OLEUnbound39) (Done on Access form by Insert > Object > Microsoft excel chart)
I would like to place some records in to the underliing sheet and display the chart of this data using VBA.
I use CopyFromRecordset. The code runs and the records are copied to the sheet, but apparently after this copy and before the next line in the code I get the following Error:

Run-time error ' -2147417851 (80010105)':
Method 'CopyFromRecourdset' of object 'Range' failed

This also happens if I change the recordset in a table recordset or if i copy not all the record or fields from the recordset.
The fields that are coupied are nothing special really (number autonumber, date and text fields)

Has anybody got any idea why i get the error and what I can do about it.
Both access and Excel on PC are 2003

Thanks

Code:

Private Sub Command40_Click()
Dim myWorkBook As Object
Dim myWorkSheet As Object
Dim myChart As Object
Dim db As DAO.Database
Dim rst As DAO.Recordset

Dim frm As Form

Set db = CurrentDb

Set myWorkBook = Me.OLEUnbound39.Object
Set myChart = myWorkBook.Sheets("chart1")
Set myWorkSheet = myWorkBook.Sheets(2)

Set rst = Me.Weight.Form.RecordsetClone
rst.MoveFirst
myWorkSheet.Activate
'myWorkSheet.Range("A20").CopyFromRecordset rst, rst.RecordCount - 1, rst.Fields.Count - 1
myWorkSheet.Cells(2, 1).CopyFromRecordset rst 'THIS LINE GIVES THE ERROR
rst.Close
myChart.SetSourceData Source:=myWorkSheet.Range("A1:B6"), PlotBy:=xlRows

End Sub
Dec 9 '07 #1
1 3221
So, I discovered that if I open excel manually from the start menu and minimalize it then returning to the access window and click the command button I do not get the error.

But, strangely, when i do get the error (so without the excel "trick") and click "end" on the error message, then open excel like discribed above and again click the button I do get the error.

So once I get the error access seems to "remember" this fact.

If I open excel with VBA in the code it does not prevent the error.

Still puzzelled!
Anybody any idea ?
Thanks
Dec 10 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Bryan Harrington | last post: by
2 posts views Thread by ExDelphi | last post: by
3 posts views Thread by Gerry Blais | last post: by
10 posts views Thread by Mark Day | last post: by
2 posts views Thread by Japi | last post: by
1 post views Thread by setterst | last post: by
3 posts views Thread by kenduron | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.