Change references to VBA code libs

[MrStar]

Hi
I have about 80 Excel workbooks which all uses the same Excel addin with shared vba code. So each of the workbooks has a reference to this x.xlam library.

The workbooks are used by employes during the day, and therefore I can't change anything in those or in the code library.
I need to have my own copies pointing to my own code library.

So when I make changes to the x.xlam library or one or more of the 80 workbooks, I need to change the references in VBA and this is very timeconsuming.

Is there any way to this using VBA ?

I'm thinking about some code which can open all 80 workbooks one by one and change the reference to my private codelib, do the compile and then save the workbook.

 

[MajP]

Maybe look at Thread705-903662

 

[MrStar]

Thanks for the tip, but it dosn't solve my problem

 

[MajP]

Really? What part of this does not help?
Demonstrates looping the references
Deleting a found reference
Adding in a new reference
and compiling the App

So what is left? I am assuming that since you are a programmer the opening and closing of the 80 dbs is not an issue. what have you wrote so far? What part of the code is unclear?

 

[MrStar]

Hi MajP
Actually I haven't the code you suggested because it is written to Access databases, and I don't think it will do for Excel VBA references.
The way the Access code is doing the reference is though what I was searching for but for Excel.

Have you tried this in Excel ?

 

[PHV]

I'll say the same thing than in the above referenced thread:
Provided you've referenced the Microsoft Visual Basic for Application Extensibility you may play with the References collection of the VBProject object.


Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

AFAIK there is no requirement to reference Visual Basic for Application Extensibility.

VBProject is memeber of the Excel.Workbook object. So basically the above code is modified from the

Access.References
to
ActiveWorkbook.VBProject.References

Sorry for the confusion, but since you posted the question in the Access forum I assumed you were familiar with both the Excel object model and the Access object model.