Excel sheet (OLE object) sometimes open on the form sometimes in E

[Dan]

Hello there,
I have an Excel sheet (OLE object in the table) on a form , I have setup
the Property in "Auto Activate" as Double-Click , so when I double click the
object the sheet open.

But sometime the object appears on the form as an Excel Icon - then when I
double click it open in Excel - that is what I want.
But sometime it appears as a table of data and when I double click it open
on the form - I want to avoid that and instead always have the sheet open in
excel.

Any idea?
Many many thanks,
Dan

 

[Charles Wang [MSFT]]

Hi Dan,
By default, you can directly double click the OLE Object field to open it
in data sheet view. In a Form view, for an Excel file, by default it is
displayed as an embeded table.
In Access 2007, to view it in Excel, you need to right click the field,
select "Worksheet Object"->"Open". If you want to open the file by double
clicking the field, you need to add some VBA code as following:
1. Switch to Design View and open the Visual Basic Editor;
2. Create a module and paste the following constants
Global Const VERB_OPEN = -2
Global Const OLE_ACTIVATE = 7
Global Const OLE_EMBEDDED = 1
Global Const OLE_CREATE_EMBED = 0
3. Add the following code to the Dbl_Click event of the corresponding
control:
IMG.Verb = VERB_OPEN
IMG.Action = OLE_ACTIVATE

For example:
Private Sub XlsFile_DblClick(Cancel As Integer)
IMG.Verb = VERB_OPEN
IMG.Action = OLE_ACTIVATE
End Sub

Then when you double click the field, the Excel workbook will show.Hope
this helps.

Best regards,
Charles Wang