Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0613· OpenAIAi2 WildChat-4.8M (AI2)

All the documents linked to in my workbook 'Service Providers' are stored in this…

  1. User

    All the documents linked to in my workbook 'Service Providers' are stored in this location 'G:\Shared drives\Swan School Site Premises\PREMISES MANAGEMENT\SERVICE PROVIDERS\zzzz ServProvDocs' Most of the links are accessed through VBA codes. I want to first create a table of all the files stored in sheet 'Linked Docs' in the following way. Column A, the Name of the document Column B, the file type i.e. docm, docx, pdf, xlsm, xlsx, csv, etc.

  2. Assistant

    To create a table of all the files stored in the specified location in Excel VBA, you can use the following code:

    Sub CreateFileTable()
        Dim folderPath As String
        Dim files As Object
        Dim file As Object
        Dim rowNum As Long
        
        'Set the folder path
        folderPath = "G:\Shared drives\Swan School Site Premises\PREMISES MANAGEMENT\SERVICE PROVIDERS\zzzz ServProvDocs"
        
        'Clear previous data from the Linked Docs sheet
        Sheets("Linked Docs").Range("A:B").Clear
        
        'Get the list of files in the folder
        Set files = CreateObject("Scripting.FileSystemObject").GetFolder(folderPath).Files
        
        'Set the starting row number
        rowNum = 2
        
        'Loop through each file and add to the table
        For Each file In files
            'Check if the file is not a folder
            If Not file.Attributes = 16 Then
                'Add the file name to column A
                Sheets("Linked Docs").Cells(rowNum, 1).Value = file.Name
                'Add the file type to column B
                Sheets("Linked Docs").Cells(rowNum, 2).Value = Right(file.Name, Len(file.Name) - InStrRev(file.Name, "."))
                'Increment the row number
                rowNum = rowNum + 1
            End If
        Next file
    End Sub
    

    You can adjust the folderPath variable to match your specific folder location. This code assumes that the worksheet "Linked Docs" already exists in your workbook.

    To use this code, follow these steps:

    1. Press Alt+F11 to open the VBA Editor.
    2. Insert a new module by clicking Insert > Module.
    3. Copy and paste the above code into the new module.
    4. Modify the folderPath variable to match your folder location if necessary.
    5. Run the code by pressing F5 or by going to the "Developer" tab and clicking "Macros" and selecting "CreateFileTable".

    After running the code, the "Linked Docs" sheet will be populated with the file names and corresponding file types from the specified folder location.

  3. User

    The VBA code worked perfectly. I now have a list of files in the said folder with Name of file in column A and FIle type in Column B. What I would like to do now is as follows. For each file name, find the sheet that contains a reference to it and list the sheet Name in column C. If another sheet also has a reference to the document then list the other sheet name in column D, and so on. Most of the references to files will be in the sheet VBA code.

  4. Assistant

    To find the sheets that contain references to each file in the "Linked Docs" sheet and list them in columns C, D, etc., you can use the following code:

    Sub FindReferencingSheets()
        Dim linkedDocsSheet As Worksheet
        Dim fileRow As Long
        Dim fileName As String
        Dim referencingSheets As String
        Dim sheet As Worksheet
        Dim codeModule As Object
        Dim lineNum As Long
        
        'Set the "Linked Docs" sheet
        Set linkedDocsSheet = ThisWorkbook.Worksheets("Linked Docs")
        
        'Loop through each file name in column A
        For fileRow = 2 To linkedDocsSheet.Cells(Rows.Count, "A").End(xlUp).Row
            'Get the file name
            fileName = linkedDocsSheet.Cells(fileRow, "A").Value
            
            'Clear previous referencing sheets
            linkedDocsSheet.Cells(fileRow, "C").Resize(1, 100).ClearContents
            
            'Loop through each sheet in the workbook
            For Each sheet In ThisWorkbook.Sheets
                'Check if the sheet has a VBA project
                If sheet.Type = xlWorksheet And sheet.CodeName <> "Linked_Docs" Then
                    Set codeModule = sheet.CodeModule
                    lineNum = 1
                    
                    'Search for the file name in the sheet's code
                    Do While lineNum < codeModule.CountOfLines
                        lineNum = codeModule.Find(fileName, lineNum, 1, -1, -1, False, False, False)
                        
                        'If the file name is found in the code
                        If lineNum > 0 Then
                            'Add the sheet name to the referencing sheets
                            referencingSheets = referencingSheets & sheet.Name & ","
                            lineNum = lineNum + 1
                        End If
                    Loop
                End If
            Next sheet
            
            'Remove trailing comma and add referencing sheets to column C onwards
            If Len(referencingSheets) > 0 Then
                referencingSheets = Left(referencingSheets, Len(referencingSheets) - 1)
                linkedDocsSheet.Cells(fileRow, "C").Value = referencingSheets
            End If
            
            'Reset referencing sheets variable for the next file
            referencingSheets = ""
        Next fileRow
    End Sub
    

    Here's how to use this code:

    1. Press Alt+F11 to open the VBA Editor.
    2. Insert a new module by clicking Insert > Module.
    3. Copy and paste the above code into the new module.
    4. Run the code by pressing F5 or by going to the "Developer" tab and clicking "Macros" and selecting "FindReferencingSheets".

    After running the code, the "Linked Docs" sheet will be updated with the referencing sheets for each file. The referencing sheets will be listed in columns C, D, etc. based on the number of referencing sheets for each file. If a file has no referencing sheets, the corresponding cell will be empty.

  5. User

    I am getting an error 'Method or data member not found' on '.codeModule' in the line 'Set codeModule = sheet.codeModule'

  6. Assistant

    Apologies for the confusion. I realize that the CodeModule property is not available for worksheets but only for modules and user forms.

    To modify the code and handle sheets that contain VBA code, you can use the VBComponents property to access the modules associated with each sheet. Here's the updated code:

    Sub FindReferencingSheets()
        Dim linkedDocsSheet As Worksheet
        Dim fileRow As Long
        Dim fileName As String
        Dim referencingSheets As String
        Dim sheet As Worksheet
        Dim vbComp As Object
        Dim lineNum As Long
        
        'Set the "Linked Docs" sheet
        Set linkedDocsSheet = ThisWorkbook.Worksheets("Linked Docs")
        
        'Loop through each file name in column A
        For fileRow = 2 To linkedDocsSheet.Cells(Rows.Count, "A").End(xlUp).Row
            'Get the file name
            fileName = linkedDocsSheet.Cells(fileRow, "A").Value
            
            'Clear previous referencing sheets
            linkedDocsSheet.Cells(fileRow, "C").Resize(1, 100).ClearContents
            
            'Loop through each sheet in the workbook
            For Each sheet In ThisWorkbook.Sheets
                'Check if the sheet has a VBA component
                If sheet.Type = xlWorksheet And sheet.CodeName <> "Linked_Docs" Then
                    '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 = 1 Then
                            Set codeModule = vbComp.CodeModule
                            lineNum = 1
                            
                            'Search for the file name in the module's code
                            Do While lineNum < codeModule.CountOfLines
                                lineNum = codeModule.Find(fileName, lineNum, 1, -1, -1, False, False, False)
                                
                                'If the file name is found in the code
                                If lineNum > 0 Then
                                    'Add the sheet name to the referencing sheets
                                    referencingSheets = referencingSheets & sheet.Name & ","
                                    lineNum = lineNum + 1
                                End If
                            Loop
                        End If
                    Next vbComp
                End If
            Next sheet
            
            'Remove trailing comma and add referencing sheets to column C onwards
            If Len(referencingSheets) > 0 Then
                referencingSheets = Left(referencingSheets, Len(referencingSheets) - 1)
                linkedDocsSheet.Cells(fileRow, "C").Value = referencingSheets
            End If
            
            'Reset referencing sheets variable for the next file
            referencingSheets = ""
        Next fileRow
    End Sub
    

    Please replace the previous code with this updated code, and then run the FindReferencingSheets macro again. If there are any further issues, please let me know.

  7. User

    When I run the vba code, there are no errors. However, it appears that I am stuck in a loop

  8. Assistant

    Apologies for the inconvenience caused. It seems there was an oversight in the code. To address the loop issue, modify the loop conditions and exit the loop once a match is found. Here's the updated code:

    Sub FindReferencingSheets()
        Dim linkedDocsSheet As Worksheet
        Dim fileRow As Long
        Dim fileName As String
        Dim referencingSheets As String
        Dim sheet As Worksheet
        Dim vbComp As Object
        Dim lineNum As Long
        Dim foundMatch As Boolean
        
        'Set the "Linked Docs" sheet
        Set linkedDocsSheet = ThisWorkbook.Worksheets("Linked Docs")
        
        'Loop through each file name in column A
        For fileRow = 2 To linkedDocsSheet.Cells(Rows.Count, "A").End(xlUp).Row
            'Get the file name
            fileName = linkedDocsSheet.Cells(fileRow, "A").Value
            
            'Clear previous referencing sheets
            linkedDocsSheet.Cells(fileRow, "C").Resize(1, 100).ClearContents
            
            'Loop through each sheet in the workbook
            For Each sheet In ThisWorkbook.Sheets
                'Check if the sheet has a VBA component
                If sheet.Type = xlWorksheet And sheet.CodeName <> "Linked_Docs" Then
                    '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 = 1 Then
                            Set codeModule = vbComp.CodeModule
                            lineNum = 1
                            foundMatch = False
                            
                            'Search for the file name in the module's code
                            Do While lineNum < codeModule.CountOfLines And Not foundMatch
                                lineNum = codeModule.Find(fileName, lineNum, 1, -1, -1, False, False, False)
                                
                                'If the file name is found in the code
                                If lineNum > 0 Then
                                    'Add the sheet name to the referencing sheets
                                    referencingSheets = referencingSheets & sheet.Name & ","
                                    foundMatch = True
                                End If
                                
                                'Increment the line number
                                lineNum = lineNum + 1
                            Loop
                        End If
                    Next vbComp
                End If
            Next sheet
            
            'Remove trailing comma and add referencing sheets to column C onwards
            If Len(referencingSheets) > 0 Then
                referencingSheets = Left(referencingSheets, Len(referencingSheets) - 1)
                linkedDocsSheet.Cells(fileRow, "C").Value = referencingSheets
            End If
            
            'Reset referencing sheets variable for the next file
            referencingSheets = ""
        Next fileRow
    End Sub
    

    Please replace the previous code with this updated code, and then run the FindReferencingSheets macro again. It should now exit the loop once a match is found and resolve the issue you were facing with an infinite loop. Let me know if you encounter any further issues.

  9. User

    It appears that I am either stuck in a loop or it will take a few minutes to complete the code.

  10. Assistant

    I apologize for the inconvenience. The loop might be time-consuming if there are a large number of sheets or modules in your workbook. To optimize the code and speed up the process, you can add an additional check to skip empty modules or modules with no code lines. Here's the updated code:

    Sub FindReferencingSheets()
        Dim linkedDocsSheet As Worksheet
        Dim fileRow As Long
        Dim fileName As String
        Dim referencingSheets As String
        Dim sheet As Worksheet
        Dim vbComp As Object
        Dim codeLines As Long
        Dim lineNum As Long
        Dim foundMatch As Boolean
        
        'Set the "Linked Docs" sheet
        Set linkedDocsSheet = ThisWorkbook.Worksheets("Linked Docs")
        
        'Loop through each file name in column A
        For fileRow = 2 To linkedDocsSheet.Cells(Rows.Count, "A").End(xlUp).Row
            'Get the file name
            fileName = linkedDocsSheet.Cells(fileRow, "A").Value
            
            'Clear previous referencing sheets
            linkedDocsSheet.Cells(fileRow, "C").Resize(1, 100).ClearContents
            
            'Loop through each sheet in the workbook
            For Each sheet In ThisWorkbook.Sheets
                'Check if the sheet has a VBA component
                If sheet.Type = xlWorksheet And sheet.CodeName <> "Linked_Docs" Then
                    '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 = 1 Then
                            codeLines = vbComp.CodeModule.CountOfLines
                            
                            'Skip empty modules or modules with no code lines
                            If codeLines > 0 Then
                                Set codeModule = vbComp.CodeModule
                                lineNum = 1
                                foundMatch = False
                                
                                'Search for the file name in the module's code
                                Do While lineNum <= codeLines And Not foundMatch
                                    lineNum = codeModule.Find(fileName, lineNum, 1, -1, -1, False, False, False)
                                    
                                    'If the file name is found in the code
                                    If lineNum > 0 Then
                                        'Add the sheet name to the referencing sheets
                                        referencingSheets = referencingSheets & sheet.Name & ","
                                        foundMatch = True
                                    End If
                                    
                                    'Increment the line number
                                    lineNum = lineNum + 1
                                Loop
                            End If
                        End If
                    Next vbComp
                End If
            Next sheet
            
            'Remove trailing comma and add referencing sheets to column C onwards
            If Len(referencingSheets) > 0 Then
                referencingSheets = Left(referencingSheets, Len(referencingSheets) - 1)
                linkedDocsSheet.Cells(fileRow, "C").Value = referencingSheets
            End If
            
            'Reset referencing sheets variable for the next file
            referencingSheets = ""
        Next fileRow
    End Sub
    

    Please replace the previous code with this updated code, and then try running the FindReferencingSheets macro again. It should skip empty modules or modules with no code lines, which can significantly improve the processing time. Let me know if the issue persists or if you have any further questions.

  11. User

    Is there a way to restrict the search to sheets where the sheet name is more than three 3 letters

  12. Assistant

    Yes, you can modify the code to search only in sheets where the sheet name is more than three letters. Here's the updated code:

    Sub FindReferencingSheets()
        Dim linkedDocsSheet As Worksheet
        Dim fileRow As Long
        Dim fileName As String
        Dim referencingSheets As String
        Dim sheet As Worksheet
        Dim vbComp As Object
        Dim codeLines As Long
        Dim lineNum As Long
        Dim foundMatch As Boolean
        
        'Set the "Linked Docs" sheet
        Set linkedDocsSheet = ThisWorkbook.Worksheets("Linked Docs")
        
        'Loop through each file name in column A
        For fileRow = 2 To linkedDocsSheet.Cells(Rows.Count, "A").End(xlUp).Row
            'Get the file name
            fileName = linkedDocsSheet.Cells(fileRow, "A").Value
            
            'Clear previous referencing sheets
            linkedDocsSheet.Cells(fileRow, "C").Resize(1, 100).ClearContents
            
            'Loop through each sheet in the workbook
            For Each sheet In ThisWorkbook.Sheets
                'Check if the sheet has a VBA component
                If sheet.Type = xlWorksheet And sheet.CodeName <> "Linked_Docs" Then
                    'Check if the sheet name is more than three letters
                    If Len(sheet.Name) > 3 Then
                        '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 = 1 Then
                                codeLines = vbComp.CodeModule.CountOfLines
                                
                                'Skip empty modules or modules with no code lines
                                If codeLines > 0 Then
                                    Set codeModule = vbComp.CodeModule
                                    lineNum = 1
                                    foundMatch = False
                                    
                                    'Search for the file name in the module's code
                                    Do While lineNum <= codeLines And Not foundMatch
                                        lineNum = codeModule.Find(fileName, lineNum, 1, -1, -1, False, False, False)
                                        
                                        'If the file name is found in the code
                                        If lineNum > 0 Then
                                            'Add the sheet name to the referencing sheets
                                            referencingSheets = referencingSheets & sheet.Name & ","
                                            foundMatch = True
                                        End If
                                        
                                        'Increment the line number
                                        lineNum = lineNum + 1
                                    Loop
                                End If
                            End If
                        Next vbComp
                    End If
                End If
            Next sheet
            
            'Remove trailing comma and add referencing sheets to column C onwards
            If Len(referencingSheets) > 0 Then
                referencingSheets = Left(referencingSheets, Len(referencingSheets) - 1)
                linkedDocsSheet.Cells(fileRow, "C").Value = referencingSheets
            End If
            
            'Reset referencing sheets variable for the next file
            referencingSheets = ""
        Next fileRow
    End Sub
    

    Please replace the previous code with this