Is there a code that Can be used to setup the reference library

Sheils
Sheils used Ask the Experts™
on
As we all know every time you install a database application that have some degree of automation, you have to make sure that the related reference  library has been checked.

I was wondering if there is any code that could be inseryed in the application that would inspect the reference library and select the required references.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Chris BottomleySoftware Quality Lead Engineer
Top Expert 2011

Commented:
Hello sb9,

Do you mean something like this ... the fixed string can of cource be replaced with a loop on an array of references.

Regards,

chris_bottomley
Sub addme()
   With Application
      With .AddIns("Analysis Toolpak")
         If .Installed = False Then
             .Installed = True
         End If
      End With
   End With 
End Sub

Open in new window

Author

Commented:
Chris

I am not sure. What is Analysis Toolpak.

What I enquierying about is if for example I have automation to mail merge data from MS Access to MS Word document, I need to have Microsoft Word XX Object Library selected in the reference library.

I am wondering if there is a code that will check if the object library has been selected and if it is the correct version.

The following site provides a way to copy the list selected library. I think that it should be posible to go on from there

http://www.msaccesstips.com/2009/03/ms-access-and-reference-library.shtml
Software Quality Lead Engineer
Top Expert 2011
Commented:
APologies .. I was looking at the wrong thing.

Try the following it only addresses the word library since that was alll I think you identified.

Chris
Sub AddReference()
' Derived from some code defined in www.vbaexpress.com
Dim strGUID As Variant
Dim itm As Variant
Dim ref As Variant
Dim i As Long
'Word Library : {00020905-0000-0000-C000-000000000046}
 
    strGUID = Array("{00020905-0000-0000-C000-000000000046}")
     
     'Set to continue in case of error
    On Error Resume Next
     
     'Remove any missing references
    For i = Application.VBE.ActiveVBProject.References.Count To 1 Step -1
        Set ref = Application.VBE.ActiveVBProject.References.Item(i)
        If ref.IsBroken = True Then
            Application.VBE.ActiveVBProject.References.Remove ref
        End If
    Next i
     
     'Clear any errors so that error trapping for GUID additions can be evaluated
    Err.Clear
     
     'Add the reference
    For Each itm In strGUID
        Application.VBE.ActiveVBProject.References.AddFromGuid _
        Guid:=itm, Major:=1, Minor:=0
         
         'If an error was encountered, inform the user
        Select Case Err.Number
        Case Is = 32813
             'Reference already in use.  No action necessary
        Case Is = vbNullString
             'Reference added without issue
        Case Else
             'An unknown error was encountered, so alert the user
            MsgBox "A problem was encountered trying to" & vbNewLine _
            & "add or remove a reference in this file" & vbNewLine & "Please check the " _
            & "references in your VBA project!", vbCritical + vbOKOnly, "Error!"
        End Select
    Next
    On Error GoTo 0
End Sub

Open in new window

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012
Commented:
... Or use "Late Binding", then you will probably won't need any VBA references for the common Libraries at all!
;-)

See here:
http://word.mvps.org/fAQs/InterDev/EarlyvsLateBinding.htm

JeffCoachman

Author

Commented:
Hi Guys

Thanks for your contributions.

Chris

My question was generic, I wanted to know if it is posible to insert codes in your application that will add required references and delete missing reference. I only used MS Word as an example. You answer clear indicate that there is. I have tried it and it works on my Word 2003 version. My understanding is that it will work on every version. Is that correct?

If I understand you code correctly one needs to know that

 strGUID = Array("{00020905-0000-0000-C000-000000000046}")


Also since I use other refrences such as outlook, ADODB,DOA, Microsoft Scripting Runtime ect... can you tell me where I find the strGUID value for the other references.

Also can you provide syntax for adding multiple references

Jeff

The article on late binding was interesting. Can late binding be used with all the reference.

Cheers

SB
Chris BottomleySoftware Quality Lead Engineer
Top Expert 2011
Commented:
LAte binding absolutely, can be used for pretty much everything everything ... though you do lose intellisense support in non native applications.

ASs for auto enable then others are:

'VBA Library                {000204EF-0000-0000-C000-000000000046}
'Excel Library              {00020813-0000-0000-C000-000000000046}
'stdole Library             {00020430-0000-0000-C000-000000000046}
'Office Library             {2DF8D04C-5BFA-101B-BDE5-00AA0044DE52}
'Access Library             {4AFFC9A0-5F99-101B-AF4E-00AA003F0F07}
'VBIDE Library              {0002E157-0000-0000-C000-000000000046}
'MSForms Library            {0D452EE1-E08F-101A-852E-02608C4D0BB4}
'Word Library               {00020905-0000-0000-C000-000000000046}
'Scripting Library          {420B2830-E718-11CF-893D-00A0C9054228}
'VBScript_RegExp_55 Library {3F4DACA7-160D-11D2-A8E9-00104B365C9F}
'MAPI Library               {3FA7DEA7-6438-101B-ACC1-00AA00423326}

And they are version independant i.e. work on on all versions of an application picking up whatever is installed.

Chris
Chris BottomleySoftware Quality Lead Engineer
Top Expert 2011
Commented:
For multiples, i.e. word and scripting:

    strGUID = Array("{00020905-0000-0000-C000-000000000046}", "{420B2830-E718-11CF-893D-00A0C9054228}")

Chris
Chris BottomleySoftware Quality Lead Engineer
Top Expert 2011
Commented:
Also:

'ADODB Library              {B691E011-1797-432E-907A-4D8C69339129}
'DAO Library                {00025E01-0000-0000-C000-000000000046}

Chris

Author

Commented:
Thanks Chris

That works really well. I'll award the points since you have answered my original question. But any chance letting me know where I can find these values so that if one day I use a new reference not listed in your  post, I will know what number to use.

Cheers

SB9
Chris BottomleySoftware Quality Lead Engineer
Top Expert 2011

Commented:
I belive I can find something ... I used a sub in the past so I should be able to find it.  Might take a little while though

Chris

Author

Commented:
That's fine mate. Don't worry if it is going to take you a while to fine it. I thought that you had website or a way of browsing the computer for the values handy. But if you have to spend time looking for it don't worry. I will conduct a search or post a question when I come to this crossroad

Cheers

SB
Chris BottomleySoftware Quality Lead Engineer
Top Expert 2011

Commented:
Try the following I have modified it to work with Access ... I only had it tuned for word and excel but have added access as well now.  Run sub whatRefs and it will populate a file in the specified path with the current references therefore select the one want and run the sub.

Chris
Sub whatRefs()
Dim fso As Object
Dim outputFile As Object
Dim outputPath As String
Dim outputFileName As String
Dim ref As Long
Dim VBP As Object
 
    outputPath = "c:\deleteme"
    outputFileName = "GUID.txt"
    Set fso = CreateObject("scripting.filesystemobject")
    If Right(outputPath, 1) <> "\" Then outputPath = outputPath & "\"
    On Error Resume Next
    If Not fso.FolderExists(outputPath) Then
        fso.CreateFolder outputPath
    End If
    Set outputFile = fso.CreateTextFile(outputPath & outputFileName, True)
    If Application.Name = "Microsoft Excel" Then
        Excel_App_Tuning VBP
    ElseIf Application.Name = "Microsoft Word" Then
        Word_App_Tuning VBP
    ElseIf Application.Name = "Microsoft Access" Then
        Access_App_Tuning VBP
    End If
    
    On Error Resume Next
    For ref = 1 To VBP.References.Count
        With VBP.References(ref)
            outputFile.Write "' " & .Name & " Library" & String(40 - Len(.Name), " ") & .Guid & vbCrLf
        End With
    Next
    On Error GoTo 0
    outputFile.Close
 
End Sub
 
Sub Excel_App_Tuning(container As Object)
    Set container = ThisWorkbook.VBProject
End Sub
Sub Word_App_Tuning(container As Object)
    Set container = Application.VBE.ActiveVBProject
End Sub
Sub Access_App_Tuning(container As Object)
    Set container = Application.VBE.ActiveVBProject
End Sub

Open in new window

Author

Commented:
Briliant

Thanks Chris. You have saved me a lot of headaches
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
chris_bottomley,

<LAte binding absolutely, can be used for pretty much everything everything>

Thanks

I was only sure that Late Binding would work for the Office Libraries.

;-)

Jeff

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial