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.
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
‘Add the new reference
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.