Office XP VBA can't find reference after saving by Office 2003  
Author Message
Goheaven





PostPosted: Visual Basic for Applications (VBA), Office XP VBA can't find reference after saving by Office 2003 Top

Situation is:
1) I created VBA in Excel XP, and added reference to "Word" object --
version 10.0
2) I open that Excel in Excel 2003, and now the reference automatically
changed to -- version 11.0 Word object
3) I saved file, then open it again in Excel XP

Now the reference to "Word" object shows "missing" in VBA project, and
it still refer to version 11.0.

Any idea how to fix that Instead of modifying the reference in VBA
project.

Can we stop auto update of dll reference in Excel

Regards,
Robin


Microsoft ISV Community Center Forums2  
 
 
Cindy Meister





PostPosted: Visual Basic for Applications (VBA), Office XP VBA can't find reference after saving by Office 2003 Top

<<Situation is:
1) I created VBA in Excel XP, and added reference to "Word" object --
version 10.0
2) I open that Excel in Excel 2003, and now the reference automatically
changed to -- version 11.0 Word object
3) I saved file, then open it again in Excel XP

Now the reference to "Word" object shows "missing" in VBA project, and
it still refer to version 11.0.

Any idea how to fix that Instead of modifying the reference in VBA
project.

Can we stop auto update of dll reference in Excel >>

No, you can't "lock" references so they can't update. Best you could do,
if this is really something that's going to be going back and forth
between installations with different versions of Office, would be to
dynamically create the Reference in your VBA code, using an Auto_Open or
ThisWorkbook_Open macro.

To do this, you need to work with the VBE (Extensibility) library. AND
you need to be sure that macro security on all machines is set to allow
access to the VBA project. To get into the Help for the VBE type
something like the following, then press F1. Use the "Applies To" and
other menus and links to move between the Help topics.

ThisWorkbook.VBProject.References

Your solution needs to loop through all references and test the IsBroken
property. If it's true, you need to use the AddFromGUID method to
re-instate the reference, after testing the Application.Version to
determine which GUID you need.

You should also search MSDN and in the Knowledge Base on the term
IsBroken for some discussions and sample code on using these things.

Finally, for an example, see here and look for the proc Public Sub
prcAddReverence(objWorkbook As Workbook)
http://www.wer-weiss-was.de/theme156/article3462480.html