transfer subform data into unbound excel sheet

A

anil

hi all
I want to transfer data from subform(in datasheet view ) in to excel
sheet (as unbound control).Is there any easy way to do it.
My subform is based on query and the data in subform is filtered data
using combo box in form.i.e. from combobox I select a particular
'Location' and I get data in subform for that particular Location.
Now I have excel chart embeded as unbound object frame in the form.I
want to transfer the subform data into that chart( or sheet ) so that I
can analyse the data.
I have tried one code as
Private Sub Form_Current()
Dim R As DAO.Recordset, X1 As Object ,pointer as integer
Set X1 = CreateObject("Excel.sheet.5")
Set R = Me.ExcelTestsubform.Form.RecordsetClone
pointer = 1
R.MoveFirst
Do Until R.EOF
X1.Cells(pointer, "A").Value = R.Fields("Location")
R.MoveNext
pointer = pointer + 1
Loop
X1.Cells(pointer, "A").Formula = "=Geomean(A1:A" & pointer - 1 &
")"
Me.Geomean = X1.Cells(pointer, "A").Value
End Sub

But it keeps giving me error at
"X1.Cells(pointer, "A").Value = R.Fields("Location")"

can some help me if If I can actually do it and if the module used is
in right direction.
Thanks
anil
 
Top