Can you write me a VBA code that will list all linked file names in my workbook and the nmae of the sheet or module in which they are found.
I would like the list in my sheet ‘Linked Docs’ with the file names in column L and the location in column M
Certainly! Here’s a VBA code that will list all linked file names in your workbook, along with the names of the sheets or modules in which they are found. The list will be placed in the “Linked Docs” sheet, with the file names in column L and the locations in column M.

Sub ListLinkedFiles()
    Dim linkedDocsSheet As Worksheet
    Dim linkedFiles As New Collection
    Dim sheet As Worksheet
    Dim vbComp As Object
    
    'Set the “Linked Docs” sheet
    Set linkedDocsSheet = ThisWorkbook.Worksheets(“Linked Docs”)
    
    'Clear previous list
    linkedDocsSheet.Range(“L:M”).ClearContents
    
    'Loop through each sheet in the workbook
    For Each sheet In ThisWorkbook.Sheets
        'Loop through each VBA component in the sheet
        For Each vbComp In sheet.Parent.VBProject.VBComponents
            'Check if VBA component is a module
            If vbComp.Type = vbext_ct_StdModule Then
                'Loop through each line of the module’s code
                Dim codeLines As Long
                codeLines = vbComp.CodeModule.CountOfLines
                Dim lineNum As Long
                For lineNum = 1 To codeLines
                    'Get the code line
                    Dim codeLine As String
                    codeLine = vbComp.CodeModule.Lines(lineNum, 1)
                    
                    'Check if the code line contains a file name
                    Dim fileName As String
                    fileName = GetLinkedFileName(codeLine)
                    If fileName <> “” Then
                        'Add the file name and location to the collection
                        linkedFiles.Add Array(fileName, sheet.Name)
                    End If
                Next lineNum
            End If
        Next vbComp
    Next sheet
    
    'Output the linked file names and locations in “Linked Docs” sheet
    Dim fileRow As Long
    For fileRow = 1 To linkedFiles.Count
        linkedDocsSheet.Cells(fileRow, “L”).Value = linkedFiles(fileRow)(0)
        linkedDocsSheet.Cells(fileRow, “M”).Value = linkedFiles(fileRow)(1)
    Next fileRow
End Sub

Function GetLinkedFileName(codeLine As String) As String
    Dim fileName As String
    fileName = “”
    
    'Check if the code line contains a file name pattern
    Dim regex As Object
    Set regex = CreateObject(“VBScript.RegExp”)
    regex.Pattern = “”“[^”“]+.[^”“]+”“”
    regex.Global = True
    
    'If a file name pattern is found in the code line, extract the file name
    If regex.test(codeLine) Then
        Dim matches As Object
        Set matches = regex.Execute(codeLine)
        fileName = Mid(matches(0).Value, 2, Len(matches(0).Value) - 2)
    End If
    
    GetLinkedFileName = fileName
End Function


To use this code, simply run the ListLinkedFiles macro. It will populate the “Linked Docs” sheet with the linked file names and their locations. The GetLinkedFileName function is used to extract file names from code lines, assuming that the file names are enclosed in double quotes.

Please note that this code may not capture all possible ways that file names can be referenced in your VBA code. It assumes a basic pattern of file names enclosed in double quotes. You may need to customize the code to match the specific naming conventions used in your VBA code.
The VBA works but it gives me more information that I actually need.
The information I want in column L are files that in G: drive as shown in the example below
G:\Shared drives\Swan School Site Premises\PREMISES MANAGEMENT\SERVICE PROVIDERS\Auxillary.xlsm
Understood. To modify the code to filter for specific file paths, we can adjust the GetLinkedFileName function to consider only file paths that start with “G:”. Here’s the updated code:

Sub ListLinkedFiles()
    Dim linkedDocsSheet As Worksheet
    Dim linkedFiles As New Collection
    Dim sheet As Worksheet
    Dim vbComp As Object
    
    'Set the “Linked Docs” sheet
    Set linkedDocsSheet = ThisWorkbook.Worksheets(“Linked Docs”)
    
    'Clear previous list
    linkedDocsSheet.Range(“L:M”).ClearContents
    
    'Loop through each sheet in the workbook
    For Each sheet In ThisWorkbook.Sheets
        'Loop through each VBA component in the sheet
        For Each vbComp In sheet.Parent.VBProject.VBComponents
            'Check if VBA component is a module
            If vbComp.Type = vbext_ct_StdModule Then
                'Loop through each line of the module’s code
                Dim codeLines As Long
                codeLines = vbComp.CodeModule.CountOfLines
                Dim lineNum As Long
                For lineNum = 1 To codeLines
                    'Get the code line
                    Dim codeLine As String
                    codeLine = vbComp.CodeModule.Lines(lineNum, 1)
                    
                    'Check if the code line contains a “G:” file path
                    Dim filePath As String
                    filePath = GetLinkedFilePath(codeLine)
                    If Left(filePath, 3) = “G:” Then
                        'Add the file path and location to the collection
                        linkedFiles.Add Array(filePath, sheet.Name)
                    End If
                Next lineNum
            End If
        Next vbComp
    Next sheet
    
    'Output the linked file paths and locations in “Linked Docs” sheet
    Dim fileRow As Long
    For fileRow = 1 To linkedFiles.Count
        linkedDocsSheet.Cells(fileRow, “L”).Value = linkedFiles(fileRow)(0)
        linkedDocsSheet.Cells(fileRow, “M”).Value = linkedFiles(fileRow)(1)
    Next fileRow
End Sub

Function GetLinkedFilePath(codeLine As String) As String
    Dim filePath As String
    filePath = “”
    
    'Check if the code line contains a file path pattern
    Dim regex As Object
    Set regex = CreateObject(“VBScript.RegExp”)
    regex.Pattern = “G:\[^”“]+”
    regex.Global = True
    
    'If a file path pattern is found in the code line, extract the file path
    If regex.Test(codeLine) Then
        Dim matches As Object
        Set matches = regex.Execute(codeLine)
        filePath = matches(0).Value
    End If
    
    GetLinkedFilePath = filePath
End Function


In this updated code, the GetLinkedFilePath function uses a regular expression to look for file paths starting with “G:”. Only file
The amendment did not list any files
When I run the amended code it does not list any files