Access VBA References

RichardMGreen

Well-known Member
Joined
Feb 20, 2006
Messages
2,177
Hi all

I've got the following code which lists all the ticked references in Excel:-
Code:
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?
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
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:
Code:
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
 
Upvote 0
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)?
 
Upvote 0

Forum statistics

Threads
1,211,800
Messages
6,104,041
Members
447,891
Latest member
Edson Santos 294

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top