Access VBA References

[RichardMGreen]

Hi all

I've got the following code which lists all the ticked references in Excel:-

Option Explicit
 
Sub ListReferencePaths()
     'Macro purpose:  To determine full path and Globally Unique Identifier (GUID)
     'to each referenced library.  Select the reference in the Tools\References
     'window, then run this code to get the information on the reference's library
     
    On Error Resume Next
    Dim i As Long
    With ThisWorkbook.Sheets(1)
        .Cells.Clear
        .Range("A1") = "Reference name"
        .Range("B1") = "Full path to reference"
        .Range("C1") = "Reference GUID"
    End With
    For i = 1 To ThisWorkbook.VBProject.References.Count
        With ThisWorkbook.VBProject.References(i)
            ThisWorkbook.Sheets(1).Range("A65536").End(xlUp).Offset(1, 0) = .Name
            ThisWorkbook.Sheets(1).Range("A65536").End(xlUp).Offset(0, 1) = .FullPath
            ThisWorkbook.Sheets(1).Range("A65536").End(xlUp).Offset(0, 2) = .GUID
        End With
    Next i
    On Error GoTo 0
End Sub

Is it possible to change this so it picks up the same thing but in Access and inserts it into a table?

 

[SydneyGeek]

Hi Richard, try this:

Create a table (tsysRefs) with these fields:
RefID (Autonumber, PK)
RefName (Text)
RefPath (Text)
RefGUID (Text)

Insert this code into a module, and run it to list the refs:

Sub ListRefs_Access()
    Dim i As Long
    Dim strSQL As String
    
    'clear out tsysRefs
    CurrentDb.Execute ("DELETE * FROM tsysRefs")
    
    'create records in tsysRefs
    On Error Resume Next
    For i = 1 To Application.VBE.ActiveVBProject.References.Count
        strSQL = "INSERT INTO tsysRefs (RefName, RefPath, RefGUID ) " _
            & "VALUES('" & Application.VBE.ActiveVBProject.References(i).Name & "','" _
            & Application.VBE.ActiveVBProject.References(i).FullPath & "','" _
            & Application.VBE.ActiveVBProject.References(i).Guid & "')"
        Debug.Print strSQL
        CurrentDb.Execute strSQL
    Next i
    On Error GoTo 0
End Sub

Denis

 

[RichardMGreen]

Hi Denis

Managed to get the current references into a table but now it won't let me load them again.
I'm at home now, but from memory, the problem lies with refitem.name where refitem is DIMmed as Reference.

I'm running 2007 (and it seems to work without errors for me, but some of my colleagues who need to use the same database are working in 2003 which is why I need to replace any broken references with good ones.

I've loaded there working references into the same tale and I'm reading them back via a query read into a recordset built on the fly in VBA.

Does reference.name exist in 2003 (I can't remember the exact error)?