OLE control and Excel

Hi,

I'm using an OLE control to link with Excel and display a worksheet. I have 2 problems. The first is controlling how much of the worksheet is displayed within the control.

I have the following code to initialize the control and a worksheet object to add data.

Code:

---

Dim xlBook as Excel.workbook
Dim xlSheet As Excel.Worksheet
Dim origNumSheets

    oleReport.CreateLink "data.xls", "R1C1:R5C5"
    Set xlBook = oleReport.object
    origNumSheets = xlBook.Worksheets.count
   
    For i = 1 To origNumSheets
        Set xlSheet = xlBook.Worksheets(i)
        cmbSheets.AddItem xlSheet.Name
    Next i
     
    Set xlSheet = Nothing

---

This works fine for setting up the control except that there seems to be no consistency with how much of the sheet is displayed. I'm not sure if "R1C1:R5C5" is the right syntax - although there is an example similar to that in MSDN.

The second problem occurs when opening the sheet for editing in Excel. There is no "Close and return to .." option in the "File" menu. Although there is an "Update" option in the "File" menu. I'm not sure if this is correct. The real problem occurs when I close the Excel edit window. This seems to terminate the OLE control link to the worksheet. I thought this would only close and save the "editing" of the worksheet.

To try and account for this, I have:

Code:

---

Private Sub oleReport_Updated(Code As Integer)
'    MsgBox "Update event: " & str(Code)
   
    If Code = vbOLEClosed Then
        oleReport.CreateLink txtExcelFile.Text, "R1C1:R10C10"
'        oleReport.CreateEmbed txtExcelFile.Text
        Set xlBook = oleReport.object
        LoadReportSheets  ' updates sheets comboBox
    ElseIf Code = vbOLESaved Then
        oleReport.CreateLink txtExcelFile.Text, "R1C1:R10C10"
'        oleReport.CreateEmbed txtExcelFile.Text
        LoadReportSheets  ' updates sheets comboBox
    End If
   
End Sub

---

I am resetting xlBook because the OLE container gets reset. When exiting Excel, both the Save and Close event occur. On a slow computer, this worked as both events were handled (Save first, then Close) before LoadReportSheets was executed (expects xlBook to be set). On a fast computer, xlBook gets accessed through "LoadReportSheets" before the "Close" event is handled.

After crashing with a "Couldn't read from address..." error, when restarting the program, I get a:

Run-time error '31032'
System Error &H800706A. The RPC server is unavailable.

I tried adding the reset of the OLE control to the "Save" event without success. I also tried using .CreateEmbed without any luck.

Any help is greatly appreciated.

Daryl

Hello Daryl,

I am facing exactly the same problem. If u get the answer from somewhere, would u be kind enough to mail it to me at [email protected] your help would be much appreciated.