Solved: edit Excel ole

[salonas]

Hi,
I am new to posting so please forgive any errors. I have an Access 2003 report with an embedded unbound Excel OLE. I need to edit various cell contents on report open. I have not been able to find any object properties or methods to achieve this. Thanks in advance for any response.-SAL

[stanl]

Hi,
I have an Access 2003 report with an embedded unbound Excel OLE.

What is the Object - a spreadsheet, bitmap, word document. If it has been inserted as a binary object, you can use an ADO Stream to access it.

Stan

[salonas]

What is the Object
Stan

It is a spreadsheet (class Excel.Sheet.8). I have a similar object on a form that I can edit. The objects have been created in design view and are emedded. Below are code snippets, the form code works however the report code (ctl.Action = acOLEActivate) faults with a run time error 2771- object frame doesn't contain an ole.Thanks again for your time. -SAL

Private Sub Report_Open(Cancel As Integer)
Dim ctl As Control, wks As Excel.Worksheet
    Set ctl = Me.OLEUnbound128
        ctl.Action = acOLEActivate
        Set wks = ctl.Object.Application.Workbooks(1).Worksheets(1)
'            wks.Cells(22, 4).Value = ""
        Set wks = Nothing
        ctl.Save
        ctl.Close
    Set ctl = Nothing
End Sub
 
Private Sub Form_Open(Cancel As Integer)
Dim ctl As Control, wks As Excel.Worksheet
    Set ctl = Me.OLEUnbound85
        ctl.Action = acOLEActivate
        Set wks = ctl.Object.Application.Workbooks(1).Worksheets(1)
            wks.Cells(22, 4).Value = ""
        Set wks = Nothing
        ctl.Save
        ctl.Close
    Set ctl = Nothing
End Sub

[Carl A]

Try setting the OLE verb property to ctl.Verb = acOLEVerbShow

Private Sub Report_Open(Cancel As Integer)
Dim ctl As Control, wks As Excel.Worksheet
    Set ctl = Me.OLEUnbound128
    ctl.Verb = acOLEVerbShow
        ctl.Action = acOLEActivate
        Set wks = ctl.Object.Application.Workbooks(1).Worksheets(1)
'            wks.Cells(22, 4).Value = ""
        Set wks = Nothing
        ctl.Save
        ctl.Close
    Set ctl = Nothing
End Sub
 
Private Sub Form_Open(Cancel As Integer)
Dim ctl As Control, wks As Excel.Worksheet
    Set ctl = Me.OLEUnbound85
    ctl.Verb = acOLEVerbShow
        ctl.Action = acOLEActivate
        Set wks = ctl.Object.Application.Workbooks(1).Worksheets(1)
            wks.Cells(22, 4).Value = ""
        Set wks = Nothing
        ctl.Save
        ctl.Close
    Set ctl = Nothing
End Sub

[salonas]

Try setting the OLE verb property to ctl.Verb = acOLEVerbShow

Same error on report open.

[salonas]

I moved the code from the report open event to page event and it executes. But the edits made to the ole object while connected to the ole server do not save when the server terminates. This does not happen with the form model.

[salonas]

FAI-Moving the code to the report detail format event appears to achieve the desired result except that the ole object is not saved and will return to the last saved design view state when report is closed. The REM code is my feeble attempt to work around that. TA-SAL

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim ctl As Control, wkb As Workbook, wks As Excel.Worksheet
Set ctl = Me.OLEUnbound85
ctl.Verb = acOLEVerbHide
ctl.Action = acOLEActivate
Set wkb = ctl.Object.Application.Workbooks("Worksheet in " & Me.Caption)
Set wks = wkb.Worksheets(1)
wks.Cells(22, 4).Value = "test7"
'wkb.Save
'ctl.Requery
'ctl.Save
'Me.Application.DoCmd.Save acReport, Me.Name
Set wkb = Nothing
ctl.Close
Set ctl = Nothing
End Sub