OWC10 issues

[theta]

Having some issues with OWC10 (or lack of it).

I am using Office 2010, but when I open a project originally written in 2003 it will not run correctly unless I install OWC10.

Windows 7 keeps removing OWC10 as it has security issues, and I already have OWC12 installed on the machine. There is no early binding in the code to specifically request OWC10.dll so I cannot understand why I get an "Automation Error" when launching the project.

Summary I would like to solve 2 points :

1. Use the latest OWC dll, not specifically OWC10 (although it is not bound in the VBA at present).

2. Find which elements have been used in the project that are reliant on OWC as I will try to remove them in the next development lifecycle?

Any help appreciated...

[Aflatoon]

most likely spreadsheet or chart controls on userforms.

[theta]

Thanks - there is a great deal of form based charting in this project.

I have checked the VBA and there is no early bound reference to OWC10, so I am guessing whenever Windows 7 removes it (as versions 11 and 12 are safer) the references disappear.

If i was to reference the latest version in VBA this should fix this - is there a way to do this with VBA?

Sub AddLatestOWCRef

?

[Aflatoon]

If i was to reference the latest version in VBA this should fix this - is there a way to do this with VBA?

If your users all have the latest version, then there is no need for vba to add the reference. Just add the correct versions of the controls to the forms.

[theta]

If your users all have the latest version, then there is no need for vba to add the reference. Just add the correct versions of the controls to the forms.

My problem is not the version - microsoft windows 7 removes OWC10 as it had security issues. And the users (several of them) are not techie.

So would like to have some VBA in the Auto_Open to add the latest OWC reference on open. I already have code to check the registry keys and if OWC is missing altogether, it prompts them to download it. But in many cases, version 11 or 12 is present and 10 has been removed - causing the lack of referencing.

Any help appreciated...

[Aflatoon]

Your problem is the version. If you have an earlier version of the control, it gets removed, hence it's a version problem.

If your users all have different versions or maybe missing the library altogether then I suggest you add the controls at runtime (referencing won't help) and refer to them using me.controls("control name") rather than a direct reference using Me.Controlname

[theta]

Your problem is the version. If you have an earlier version of the control, it gets removed, hence it's a version problem.

If your users all have different versions or maybe missing the library altogether then I suggest you add the controls at runtime (referencing won't help) and refer to them using me.controls("control name") rather than a direct reference using Me.Controlname

Yes this is late binding vs. early binding. But the control still needs to be added under referenced under the VBE? This is the part I wanted to automate?

[theta]

Yes this is late binding vs. early binding. But the control still needs to be added under referenced under the VBE? This is the part I wanted to automate?

Sorry I re-read your post.

Do you have a code example to late bind the OWC dll? (regardless of version)

Dim x as Object
Set x = CreateObject ( ?? )

If I set this at runtime, then all OWC components should work?

[Aflatoon]

Assuming you know the version available:

    Dim ctl As Control
    Set ctl = Me.Controls.Add("OWC11.Spreadsheet", "Spreadsheet1")

The controls are version-specific so you cannot add them at design time-you have to actually add them to the form at runtime. You might find it easier to use actual charts in the workbook and load images of them dynamically or just use the worksheets!

[theta]

Assuming you know the version available:

    Dim ctl As Control
    Set ctl = Me.Controls.Add("OWC11.Spreadsheet", "Spreadsheet1")

The controls are version-specific so you cannot add them at design time-you have to actually add them to the form at runtime. You might find it easier to use actual charts in the workbook and load images of them dynamically or just use the worksheets!

There are some elements that have been implemented at design stage (not by me).

I just removed OWC11 and Office Web Components XP. When I open the project and enter the VBE (Alt+F11) and try to view the form in design mode I get an error saying some of the objects are not available and a missing reference under the Tools > Referencing

MISSING : Microsoft Office XP Web Components

So there must be elements at design stage.

What I really need is a catch all solution. Do they have XP, OWC10, OWC11, OWC12? Reference the correct library.

I cannot find any early binding at all in the form code / related modules...

[theta]

This is based on my (limited) knowledge of OWC. An OWC9 is compatible with OWC10 > OWC11 > OWC12 reference library. There is backward compatibility. The original designer used the oldest version of office on purpose to ensure future compatibility.

I do not want to keep sending user the OWC10 installer just to get the project to work, especially considering they are all (mostly) on 2007 /2010 so have 11 and 12 available...

?

[Aflatoon]

Are there references to the controls directly rather than as part of the Controls collection (i.e. Me.controlname rather than me.controls("Controlname") as I mentioned previously)?
Do the controls get removed if you open them on a machine with a different version of the OWC library? (I can't recall)

[theta]

Ahh...just found one of the culprits. Not an actual object, but part of a reference :

[vba]
Private Sub spChange_SheetChange (ByVal sht as OWC10.Worksheet, ByVal Target as OWC10.Range)
[/vba]

This is within a form element - very strange. How would I solve this issue, as it is a very awkward place (attributes of a sub).

And on other forms (different workbook references as an addin) I received the missing error when trying to view the form - which leads me to believe OWC controls have been incorporated directly onto the forms at design time.

[theta]

I have also found several references to XYZ(ByVal Cancel as MSForms.ReturnBoolean) which I now understand to be an even older OWC reference (MSO).

So is there a way to get the projet working with the latest OWC version / late binding?

Thanks for your insight so far...

[Aflatoon]

I have also found several references to XYZ(ByVal Cancel as MSForms.ReturnBoolean) which I now understand to be an even older OWC reference (MSO).

That makes absolutely no sense whatsoever, I'm afraid.

Re the controls added at design time, that is exactly what I have been talking about.

I suggest you either settle on one version of OWC that everyone can use (if not everyone has one, then get them one) or you abandon OWC completely (which is sensible as it has been deprecated anyway) and rethink whatever it is your workbook does.

[theta]

That makes absolutely no sense whatsoever, I'm afraid.

These MSO Form elements are OWC 9 - based on what I have read on the microsoft website (OWC XP).

There are also the obvious OWC10 components.

I thought the whole idea of a late bind was so that I could reference something regardless of it's version.

e.g. using this early bind would require referencing in VBE :

[vba]
Dim oXL As Excel.Application
Set oXL = New Excel.Application
[/vba]

But using this code would not require any referncing

[vba]
Dim oXL As Object
Set oXL = CreateObject("Excel.Application")
[/vba]

I was hoping there would be an approach where I do not have to set references to OWC10, 11, 12 - but I would like some VBA to check that a version of OWC is installed.

[Aflatoon]

These MSO Form elements are OWC 9 - based on what I have read on the microsoft website (OWC XP).

No, they are not. MSForms is the library that gives you userforms and the Forms controls.

Late binding automation objects is easy because you create them at runtime. You cannot strictly late bind a control that you add at design time since you are using a specific executable but you can usually create them at runtime and hook their events. I don't think this will work with OWC since the event declarations seem to refer to version-specific objects which would therefore require a reference.

These controls were not really designed for use in office apps so I would suggest you either try and ensure everyone has the same version or use a different method.

[theta]

Thanks - all good info there. I got confused as OWC 9 components are named using MSO (http://support.microsoft.com/kb/319793).

I will see what I can change in the code to remove these hard library references/methods.

Many thanks