Removeing an Object Library Reference with VBA runtime error 9

[Liz_I3]

Hi am working with Access 2010. I am want to remove a reference from the library with VBA
I am using the below function
Public Sub RemoveReference(strReferenceName As String)
Access.References.Remove Access.References(strReferenceName)
End Sub

Calling from an Event Procedure

Call RemoveReference("Microsoft Outlook 14.0 Object Library")

I have also tried RemoveReference("C:\Program Files (x86)\Microsoft Office\Office14\MSOUTL.OLB") but keep getting Runtime Error9 Subscripted out of Range

What I am missing

Thanks
L

 

[Micron]

I am using the below function

Actually, it's a sub, which is a type of procedure, but it's not a function. Not sure why you'd want to removed a broken reference. If your application doesn't need it, don't include it in design. If it's needed but broken, better to fix it than remove it? Otherwise, something must be within that will eventually call it, thus causing an error if not an outright crash. Besides, will this be the only one that could be broken? Why not loop through the references and validate them, and if broken, get the GUID and path from a db table that lists where they would be stored - especially if you're working in a IT controlled environment where everything tends to be the same.

You call statement looks OK. I think the issue is your use of brackets and quotes around the remove statement. Suggest

Access.References.Remove strReferenceName

(assuming you have the rest of the syntax correct - I don't know about the Access preface since again, I have only written code to fix, not remove.

 

[Liz_I3]

Thanks I tried to remove the brackets but still cannot get it to work still getting Runtime Error9 Subscripted out of Range

The reference is not broken I just want to uncheck it with VBA instead of Tools > References and when unchecking it. I have been successful in checking it through VBA but cannot uncheck it

Thanks for your assistance

 

[Liz_I3]

Hi just an update
I found the below code and it does exactly what I wanted

Dim r As Reference

For Each r In References
If UCase(r.FullPath) = UCase("C:\Program Files (x86)\Microsoft Office\Office14\MSOUTL.OLB") Then
'MsgBox r.FullPath
References.Remove r
End If
Next r
Set r = Nothing

This is just a temporary fix. I have code to loop through a query and send emails. I want to change the procedure to a late binding.


L

 

[Micron]

A minor point: if you post your code changes we might find that one error was replaced by another, otherwise, we're just guessing if it's that or should be focusing on what's left. Which, by the way, after researching, it seems you don't remove the reference by referring to its name. What you have now is a loop that removes the item from the references collection IF a property = something (FullPath=, or Name=, etc). As I mentioned, I've only dabbled in this to check if all the required references were set (according to a db table of references) and fix them if broken. In playing around with what you're doing, I found that it removes the reference from the list entirely, not just unselects it. If you want it back, you better know where to find the file.
I suspect you don't need to UCase anything - unless you have Option Compare set to binary.

 

[xenou]

This is just a temporary fix. I have code to loop through a query and send emails. I want to change the procedure to a late binding.

Sounds like a good idea if you can do this - references are less of a problem when new office versions come out.

 

[air23live]

Excel help please

EXCEL


I am seeking help with macros and a template that has a picture recurring on each page i need to switch the picture.

I also need help transferring data from a master to a workbook in excel


New to this! not sure if I'm in the right area not sure how to post

 

[air23live]

Re: Excel help please

seeking advanced excel person to assist me in doing these two functions please reply here or email me @ antoinetterincon@gmail.com

thank you

 

[Micron]

Re: Excel help please

Suggest you read the Board Announcements near the top of your screen for help on how/what to post. Posting in the wrong forum thread on purpose is not cool. Hijacking a post for some unrelated issue you're having is even less so.

Look for the +Post New Thread button at the bottom of the list for the forum you should post in, and please do read the announcements.

 

[air23live]

Re: Excel help please

relax i asked for reason announcements were read couldnt find where to post geeze