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?
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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
 

RichardMGreen

Well-known Member
Joined
Feb 20, 2006
Messages
2,177
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)?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,686
Messages
5,597,536
Members
414,153
Latest member
SandraC

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
Top