Solved List References locations? (1 Viewer)

Gasman

Enthusiastic Amateur
Local time
Today, 22:12
Joined
Sep 21, 2011
Messages
14,472
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
1681832467716.png


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. :mad:

Thanks for any help.
 

Gasman

Enthusiastic Amateur
Local time
Today, 22:12
Joined
Sep 21, 2011
Messages
14,472
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.

Code:
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
 
Last edited:

jdraw

Super Moderator
Staff member
Local time
Today, 17:12
Joined
Jan 23, 2006
Messages
15,396
Paul,

Isladogs has a Version Checker Addin that can show references.

Glad you have it sorted.
 

Gasman

Enthusiastic Amateur
Local time
Today, 22:12
Joined
Sep 21, 2011
Messages
14,472
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

Super Moderator
Staff member
Local time
Today, 17:12
Joined
Jan 23, 2006
Messages
15,396
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.
Code:
'---------------------------------------------------------------------------------------
' 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
 

isladogs

MVP / VIP
Local time
Today, 22:12
Joined
Jan 14, 2017
Messages
18,258
Paul
In addition to the comments in our email exchange, you may be interested in these articles on my website:


HTH
 

Gasman

Enthusiastic Amateur
Local time
Today, 22:12
Joined
Sep 21, 2011
Messages
14,472
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.
 

Users who are viewing this thread

Top Bottom