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???

 

[kphu]

Hi MAVALON,

Just curious to know what version of ACCESS your using.

 

[mavalon]

I'm using Access 2002, but I've been trying out the codes on 2000 as well, which doesn't work either.

 

[lespaul]

I think that if you are using an embedded worksheet that when you set xlsSheet, you are setting it to the sheet, not the workbook.

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

So when you are doing the line in red (I'm assuming this is where your code crashes), you are actually calling:
Workbook.Worksheets(1).Worksheets(1).Cells(1,1) - (just my guess).

Why don't you try xlsSheet.Cells(1,1) = "test"

HTH




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]

I've tried that. Unfortunately, I received the same runtime errors. Also, when I highlight "Cells" (in xlsSheet.Cells(1,1) = "test&quot, and hover over it with my mouse pointer, I get a little message saying "Object variable or With block variable not set".

 

[lespaul]

What line is highlighted as the error? Is it the = "test" line?


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]

Yes, that's the line that's highlighted. I've been looking at that highlighted line all day, and it's driving me insane! Any idea what I'm doing wrong? (I only started teaching myself VBA a week ago, so I'm pretty much clueless)

 

[lespaul]

So if you hover over xlsSheet do you get a reference? Is it recognizing the spreadsheet? If so, maybe try:


Set xlsSheet = Me!diagTemplate
row = 1
With xlsSheet do
.Cells(1, 1) = "test"
end with



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]

When I hover over xlsSheet, I get nothing. I tried the code above anyway.

The "do" was producing errors in Visual Basic. It said I needed "until" or "while". I removed the "do" and tried to run the code and the error was "Method 'Cells' of object '_Globals' failed".

 

[lespaul]

Well, if you are hovering over the xlsSheet and it's not stating there's anything, then the assignment isn't correct. I would suggest looking in the Access - VBA forum and see if you can find any information there. I'm fresh out of ideas!

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]

I appreciate your suggestions. Thanks.

 

[kphu]

mavalon,

if your referencing an object. you need to use the word set.

try this instead

set test = xlsSheet.Worksheets(1).Cells(1, 1)

 

[lespaul]

except the worksheet cells are blank and he's trying to fill it in!

Leslie
landrews@metrocourt.state.nm.us

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

 

[kphu]

If he's trying to fill in the value then use this

xlsSheet.Worksheets(1).Cells(1, 1).formula = "Test"

 

[mavalon]

I'm still getting "Object does not support this property or method".

It's almost like it doesn't recognize the object as a spreadsheet.

Am I following the right procedure in adding the object? I tried both:

1. Insert -> Object -> Create New --> Microsoft Excel Worksheet
2. Insert -> Object -> Create from file --> (and I selected the file) [--> sometimes I tried selecting "Link"]

Is there something else I need to do????

 

[kphu]

I asked you earlier what version of access you were using and you responded with 2002.

I'm suspecting that your use of:

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim qdf As DAO.QueryDef


Maybe the problem.
I am aware with access 2000 that DAO is limited and rather ADO is suggested in using. I'm assuming that this applies to 2002.

Go to your access help file and type in
DAO Object library compatibility
in the search.

Here's a sample of what the help file said.

Examples of Converting DAO Code


Microsoft Access includes version 4.0 of the Microsoft Jet database engine and the DAO 3.6 object library. All previous version of DAO are no longer supported in Microsoft Access 2000. The following examples demonstrate how to convert code constructs in Microsoft Access databases created with earlier versions of the Jet database engine and DAO object libraries

Note This information is provided for reference only. Versions of DAO code prior to 3.6 are no longer supported in Access 2000. It is suggested that you use the ADO methods of accessing data in your current database for all new applications and future version compatibility.

Setting a Reference to the current database

Version Example
1.x Dim dbs As DatabaseSet dbs = CurrentDb
2.x Dim dbs As DatabaseSet dbs = DBEngine.Workspaces(0).Databases(0)
or Dim dbs As DatabaseSet dbs = DBEngine(0)(0)
7.0 or Later Dim dbs As DAO.DatabaseSet dbs = CurrentDb
9.0
(Recommended)
Dim cnn As ADODB.ConnectionSet cnn = CurrentProject.Connection


Note In Microsoft Access, the current database is the default database in the Databases collection, so you can still use the DBEngine(0)(0) syntax to return a Database object variable pointing to the current database. However, when using DAO the CurrentDb method is the preferred means of returning this object variable, because it enables you to create more than one Database object variable that points to the current database.

 

[pbrodsky]

You need to reference the name of the embedded ole object. This is from the Access97 Help for "Object Property"

"You can use the Object property in Visual Basic to return a reference to the Automation object that is associated with a linked or embedded OLE object in a control. By using this reference, you can access the properties or invoke the methods of the OLE object.

Setting

The Object property returns a reference to an Automation object. You can use the Set statement to assign this Automation object to an object variable. The type of object reference returned depends on which application created the OLE object.
The Object property setting is read-only in all views.

Remarks

When you embed or link an OLE object in a Microsoft Access form, you can set properties that determine the type of object and the behavior of the container control. However, you can't directly set or read the OLE object's properties or apply its methods, as you can when performing Automation. The Object property returns a reference to an Automation object that represents the linked or embedded OLE object. By using this reference, you can change the OLE object by setting or reading its properties or applying its methods. For example, Microsoft Excel is an ActiveX component that supports Automation. If you've embedded a Microsoft Excel worksheet in a Microsoft Access form, you can use the Object property to set a reference to the Worksheet object associated with that worksheet. You can then use any of the properties and methods of the Worksheet object.

For information on which properties and methods an Automation object supports, see the documentation for the application that was used to create the OLE object.

Example:

The following example uses the Object property of an unbound object frame named OLE1. Customer name and address information is inserted in an embedded Microsoft Word document formatted as a form letter with placeholders for the name and address information and boilerplate text in the body of the letter. The procedure replaces the placeholder information for each record and prints the form letter. It doesn't save copies of the printed form letter.

Sub PrintFormLetter_Click()
Dim objWord As Object
Dim strCustomer As String, strAddress As String
Dim strCity As String, strRegion As String

' Assign object property of control to variable.
Set objWord = Me!OLE1.Object.Application.Wordbasic
' Assign customer address to variables.
strCustomer = Me!CompanyName
strAddress = Me!Address
strCity = Me!City & ", "
If Not IsNull(Me!Region) Then
strRegion = Me!Region
Else
strRegion = Me!Country

End If
' Activate ActiveX control.
Me!OLE1.Action = acOLEActivate
With objWord
.StartOfDocument
' Go to first placeholder.
.LineDown 2
' Highlight placeholder text.
.EndOfLine 1
' Insert customer name.
.Insert strCustomer
' Go to next placeholder.
.LineDown
.StartOfLine
' Highlight placeholder text.
.EndOfLine 1
' Insert address.
.Insert strAddress
' Go to last placeholder.
.LineDown

.StartOfLine
' Highlight placeholder text.
.EndOfLine 1
' Insert City and Region.
.Insert strCity & strRegion
.FilePrint
.FileClose
End With
Set objWord = Nothing
End Sub
"

 

[mavalon]

Thanks pbrodsky. It seems like that's on the right track. Unfortunately, I'm just a newbie, and I'm not very familiar with VBA syntax. Also, I'm using Access 2002. At this point I have created a sample database with 1 table, 1 query, and 1 report with an embedded excel spreadsheet. I'm using the following code (based on the help file you included.) I get the error: "Microsoft Access can't fine the FIELD 'OLE1' referred to in your expression."

Private Sub Report_Open(Cancel As Integer)
Dim objExcel As Object

Set objExcel = Me!OLE1.Object.Application.Test
Test.Worksheet(1).Cells(1, 1) = "test"
End Sub

What now?

 

[mavalon]

Ooops..I misread the post. I should have written:

Private Sub Report_Open(Cancel As Integer)
Dim objExcel As Object

Set objExcel = Me!Test.Object.Application.[EXCEL?]
Test.Worksheet(1).Cells(1, 1) = "test"
End Sub

What goes where I wrote "[EXCEL?]" ??? I.e., how do i reference the application?

 

[pbrodsky]

I don't have the exact syntax - I hope someone else can help, but you need to get the correct name of the control on your form by clicking on it in design mode and looking at its properties (when I tried the name came out as "OLEUnbound0&quot. I still haven't found the correct way to refer to the worksheet -- it looks as though it should be something like

.object.excel.sheet

but that doesn't work. I'll keep trying unless somene else knows.