UNIVERSAL PROBLEM - Fixing Missing Office References Programatical

[Derek P.]

Skip to NUT & BOLTS OF IT if you don’t want the history.

I've searched and searched but cant' find someone that has found a solution
that I'm looking for, and everything I’ve tried “should†work but is failing.
Namely, I want to create a dependency checker that robustly checks all
dependencies used for my companies tool(s) written in excel, and remove
broken references, and re-add them based on the Office version.

NUT & BOLTS OF IT (I’ve included the problem function only, other functions
should be self explanatory what they do)

Macro Security > Trusted Publishers has Trust access to VB project checked.

When the workbook opens, the following code is executed:
'*********************************************************
'Function checks all currently enabled application dependencies and attempts
to
'resolve broken dependencies.
'
'Returns:
' True - No broken references encountered.
' False -
' Err.Number = 0 - Broken references encountered, but all were restored
' Err.Number = 1001 - Un-Handled exception occurred
' Err.Number = 1002 - Broken references encountered, and one or more
were ' not restored
Public Function bCheckDependencies() As Boolean
On Error GoTo ErrorHandler
Dim breturn As Boolean
Dim cbroken_references As New Collection
Dim reference As Object

'Loop through all of the application defined references to see if any are
broken
'or 'missing
For Each reference In ThisWorkbook.VBProject.References

'********************************
'I believe office is crashing at this point
If reference.isbroken = True Then
'********************************
'Add the GUID, Major, and Minor information for the broken reference
'into an array and add that to the broken ref collection.
cbroken_references.Add (Array(reference.GUID, _

reference.major, _

reference.minor))
'Remove the current broken reference
'********************************
'Or i get the "Object Library not registered" error at this point
ThisWorkbook.VBProject.References.Remove reference
'********************************
End If
Next reference

'Check to see if any of the application references were broken
If cbroken_references.Count <> 0 Then
Call RestoreBrokenDependencies(cbroken_references)
'Worksheet must be opened with all references valid, or other problems
'Spawn, return false to indicate references were missing. If Err.Number
'is 0, all references were restored correctly.
breturn = False
Else
breturn = True
End If

bCheckDependencies = breturn
Exit Function
ErrorHandler:
Select Case Err.Number
Case Else
'Pop message to user about function failure and exit.
Call iPopMSG(Array("UN-HANDLED EXCEPTION", _
Err))
Err.Clear
With Err
.Number = 1001
.Description = "Un-Handled exception occured"
.Source = "Module: " & sMODULENAME & _
vbLf & "Sub/Function: bCheckDependencies()"
End With
End Select
bCheckDependencies = False
End Function

This code functions correctly if saved in Office Excel 2003, and one of the
references are removed from the system, then re-opened in 2003. However if
the worksheet is saved in 2007 using the 2003 format, and opened on a 2003
machine where the ..\Microsoft Office\OFFICE12 folder is missing, Office will
either crash. (the reference missign is the REFEDIT.DLL for my project) or i
get an error number -2147319779: Object library not registered, either way
the result is that i can not remove the broken reference programatically,
and i believe it should be possible.

Any help would be appreciated as I've spent more than a few hours trying all
kinds of things and nothing resolves the problem.

Please let me know if additional information is needed.

Thank you,

 

[Jon Peltier]

I've had a few problems with workbooks I've moved backwards from 2007 to
earlier versions, when those workbooks have userforms with RefEdits on them.
This reminds me to practice what I preach, and develop in the earliest Excel
version that will use the workbook or add-in.

When I've screwed this up and built a form in 2007, the RefEdit usually is
problematic. Sometimes they even fail to appear on the forms. I've found
that unchecking the reference to the RefEdit in the VB project seems to
minimize the problem, without having any adverse effect on the RefEdits in
the userforms.

- Jon

 

[Derek P.]

Thank you, but the whole point is that i don't want to have to rely on
manually fixing the issue. Its worse than pulling teeth to get people to
understand what you want them to do, and having them email it to me so i have
to fix it manually gets old fast.

It appears that when the reference is updated by 07, 03 is unable to remove
it becuase the file folder does not exist. And i hate to make assumptions in
my code and just do a hack, i'd like to do it properly.

Any thoughts how to get around what appears to be a VBA editor problem?

Thanks,

D.P

 

[Jon Peltier]

Can't you just fix your version of the workbook and redistribute it? This
might be a good time to redesign your project so you have one workbook (or
add-in) with the code and another with the data, which the first workbook
acts on. Keeping the code and data separate make it possible to update one
without affecting the other.

- Jon

 

[Derek P.]

Unfortunately its not that simple. This isn't a simple data entry and parsing
kind of sheet.

The sheet itself is used to define a set of test cases based on a formatting
scheme that we've developed. The sheet then interfaces to a communications
program that interfaces to hardware to execute the tests.

The tool itself is pulled fresh from the network and then the test cases
defined. Once they are defined it is distributed to other team members as
needed to execute all of the items. The dependency locations will now change
depending on who is the last to save the workbook, and what version of office
they have. Consequently though the life of a single instance of the tool, it
may move between office 2003 and office 2007 multiple times before it is
archived with the final results imported into it.

So the reason i want to may the dependencies able to update on the fly is to
create as "seamless" of a tool as possible.

I'm starting to lose faith that it can be done with he current development
environment provided. As it is, this is a workaround that should not be
required, This issue has been around since before 2003 came out, and 2007 has
propagated the issue instead of fixing it, which disheartens me much.

Thanks for any other ideas towards my end goal if they are available.

D.P

 

[Jon Peltier]

Well, I guess it seems like a lot of work, but the best chance for success
is to somehow split up the single workbook into separate ones containing
either code or data but not both. The ones containing code should be written
in the earliest version of Excel which will be used, then distributed such
that they go from central network location to installation on each computer,
then never moved to another computer. This gives Excel/VBA a chance to
update the dependencies without ever having a problem with downgrade
dependencies.

Then when the data files are moved from computer to computer, there are no
dependencies to worry about.

- Jon

 

[Derek P.]

Thanks Jon,

Essentially what you're telling me is that there is no way to do it via
code. Very disheartening, so i guess my only chance is to have to find a
workaround from the proper code into something that works, much like we've
had to do on many instances where for one reason of the other, the compiler
won't allow us to do what it says it can.

When I find the workaround (If i should say) i'll post it here for others.

Have a good one everyone.

 

[Jon Peltier]

I've been through similar things in code. It's always easier to build a
system to resist this kind of mess, than to try to make something to
inoculate a system that's infected. I was recently reminded that one can
sometimes fix these issues using the Detect and Repair feature, but if
you're going to keep passing the Typhoid Mary worksheet around, you'll have
to keep doing Detect and Repair.

I already gave you a system which is better than a workaround. Add-in with
code that is only ever installed on one machine, so doesn't suffer from
mismatched references, plus data-only workbooks that don't need references
which may otherwise become mismatched.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______