Missing object library reference 1

[dean12]

I've asked this question before and I would like to revisit the issue before the company goes out and spends tons of money on MS Office licenses.

We have users running Office 2000, XP, and 2003. We have a spreadsheet that's got tons of VBA code in it and everybody uses it, updates it, sends it to each other, etc.

Problem is that when the spreadsheet moves from user A on one version of Office to user B on a different version, then we get "Object Library Missing" errors and we have to go to the TOOLS --> REFERENCES box and remove the reference to the object library that is not there and check the reference for the library that we do have. I'm pretty sure this is a reference to "Microsoft Office 99.9 Object Library". I'm thinking I use Excel XP so I have Office 10.0 Object Library and I belive the Excel 2003 users have Office 11.0 Object Library.

It's a real pain to have to change the references when you get a spreadsheet sent to you and it's especially hard to teach the exec's of the company how to do this and why it is necessary.

So the question is: Is it for sure necessary? Is there no solution to this issue that provides an automated fix?

I'm thinking that in the discussion before there was a reference to some MSDN article that explained why this was a problem - it had to do with static, compile-time code usage rather than instantiating things dynamically. Unfortunately, that article kinda went over my head and I really didn't end up learning any solutions.

Appreciate any thoughts.

 

[xlbo]

the simplest and best solution is to have all users running the same OS and the same versions of applications - then it ain't an issue

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[dean12]

Yea, that's probably true --- but across an entire organization, what a job.

 

[sdraper]

look at early vs late binding. I am sure there are several threads about it on this forum.

Sam

 

[dean12]

Well, the early .vs. late binding issue has come up before but frankly that's where I get lost. The issue here is I think the MS Office 99.9 Object library.

From VBA, what would I do differently than I have already done that would make the whole thing portable between different versions of Excel?

For example, I create a new spreadsheet with absolutely no attached VBA code - no macros. I store a number or two, save the spreadsheet, and then send it to you. You open it with your version of Excel ---- no problems ---- why?

Next I add VBA code into "This Workbook" that says:

Option Explicit

Private Sub Workbook_Open()
'
' Flip between two sheets to force the sheet activation event
' to occur. This event will not happen if our final sheet is
' already active.
'
Application.ScreenUpdating = False
sh01.Activate
sh00.Activate
Application.ScreenUpdating = True

End Sub


and now the thing won't work when I send it to you and you have a different version. What about this code am I supposed to early or late bind? We can't say late bind the Application object because the Application object is already in execution - I think. I don't know, frustrating as I honestly do not fully understand the failure to begin with.

 

[xlbo]

You are not quite right in asserting that a spreadsheet created in 1 version of excel will be ok in another - it may be for some / the majority of functions but there will be some functions that are not backwards compatible - same thing goes for code but the important difference is that the VBA code must be compiled BEFORE it can be run and if a function does not exist or the wrong library is being referenced, it cannot compile.

Having said all of that, the code you posted should work on any version of excel higher than '95 - you should only really see issues when attempting to invoke foreign object libraries ie if you are using Access to open Excel then you would need to reference the correct object library but internal code should be fine.....

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[dean12]

Maybe my lack of complete understanding on this is causing a communication problem.

It appears to me that when you work with Excel certain object libraries are involved and these can be found by ALT-F11, TOOLS, References.

On my system, I open Excel and I have a blank, virgin worksheet displayed. ALT-F11, Tools, References shows the following references checked:

Visual Basic for Applications
Microsoft Excel 10.0 Object Library
OLE Automation
Microsoft Office 10.0 Object Library

It's the "MIcrosoft Office 10.0 Object Library" that becomes the problem. I send my spreadsheet to someone with a different version of Excel and they don't have the "Office 10.0", they have "9.0" or "11.0". Now we have a problem because there is a reference to an object library that is missing so the spreadsheet fails to start and the end user has to go into TOOLS --> REFERENCES and uncheck the "10.0" and check whatever version they have.

I really don't care so much what causes the problem, I'm just trying to ascertain how to resolve the issue in a more automatic fashion than manually editing references.

Maybe there is some way for each user to have version 9, 10, and 11 of the Microsoft Office Object library and then it would not be a problem - maybe so?

 

[xlbo]

Well my point about having a standard OS and set of applications is the best course of action. If this isn't feasible for whatever reason (we seem to have managed it here) then you need to get your users to understand the limitations.

A workbook created in 2000 should be able to be opened in XP or 2003. A workbook created in 2003 may well not open in 2000. In all versions of excel / office apps, there is an option to SAVE AS a previous version so in excel 2003 you can save as 2000 - that way the workbook can be opened in 2000, XP or 2003.

As far as I know, there is no way of having multiple object libraries and getting them to self reference - I believe it can be done via code but it would almost certainly be long and involved and the code would have to be attached to every excel workbook.

Having read your 1st post again, this may be a viable course of action if there is only 1 (or a couple) problematic workbook

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[dean12]

I want all of you who have responded to know that I am grateful for the dialog on this subject and if anyone feels I have taken issue with them, I sincerely apologize. I am simply going over this issue repeatedly and from different directions because it confounds me and in the midst of that confusion I am not sure that I am being clear about the issue.

I have forwarded and received hundreds if not thousands of spreadsheets over the generations of Microsoft Office and this issue does not seem to come up until VBA code is inserted. How on God's green Earth Microsoft expects their office users to: (1) never use code behind a spreadsheet or (2) be able to nagivate the solution of setting object library references is beyond me. I guess that's what seems so stupid to me and since it seems stupid, I begin to feel I am in some way stupid and missing the solution.

 

[xlbo]

dean12 - don't beat yourself up over this. I don't think anyone feels like you have taken issue with them - I certainly don't.

I am a little confused as both the organisations I have worked for (in the past 5 years) have had a corporate office solution ie everyone is on the same OS and the same version of Office Apps - I guess that is exactly as M$ would like it to be - ergo everyone upgrades at the same time and they get another nice fat slice of upgrade fee

I'm pretty sure I understand the issue and as I mentioned in my last post, I'm pretty sure that a solution could be set up for a few spreadsheets (seems like VB apps must do this quite a lot) - whether that would fix the issue is another matter as you would then have to keep applying the fix to new spreadsheets as they are created - it certainly wouldn't be a global fix as it would have to be applied as code to each workbook in turn in the wb_open event

Might be worth asking the question about setting references dynamically in the VB5/6 forum as I'm sure they must deal with this kid of scenario more

Anyways - hope you have had at least some half decent ideas here

Peace

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[dean12]

I understand the corporate license process and you are probably correct that that will ultimately be the way we have to go. Our financial group called me a couple of days ago and that's one of the things I spoke to them about - acquisition of a corporate license. My response was "Let's run this up the pole one last time and make sure I haven't missing something.".

 

[JoeZim]

I have run into a similar situation, but my problem was with Windows XP versus NT. Anyways, I had to create a patch macro to update an object library (I currently run this patch macro from my Personal.xls file).

In your case, here is a sample of code to find out which version of excel the current machine has installed:

Private Sub CommandButton1_Click()

Dim versiontype As String

versiontype = Application.version

MsgBox "Microsoft Excel " & versiontype & " Object Library"

Select Case versiontype
Case "9.0"
MsgBox "Success"
Case "10.0"
MsgBox "Fail"
Case "11.0"
MsgBox "Fail"
End Select

End Sub

Now, in the action statements of the Cases, you could use the following code to update the appropriate object library:

Set ThisProj = ActiveWorkbook.VBProject

ThisProj.References.AddFromFile "C:\Program Files\Microsoft Office\Office\MSACC9.OLB" (This example is adding the Microsoft Access 9.0 Object Library. You will need to find the location of the reference file and proper library type.)

I know how frustrating this problem can be, so I thought I would at least give you some samples that helped me.

Joe

 

[xlbo]

Nice Joe - like it

Have a ppt

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question