Daryl
Nov 27th, 2000, 01:59 PM
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.
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:
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
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.
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:
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