Referencing cells of an embedded excel worksheet in Access Report 2

[mavalon]

I'm trying to reference (and populate) the cells of an embedded excel worksheet in my Access report. I'm getting a runtime error teling me that "the object doesn't suppor this property or method".

This is the code:

Private Sub Report_Open(Cancel As Integer)
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim xlsSheet As Object
Dim row As Long

'Specify Parameters

Set dbs = CurrentDb()
Set qdf = dbs.QueryDefs("qry_CTQ_TEST&quot
qdf("vid&quot = 331

Set rst = qdf.OpenRecordset()

Set xlsSheet = Me!diagTemplate
row = 1
xlsSheet.Worksheets(1).Cells(1, 1) = "test"

' Obviously I'm going to populate cells with recordset
' But I'm stumpped on this less complicated step

rst.Close
Set rst = Nothing
Set qdf = Nothing
Set dbs = Nothing
Set xlsSheet = Nothing
End Sub

Any ideas what i'm doing wrong???

 

[pbrodsky]

Finally - after a lot of trial, error, and searching the internet, this worked:

Dim strCustomer As String, strAddress As String
Dim strCity As String, strRegion As String
Dim ss As Object

Set ss = Me![OLEUnbound0].Object
strCustomer = "abc"
ss.ActiveSheet.cells(2, 2) = strCustomer
Set ss = Nothing

where the name of the embedded OLE object on the form/report was OLEUnbound0

 

[lespaul]

You're the man!!! Stars for you!

Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!

 

[mavalon]

Why does this work for you and Leslie, but not for me??? What am i doing wrong? I still get that miserable error message: "The bound or unbound object frame you tried to edit doesn't contain an OLE object"

(I'm about to throw my PC out the window!)

 

[mavalon]

Can one of you guys send me a sample database where the embedded excel worksheet receives data as described by pbrodsky above? mavalon@dipidy.com

I'm desperate (as I'm sure you can tell).