In order to create a macro that modifies code, you need to set a reference to the Microsoft Visual Basic for Applications Extensibility Library. Chip Pearson has some great pages dealing with this library Programming to the Visual Basic Editor.
Jake asks:
I have a several ( 14 ) templates which contain an incorrect reference within the VBA, I would like to fix by sending out sheet which contains VBA to fix the incorrect reference.
Okay, that’s not really a question, but there was a question in the email. I swear.
I’m probably not the guy to be giving anyone advice on changing references programmatically. I don’t think I’ve ever had to do it. My workbooks are used in an environment that I pretty much control, so I don’t have a lot of problems with references. But I gave it a shot anyway. I’m sure if there are better approaches, someone will let me know in a comment.
‘Changes reference from ADO x.x to ADO 2.8
Dim wb As Workbook
Dim ref As Reference
Dim sRefFile As String
‘The file path for ADO 2.8
sRefFile = “C:Program FilesCommon FilesSystemadomsado15.dll”
‘Open the workbook to change
Set wb = Workbooks.Open(ThisWorkbook.Path & “WrongRef.xls”)
‘Loop through the references and delete the ADO one
For Each ref In wb.VBProject.References
If InStr(1, ref.Description, “ActiveX Data Objects”) > 0 Then
wb.VBProject.References.Remove ref
Exit For
End If
Next ref
‘Add the new reference
wb.VBProject.References.AddFromFile sRefFile
wb.Save
wb.Close
End Sub
This assumes that you have a reference to a certain version of ADO and you want to change it to version 2.8. There’s probably no reason to actually do that, but that’s what I’m going with. I put the path to new library in a variable. I got this path by going to Tools > References, but it will no doubt be different on every machine. So what’s a good way to add that reference? AddFromGUID is probably better after you’ve scoured the registry with APIs, but if that’s so, I’ll let someone else figure that out.
I use a loop to remove the old reference, searching for a unique string in the Description. I could have also looked at the Name and deleted any with the name ADODB. As long as you’re scouring the registry for the new reference, I suppose you can get some info on the old reference and forget about the loop.
Other than telling you to set a reference and showing you the VBProject and Reference objects, this post sucks. Hopefully it will serve to provide better approaches via the comments.
One method might be to set up a workbook containing the references you want to use for replacement.
This macro will list the details before those references, including GUID, Major and Minor (required parameters for AddFromGUID:
(PS: I’ve used special html code for nonbreakingspace nbsp so it tabs the code properly – I hope it works)
Sub test()
Dim ref As Reference, rng As Range
Set rng = Range(“A1?)
rng.Value = “Name”
rng.Offset(, 1).Value = “Type”
rng.Offset(, 2).Value = “Description”
rng.Offset(, 3).Value = “Major”
rng.Offset(, 4).Value = “Minor”
rng.Offset(, 5).Value = “GUID”
rng.Offset(, 6).Value = “Is Broken”
rng.Offset(, 7).Value = “Full Path”
rng.Offset(, 8).Value = “Built In”
For Each ref In ActiveWorkbook.VBProject.References
Set rng = rng.Offset(1)
rng.Value = ref.Name
Select Case ref.Type
Case vbext_rk_TypeLib: rng.Offset(, 1).Value = “TypeLib”
Case vbext_rk_Project: rng.Offset(, 1).Value = “Project”
End Select
rng.Offset(, 2).Value = ref.Description
rng.Offset(, 3).Value = ref.Major
rng.Offset(, 4).Value = ref.Minor
rng.Offset(, 5).Value = ref.GUID
rng.Offset(, 6).Value = ref.IsBroken
rng.Offset(, 7).Value = ref.FullPath
rng.Offset(, 8).Value = ref.BuiltIn
Next
End Sub
Rob
hmph – smileys – i never win :(
replace the smiley with the number 8 followed by close bracket )
FWIW, I’ve used a method based on GUID, but always had problems. Referance are not something that are easy to fix.
Both of the above depend on assumeing the full path for a referance is known(???), which if often not possible.
I think this is one of the “problmes” of not “intalling” a app.
Hmmmm…
What library needs to be ‘referenced’ in order that “Dim ref As Reference” no longer produces “Compile Error: User-defined type not defined”?
Doco: It’s called “Microsoft Visual Basic for Applications Extensibility 5.3?.
Hmmm…
I don’t have 5.3 available but 6.0.
However, the struct –
For Each ref In ActiveWorkbook.VBProject.References
…
returns ‘type mismatch’ error.
I run XL 2000 Premium.
I replaced ‘ref’ with a variant ‘vItem’ and it works great now…
comments?
You’re effectively late-binding.
You could just as well write:
Dim ref As Object
For the code I comment_posted, you’ll also need to change:
vbext_rk_TypeLib and vbext_rk_Project
to 0 and 1 respectively.
PS. Interesting that you dont see “Microsoft Visual Basic for Applications Extensibility 5.3?
That reference is C:Program FilesCommon FilesMicrosoft SharedVBAVBA6VBE6EXT.OLB on my computer
Rob
doco: Are you sure you’re not referencing the “Microsoft Visual Basic Extensibility 6.0?? rather than the “… for Applications Extensibility 5.3??
Rob: When using AddFromGUID, you can use zero for the major and minor versions to pick the latest.
Dick/Jake: For this technique to work, the user needs to “Trust access to Visual Basic project” in Excel’s Tools > Macro > Security > Trusted Sources, and if the projects are protected, you’ll have to use the dreaded SendKeys to unprotect and reprotect them.
Regards
Stephen Bullen
Rob:found the *.olb and now the snippet works without tweaking…
Stephen: guess I am going to have to take remedial reading :eek: I did have the VB Extensibility in place of ‘Applications’ Extensibility.
Thanks
doco
Is it posible to set microsoft Excel not to change refrerence’s.
It is set on my laptop on “Microsoft Office 11.0 Object library”. I develop on my laptop and other people use it on theire desktop with an older office version.
If i set it off the problem is gone!