Well, I got something that did work. I changed
Set ref = Application.VBE.ActiveVBProject.References
Set ref = Application.VBE.VBProjects("MyProj").References
Where I renamed the default project name of VBAProject
Now I can see my dll class, and I was able to remove it.
Whatever works, heh? I am still open for suggestions if
anyone has a better (or more correct) way to do this.
>I have an Excel project where I make a reference to an
>ActiveX dll class I wrote in VB6 on my workstation, but
>that file is not installed on other workstations which
>will use this Excel file. So I need to programmatically
>remove the reference before shipping the Excel file off.
>The purpose of the dll class is to collect data from an
>Access Application and pass it to this Excel file. This
>works fine. But I need to remove the reference to my dll
>class in the Excle File. Even though the dll class
>the data correctly to the Excel file, I can't detect it
>with the following loop, and thus cannot remove it.
>Dim v As Variant
>For Each V in Application.VBE.ActiveVBProject.References
>This will correctly print out all the standard references
>and even the reference to VBA Extensibility, but it won't
>detect my ActiveX dll. In Access I can remove the
>reference successfully as follows:
>Dim ref As Reference
>Set ref = References!MyRef
>But this does not work in Excel. If anyone knows how I
>can do this in Excel, I would be grateful if you could