Solved List References locations? (1 Viewer)

[Gasman]

Hi,
Does anyone know how to list the path of the references used in an Office program?
At the moment I am interested in Excel, and here is the reason why.

Trying to install an old version of office, but a very insiduous program called WPS Office was on this brand new computer.
My Excel workbook automates a mailmerge with Word to print passenger names, addresses etc.

On my setup I have

However despite installing Office and checking that doc and docx were associated with WOrd, when I ran my code WPS word editor would load.
As we did not want it, I uninstalled it, but it has left it's tentacles in the system, so that the library for Microsoft Office Object Library points to a Kingsoft folder and file which no longer exists, nor would I want it to. Despite attempting a repair, uninstall and install, I cannot get rid of this entry.

If anyone knows where to look in the registry and amend, I am prepared to go there and fix it.

I have tried browsing to the MSO.DLL file (that I believe it should be from googling), but it stays as the Kingsoft file.

Spent a few hours this afternoon, trying to fix what was meant to be a simple install and copy two files for my controller, and that did not turn out the way it was meant to be.

Thanks for any help.

 

[Gasman]

Marvellous,
You look for a good while and do not find anything, albeit on another computer.
I come home and try on mine after posting the above, and lo and behold, I find something.

Might come in handy for someone else?
That and more found at https://excelatfinance.com/xlf20/xl...igure,description property, in priority order.

Sub xlfVBEListReferences()
' Requires References :: Microsoft Visual Basic for Applications Extensibility 5.3
'                        C:\Program Files (x86)\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB
Dim oRef As VBIDE.Reference   ' Item
Dim oRefs As VBIDE.References ' Collection
Dim i As Integer

Set oRefs = Application.VBE.ActiveVBProject.References

    Debug.Print "Print Time: " & Time & " :: Item - Name and Description"
        For Each oRef In oRefs
            i = i + 1
            Debug.Print "Item " & i, oRef.Name, oRef.Description
        Next oRef
    Debug.Print vbNewLine

    i = 0
    Debug.Print "Print Time: " & Time & " :: Item - Full Path"
        For Each oRef In oRefs
            i = i + 1
            Debug.Print "Item " & i, oRef.FullPath
        Next oRef
    Debug.Print vbNewLine

    i = 0
    ' List the Globally Unique Identifier (GUID) for each library referenced in the current project
    Debug.Print "Print Time: " & Time & " :: Item - GUID"
        For Each oRef In oRefs
            i = i + 1
            Debug.Print "Item " & i, oRef.GUID
        Next oRef
    Debug.Print vbNewLine

End Sub

 

[jdraw]

Paul,

Isladogs has a Version Checker Addin that can show references.

Glad you have it sorted.

 

[Gasman]

Paul,

Isladogs has a Version Checker Addin that can show references.

Glad you have it sorted.

I have yet to run that on the other computer.

The GUID is all over the place on my computer, so will need to see where it might exist on that computer.
At least I will be able to find the WPS entry in the registry a lot easier.

 

[jdraw]

Paul,
I didn't realize it was for 2010 and above and that you were using 2007.

Here's a sub similar to what you have that may be helpful.

'---------------------------------------------------------------------------------------
' Procedure : ListReferences
' Author    : Dirk Goldgar
'from daniel Pineault @https://www.devhut.net/2017/03/03/vba-list-references/
'   Contributor: Tom van Stiphout
' Date      : 30-Oct-2017
' Purpose   :To provide a detailed list of references
'---------------------------------------------------------------------------------------
'
Sub ListReferences()
     
         
      
          Dim ref As Access.Reference
          Dim strRefDescription As String
          Dim lngCount As Long
          Dim lngBrokenCount As Long
          Dim blnBroken As Boolean
      
10       On Error GoTo ListReferences_Error

20        Debug.Print "REFERENCES"
30        Debug.Print "-------------------------------------------------"
      
40        For Each ref In Application.References
50            blnBroken = False
60            lngCount = lngCount + 1
70            strRefDescription = vbNullString
      
80            Err.Clear
90            strRefDescription = strRefDescription & "Name: '" & ref.name & "'"
100           If Err.Number <> 0 Then
110               strRefDescription = strRefDescription & "Name: " & "(error " & Err.Number & ")"
120               blnBroken = True
130           End If
      
140           Err.Clear
150           strRefDescription = strRefDescription & ", FullPath: '" & ref.fullPath & "'"
160           If Err.Number <> 0 Then
170               strRefDescription = strRefDescription & ", FullPath: " & "(error " & Err.Number & ")"
180               blnBroken = True
190           End If
      
200           Err.Clear
210           strRefDescription = strRefDescription & ", Guid: " & ref.Guid
220           If Err.Number <> 0 Then
230               strRefDescription = strRefDescription & ", Guid: " & "(error " & Err.Number & ")"
240               blnBroken = True
250           End If
      
260           Err.Clear
270           strRefDescription = strRefDescription & ", Kind: '" & ref.Kind & "'"
280           If Err.Number <> 0 Then
290               strRefDescription = strRefDescription & ", Kind: " & "(error " & Err.Number & ")"
300               blnBroken = True
310           End If
      
320           Err.Clear
330           strRefDescription = strRefDescription & ", BuiltIn: " & ref.BuiltIn
340           If Err.Number <> 0 Then
350               strRefDescription = strRefDescription & ", BuiltIn: " & "(error " & Err.Number & ")"
360               blnBroken = True
370           End If
      
380           Err.Clear
390           strRefDescription = strRefDescription & ", IsBroken: " & ref.IsBroken
400           If Err.Number <> 0 Then
410               strRefDescription = strRefDescription & ", IsBroken: " & "(error " & Err.Number & ")"
420               blnBroken = True
430           End If
      
440           Err.Clear
450           strRefDescription = strRefDescription & ", Major: " & ref.Major
460           If Err.Number <> 0 Then
470               strRefDescription = strRefDescription & ", Major: " & "(error " & Err.Number & ")"
480               blnBroken = True
490           End If
      
500           Err.Clear
510           strRefDescription = strRefDescription & ", Minor: " & ref.Minor
520           If Err.Number <> 0 Then
530               strRefDescription = strRefDescription & ", Minor: " & "(error " & Err.Number & ")"
540               blnBroken = True
550           End If
      
560           If blnBroken Then
570               lngBrokenCount = lngBrokenCount + 1
580               strRefDescription = "*BROKEN* " & strRefDescription
590           End If
      
600           Debug.Print strRefDescription
      
610       Next ref
      
620       Debug.Print "-------------------------------------------------"
630       Debug.Print lngCount & " references found, " & lngBrokenCount & " broken."
      
640       If lngBrokenCount <> 0 Then
650           MsgBox "Broken References were found in the VBA Project!", vbCritical + vbOKOnly
660       End If
       
ListReferences_Exit:
670      Exit Sub

ListReferences_Error:
680      MsgBox "Error " & Err.Number & " in line " & Erl & " (" & Err.Description & ") in procedure ListReferences of Module TonyToewsReferences"
690      Resume ListReferences_Exit
End Sub

 

[Gasman]

Thank you Jack.

 

[isladogs]

Paul
In addition to the comments in our email exchange, you may be interested in these articles on my website:

Add References Using Code

This article explains how to list references in a table or save these to a text file. It also describes 2 methods of adding references in code - using GUID or file name

www.isladogs.co.uk

Reference List & Locations

This article describes two simple routines giving a list of VBA references & their locations.

isladogs.co.uk

HTH

 

[Gasman]

Thank you Colin, the link I got that code from, also has code for adding/removing references. I will be giving that a go on the new computer. It appears it is only this reference that has been hijacked, the rest are all MS files.