Activating Excel sheet in OLE container

[pcerasi]

Hello

I have created an OLE container on a form that links to a range in an Excel worksheet. Running the project, I see the correct range in the container, but I would like to be able to edit the contents with the Excel window remaining inside the container, as happens when you double-click an Excel graph pasted into Word, for example. At the moment, when I double click (or any other way you choose to open the container for object editing during run time), a seperate Excel window opens with the focus not especially on the intended range of cells.

Private Sub LoadExcelRange()
If OLE_ExcelRange.SourceDoc = "" Then
OLE_ExcelRange.CreateLink "ExcelWorkbookPath.xls!ExcelRange"
End If
OLE_ExcelRange.AutoActivate = 2 'or 1 depending on opening mode desired
End Sub

I thought of tricking the Excel window into appearing in the right place by adding a Click procedure along the lines of:

Private Sub OLE_ExcelRange_Click()
Dim xlExcelFile As Excel.Workbook
Set xlExcelFile = OLE_ExcelRange.object
With xlExcelFile
.Windows(1).Activate
.Application.Visible = True
With .Application.ActiveWindow
.Left = OLE_ExcelRange.Left
.Top = OLE_ExcelRange.Top
.Width = OLE_ExcelRange.Width
.Height = OLE_ExcelRange.Height
End With
End With
End Sub

but that doesn't work and anyway there should be an easier and more elegant way of solving the problem.

Thanks for any input

[freefly21]

I'm wondering the same thing. It sure is annoying having that second window open...

[Molje]

When you want to see the file in the OLE control itself, you have to embed the file in this OLE-control...

here's a way to do it: (do not forget to add the Microsoft Excel 9.0 Object Reference to your project !)


dim sFileName as string
sFileName = "c:\test.cls"
Dim xls As Excel.Workbook
Set xls = Workbooks.Add
xls.SaveAs sFileName
xls.Close (True)

OLE1.CreateEmbed (sFileName)

'to edit the file in the OLE1 object :
OLE1.doverb(1)

Hope this helps you?

[pcerasi]

Thanks for the reply.
Using Embed, however does not allow you to see updates to your Excel chart, as this can be done with the Link option.
The way around that that works but is not very pretty looking, is to automatically cause a double-click or edit of the OLE window when changes are made to the underlying Excel code:

OLE_ExcelChart.SetFocus
SendKeys "{ENTER}", 1
With xlWorkbook
.Windows(1).Activate
With .Application
.Visible = False
End With
End With

This causes a rapid flashing of Excel windows into the poor user's face but leaves him with an updated embedded object without having to worry about closing the Excel window.

But there must be a more elegant way of doing it, I'm sure

pcerasi.