By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,057 Members | 1,407 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,057 IT Pros & Developers. It's quick & easy.

problem with CopyFromRecordset Access>Excel

P: 2
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
Share this Question
Share on Google+
1 Reply


P: 2
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.