References 2

[FranckyBo]

Hello,

How can I add dynamically a reference in a VBA Projet (word 2003) ?
Thnak's

 

[fumei]

Application.VBE.ActiveVBProject.References.AddFromFile "c:\WINNT\System32\winhttp.dll"

This would add a reference to the WinHTTP library that uses HTTP to download web files (as binary) directly into Office applications.

Gerry

 

[FranckyBo]

Thank's,

Thank's

Itwork wonderfull

 

[mts176]

How about removing a reference?

 

[fumei]

Not by code, that I know of. You can check to see if a reference IS referenced (or rather if it is active) with the .IsBroken property (Boolean), as in:

Application.VBE.vbprojects(1).References(1).IsBroken

However, while a return of False (it is NOT broken, and therefore IS actively referenced), can be returned, .IsBroken is read-only. So you can not break it by code. I would be happy to be corrected.

Gerry

 

[TonyJollans]

Hi mts176,

Yes, you can remove references by code. Some variation on this (which uses Gerry's example from above) will work ..

[blue]For Each r In Application.VBE.ActiveVBProject.References
    If r.FullPath = "c:\WINNT\System32\winhttp.dll" Then Application.VBE.ActiveVBProject.References.Remove r
Next[/blue]

If you know the Name or position of the reference you should be able to do it without the loop.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[mts176]

Thanks TonyJollans, it worked.

 

[fumei]

Damn...ooops, I mean..."darn".

Gerry

 

[fumei]

So. All you need is the index number, which is the order you see them in refereneces. You do not need to use .FullPath, or the loop.

Dim myRef As Reference
Set myRef = Application.VBE.ActiveVBProject.References(6)
Application.VBE.ActiveVBProject.References.Remove myRef

removes the 6th reference.

OR, without making the Reference object

Application.VBE.ActiveVBProject.References.Remove _
Application.VBE.ActiveVBProject.References(4)

removes the 4th.

Thanks Tony. I was trying to use a method AFTER making the object, as in:

Application.VBE.ActiveVBProject.References(4).Delete (or Remove)....which...does not work. I did not see the method on the Reference object, as I was explicitly going for the object first. Syntax syntax...gotta wonder sometimes.

Gerry

 

[PBAPaul]

Hi Gerry

I want to delete all references and then add them as required - the program will operate on various computers and with various levels of Excel (versions 8 to 11).

Adding the references is no problem but what I am having difficulty with is determining the number of references to delete.

I am using the code

Z = application.VBE.ActiveVBProject.References.count

to determine the number of references but keep on getting an error. Any ideas how to determine the number of references and then I can loop through them and remove each.

Paul

 

[mts176]

I would suggest first opening the application and trying to remove all of the refereneces. Find the ones that wont let you drop them. They will more than likely be Excel and VBA.

Then you must find the correct order to add them to your project when it is opening.

 

[TonyJollans]

Hi Paul,

As well as index number you can use names to delete references, for example [blue]....References.Remove ....References("Excel")[/blue]

Also, might I ask what you are doing that you need to do so much manipulation of references at run time? There are scenarios where you might want to do it but they are relatively rare and if, as a matter of routine, you delete references up front, why not ship the project without the references in the first place?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[PBAPaul]

Hi Tony

I write programs for clients who have varying operating systems and levels of Office applications.

I create the programs on my main machine (XP Pro with Office 2003) then test them on my network of machines which are running various operating systems and various Office applications.

After a while, each machine has different references added as I try my different programs. What I want to do is clean all references on my machines and then add the references that I need.

Each of my computers could have a different number of references loaded and so looping through and cleaning them out before loading will enable me to ensure the programs will operate on a variety of platforms.

Paul

 

[PHV]

And isn't late binding an option for production ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TonyJollans]

Hi Paul,

I can understand that but References are only needed for early binding at compilation time and removing references at run time might, at least in theory, already be too late unless you are careful about how you code.

I don't know what you're referencing but you need quite a bit of code to determine local versions and installation options in order to reference the correct version libraries. If you really have too many different environments to cope with manually, I would think that using late binding would remove a lot of your problems

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[HughLerwill]

FranckyBo
Further to PHV's, late binding should do it so if you want an Excel.Application object do;

Dim ObjXL as Object 'do not use as Excel.Application here
Set ObjXL = CreateObject("Excel.Application")

This should give you an ObjXL which is compatible with just about any version of Excel since Office 95 (v4 I think). Theoretically it is slower than when early bound but don't loose sleep over that.

Having done this you can survive without a reference to the Excel vn library at all, but it is handy to maintain it so that you can continue to use constants, Excel help and other IDE features.

regards Hugh

 

[PBAPaul]

Hi All

For one of my programs, I have to load references for:
1 Visual Basic For Applications
2 Microsoft Excel 11.0 Object Library
3 OLE Automation
4 Microsoft Office 11.0 Object Library
5 Microsoft Forms 2.0 Object Library
6 Microsoft Outlook 11.0 Object Library
7 Microsoft Word 11.0 Object Library
8 Microsoft DAO 3.6 Object Library

I use the code as follows to get the application version and then to select references as appropriate.

On Error Resume Next

Select Case Application.Version

Case "8.0"    'Office 97
 Load version 8 references

Case "9.0"    'Office 2000
 Load version 9 references

Case "10.0"    'Office XP
 Load version 10 references

Case "11.0"    'Office 2003
With ActiveWorkbook.VBProject.References
 .Remove ThisWorkbook.VBProject.References("Microsoft Outlook 11.0 Object Library")
 MyPath = OfficePath & "msoutl.olb"
 strReference = MyPath
 .AddFromFile strReference
End With

With ActiveWorkbook.VBProject.References
 .Remove ThisWorkbook.VBProject.References("Microsoft Word 11.0 Object Library")
 MyPath = OfficePath & "msword.olb"
 strReference = MyPath
 .AddFromFile strReference
End With

Etc .....

End With

Case Else
MsgBox "This code was not written for Excel Version " & Application.Version

End Select

This program can go onto any machine that a client wants to put it on. If anyone can suggest another way of ensuring that all the necessary and correct libraries are loaded on any machine on which this program might operate then I would be very interested.

Thanks for all of your interest.

Paul

 

[TonyJollans]

Hi Paul,

If it works for you, it's good!

I currently have three different versions of most of Office on my machine but only Outlook 2000. Your code would fail on my machine if I ran it in Excel 2003.

The Word 2000 library is called msword9.olb - the Word 2002 and 2003 libraries are both called msword.olb - so your code as posted would fail with Word 2000.

I suspect there are other problems. Is there a reason you don't want to use late binding?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[