unbound excel control

[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

 

[John Nurick]

Hi Anil,

I always thought that the Excel .Cells method required numeric values
for Row and Column.

 

[anil]

Dear John
So can you please suggest what should i do as this code was in
Microsoft office developer's Handbook (old book).
thanks
anil

 

[John Nurick]

What happens if you replace
"A"
with
1
in X1.Cells(pointer, "A")