Can Excel show OLE object created in DLL of VB6 ?

[chris]

In VB6 I created a DLL with a form including an OLE object with Microsoft
excel chart.

When I call this DLL in Excel for showing form, OLE object is blank. It has
data, title etc, I can retrieve their values but cannot see them in OLE
object.

When I call same DLL in Power Point for showing form, OLE object works ok.

Is there any solution for Excel to see chart of OLE object ?

Thanks a lot in advance!

 

[Joel]

The macro language in excel is slightly different in excel from other Office
products. You may need to set some References in the excel VBA menu Tools -
References. I would compare the references that arre enabled in Power Point
with the ones that are enabled in Excel.

 

[chris]

But the commands and references are included in DLL of VB6.. In Excel and
Power Point I just call form for showing OLE object..

 

[Joel]

Thre is an OLE Automation library referenced in both my Power Point and Excel
VBA. I'm not suure if that is included in the DLL. The DLL would only
included not standard items. OLE automation may be consider a standard
library in VB6 but not standard in Excel and Power Point.

 

[chris]

Unfortunately OLE Automation library is included in DLL, in Excel workbook &
Power Point... In Excel all commands work without any problem, it's just that
OLE object does not show chart...

it seems there is a conflict of OLE object including excel.chart and DLL
being called in Excel...

 

[Peter T]

How did you create the Excel Chart as an OLE on a VB6 form

Regards,
Peter T

 

[chris]

I created it with 2 ways. With insertable object of Excel chart and with OLE
Container Control. The messages below are for OLE Container Control.

DLL with insertable object, called in Excel needs user to double-click on
chart object to see chart..
DLL with OLE Container Control, called in Excel is blank, while it has
values..

DLL with insertable object, called in Power Point gives error.
DLL with OLE Container Control, called in Power Point works ok.

 

[chris]

DLL with insertable object and with OLE Container Control, called in Power
Point work ok. My mistake..

Problem is in Excel..

The reason I am trying it from DLL is that I have 2 add-ins, one for Excel &
one for Power point and I am trying to remove duplicate programming code.
Till now I have same code in both add-ins for chart preview. ex. in Excel, I
have a hidden workbook with a chart, from which I export picture of chart
and I assign it to a picture so user has preview of chart before is created.

 

[Peter T]

If I follow, in your form load event you are doing something like

Set cht = Me.OLE1.object.Charts(1)

where 'object' is an Excel chart object in the OLE container

How is the VB dll compiled, eg ActiveX or something else

How have you installed or run the dll in Excel

When and where from is the series data sourced.

Regards,
Peter T

 

[chris]

Yes, for OLE Container Control I use

Set chrt = ole1.object.Charts(1)
Set exl_WKS = ole1.object.Worksheets(1)

and for insertable object of Excel chart I use

Set chrt = Chart1.object.Charts(1)
Set exl_WKS = Chart1.object.Worksheets(1)

DLL is compiled as ActiveX DLL.

In form load I am assigning some test data for both charts in the worksheet
object retrieved (see above).

exl_WKS.Cells(1, 2).Value = "a"
exl_WKS.Cells(1, 3).Value = "b"
exl_WKS.Cells(2, 2).Value = 500

exl_WKS.Names.Add Name:="Chart_Range", RefersToR1C1:="=" & "R" & "1" & "C"
& "1:" & "R" & "2" & "C" & "3"

exl_chrt.SetSourceData Source:=exl_WKS.Range("Chart_Range"),
PlotBy:=xlRows

 

[Peter T]

I don't follow this bit

and for insertable object of Excel chart I use>
Set chrt = Chart1.object.Charts(1)
Set exl_WKS = Chart1.object.Worksheets(1)

Where is that code, in the dll or in Excel VBA
'Chart1' looks like it is the codename of an Excel chart-sheet, is that in a
normal Excel instance or in the OLE

I still don't follow how the dll form is called and shown from Excel. IOW
how is the dll installed or called from within Excel, I get the impression
it's as an OLE object on a chart sheet - interesting !

Regards,
Peter T

 

[chris]

The test includes 2 types of objects. One is the OLE Container Control and
the other is an object I added from VB6 (PROJECT/COMPONENTS/Insertable
Objects/Microsoft Excel Chart) and code below is used

Set chrt = Chart1.object.Charts(1)
Set exl_WKS = Chart1.object.Worksheets(1)

All code is in DLL. In Excel & Power Point VBA, I reference the DLL and just
calling form from DLL.

 

[Peter T]

OK I get it now, you have one chart in an OLE and the other directly on the
form. I tried that and like you the both charts do not appear when called
from Excel but they do in PP and Outlook. I couldn't get it to work in
Word - "cannot create Active X" error.

However, in Excel if I click the chart that's directly on the form then it
appears, though if I click the chart that's in the OLE then Excel crashes!

While testing I notice my toolbar settings have changed next time I open
Excel, in particular the formula bar is not visible.

I guess Excel as an OLE is not designed to run from within itself. I don't
have a solution

Regards,
Peter T

 

[chris]

Yes, that's the effects I have also. I tried it in different office versions,
but is the same.

Thanks a lot Peter for your time and support!