Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0613· openaiHugging Face Suzhen/CodeChat

My workbook ‘Service Providers’ has about fifty sheets. In the VBA coding of the sheets,…

  1. User

    My workbook ‘Service Providers’ has about fifty sheets. In the VBA coding of the sheets, there are links to external files which are stored in ‘G:\Shared drives’ as in the examples below: "G:\Shared drives\Swan School Site Premises\PREMISES MANAGEMENT\SERVICE PROVIDERS\Service Providers History.xlsx" "G:\Shared drives\Swan School Site Premises\PREMISES MANAGEMENT\SERVICE PROVIDERS\zzzz ServProvDocs\ThankYouTask.docm" I want a VBA that will search all the sheets, and for each address paths found, list the address path in a sheet named ‘Linked Docs’ in column L starting from L2. The location where the address path is found will also be listed in column M.

  2. Assistant

    You can use the following VBA code to search all the sheets in your workbook and list the address paths in a sheet named 'Linked Docs' in columns L and M:

    Sub SearchLinkedDocs()
        Dim ws As Worksheet
        Dim linkedDocsWs As Worksheet
        Dim lastRow As Long
        Dim cell As Range
        Dim addressPath As String
        
        ' Set the 'Linked Docs' worksheet
        Set linkedDocsWs = ThisWorkbook.Sheets("Linked Docs")
        
        ' Clear the previous data in 'Linked Docs' worksheet
        linkedDocsWs.Range("L2:M" & linkedDocsWs.Cells(Rows.Count, "L").End(xlUp).Row).ClearContents
        
        ' Loop through all the sheets in the workbook
        For Each ws In ThisWorkbook.Sheets
            ' Skip the 'Linked Docs' worksheet
            If ws.Name <> "Linked Docs" Then
                ' Loop through all the cells in the sheet
                For Each cell In ws.UsedRange.Cells
                    ' Check if the cell contains an address path
                    If InStr(1, cell.Value, "G:\Shared drives") > 0 Then
                        ' Get the address path
                        addressPath = Replace(cell.Value, "G:\Shared drives\", "")
                        
                        ' Find the last row in 'Linked Docs' worksheet
                        lastRow = linkedDocsWs.Cells(Rows.Count, "L").End(xlUp).Row + 1
                        
                        ' Add the address path and sheet name to 'Linked Docs' worksheet
                        linkedDocsWs.Cells(lastRow, "L").Value = addressPath
                        linkedDocsWs.Cells(lastRow, "M").Value = "'" & ws.Name
                    End If
                Next cell
            End If
        Next ws
    End Sub
    

    To use this code, follow these steps:

    1. Press ALT + F11 to open the VBA editor in Excel.
    2. Insert a new module by clicking on "Insert" > "Module".
    3. Copy and paste the above code into the new module.
    4. Press F5 or run the macro manually to execute the code.

    This will search all the sheets in your workbook for address paths containing "G:\Shared drives" and list them in the 'Linked Docs' sheet in columns L and M. The address paths will start from cell L2, and the corresponding sheet names will be listed in column M.

  3. User

    I am getting a Type Mismatch error on the line 'If InStr(1, cell.Value, "G:\Shared drives") > 0 Then'

  4. Assistant

    Apologies for the error. The Type Mismatch error occurs when there is a mismatch between the variable type and the data type being compared. In this case, it is likely because the cell value you are checking is not a string.

    To resolve the issue, you can modify the line If InStr(1, cell.Value, "G:\Shared drives") > 0 Then as follows:

    If InStr(1, CStr(cell.Value), "G:\Shared drives") > 0 Then
    

    By using the CStr function, we convert the cell value to a string before applying the InStr function. This should prevent the Type Mismatch error.

    Here's the updated version of the code:

    Sub SearchLinkedDocs()
        Dim ws As Worksheet
        Dim linkedDocsWs As Worksheet
        Dim lastRow As Long
        Dim cell As Range
        Dim addressPath As String
        
        ' Set the 'Linked Docs' worksheet
        Set linkedDocsWs = ThisWorkbook.Sheets("Linked Docs")
        
        ' Clear the previous data in 'Linked Docs' worksheet
        linkedDocsWs.Range("L2:M" & linkedDocsWs.Cells(Rows.Count, "L").End(xlUp).Row).ClearContents
        
        ' Loop through all the sheets in the workbook
        For Each ws In ThisWorkbook.Sheets
            ' Skip the 'Linked Docs' worksheet
            If ws.Name <> "Linked Docs" Then
                ' Loop through all the cells in the sheet
                For Each cell In ws.UsedRange.Cells
                    ' Check if the cell contains an address path
                    If InStr(1, CStr(cell.Value), "G:\Shared drives") > 0 Then
                        ' Get the address path
                        addressPath = Replace(cell.Value, "G:\Shared drives\", "")
                        
                        ' Find the last row in 'Linked Docs' worksheet
                        lastRow = linkedDocsWs.Cells(Rows.Count, "L").End(xlUp).Row + 1
                        
                        ' Add the address path and sheet name to 'Linked Docs' worksheet
                        linkedDocsWs.Cells(lastRow, "L").Value = addressPath
                        linkedDocsWs.Cells(lastRow, "M").Value = "'" & ws.Name
                    End If
                Next cell
            End If
        Next ws
    End Sub
    

    Please try running the updated code and let me know if you encounter any further issues.

  5. User

    No errors, but the code did not list any files found

  6. Assistant

    Apologies for the oversight. The issue might be that the UsedRange property is not properly identifying all the cells with values on each sheet. To resolve this, we can loop through all the rows and columns to search for the address paths.

    Please try the updated code below:

    Sub SearchLinkedDocs()
        Dim ws As Worksheet
        Dim linkedDocsWs As Worksheet
        Dim lastRow As Long
        Dim row As Long
        Dim col As Long
        Dim addressPath As String
        
        ' Set the 'Linked Docs' worksheet
        Set linkedDocsWs = ThisWorkbook.Sheets("Linked Docs")
        
        ' Clear the previous data in 'Linked Docs' worksheet
        linkedDocsWs.Range("L2:M" & linkedDocsWs.Cells(Rows.Count, "L").End(xlUp).Row).ClearContents
        
        ' Loop through all the sheets in the workbook
        For Each ws In ThisWorkbook.Sheets
            ' Skip the 'Linked Docs' worksheet
            If ws.Name <> "Linked Docs" Then
                ' Loop through all the rows and columns in the sheet
                For row = 1 To ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
                    For col = 1 To ws.Cells(row, ws.Columns.Count).End(xlToLeft).Column
                        ' Check if the cell contains an address path
                        If InStr(1, CStr(ws.Cells(row, col).Value), "G:\Shared drives") > 0 Then
                            ' Get the address path
                            addressPath = Replace(ws.Cells(row, col).Value, "G:\Shared drives\", "")
                            
                            ' Find the last row in 'Linked Docs' worksheet
                            lastRow = linkedDocsWs.Cells(Rows.Count, "L").End(xlUp).Row + 1
                            
                            ' Add the address path and sheet name to 'Linked Docs' worksheet
                            linkedDocsWs.Cells(lastRow, "L").Value = addressPath
                            linkedDocsWs.Cells(lastRow, "M").Value = "'" & ws.Name
                        End If
                    Next col
                Next row
            End If
        Next ws
    End Sub
    

    Please run the updated code and let me know if it correctly lists the address paths in the 'Linked Docs' worksheet.

  7. User

    All the paths are in the VBA coding of each sheet

  8. Assistant

    Apologies for the confusion. If the address paths are in the VBA coding of each sheet, we need to modify the code to access the VBA module of each sheet and search for the address paths within the code.

    Please use the modified code below:

    Sub SearchLinkedDocs()
        Dim ws As Worksheet
        Dim linkedDocsWs As Worksheet
        Dim lastRow As Long
        Dim moduleCode As String
        Dim addressPath As String
        Dim startPos As Long
        Dim endPos As Long
        
        ' Set the 'Linked Docs' worksheet
        Set linkedDocsWs = ThisWorkbook.Sheets("Linked Docs")
        
        ' Clear the previous data in 'Linked Docs' worksheet
        linkedDocsWs.Range("L2:M" & linkedDocsWs.Cells(Rows.Count, "L").End(xlUp).Row).ClearContents
        
        ' Loop through all the sheets in the workbook
        For Each ws In ThisWorkbook.Sheets
            ' Skip the 'Linked Docs' worksheet
            If ws.Name <> "Linked Docs" Then
                On Error Resume Next
                ' Get the VBA module code of the sheet
                moduleCode = ws.CodeModule.Lines(1, ws.CodeModule.CountOfLines)
                
                ' Search for address paths in the VBA module code
                startPos = InStr(1, moduleCode, "G:\Shared drives")
                Do Until startPos = 0
                    endPos = InStr(startPos, moduleCode, """")
                    If endPos > startPos Then
                        addressPath = Mid(moduleCode, startPos, endPos - startPos)
                        
                        ' Find the last row in 'Linked Docs' worksheet
                        lastRow = linkedDocsWs.Cells(Rows.Count, "L").End(xlUp).Row + 1
                        
                        ' Add the address path and sheet name to 'Linked Docs' worksheet
                        linkedDocsWs.Cells(lastRow, "L").Value = addressPath
                        linkedDocsWs.Cells(lastRow, "M").Value = "'" & ws.Name
                    End If
                    startPos = InStr(startPos + 1, moduleCode, "G:\Shared drives")
                Loop
                On Error GoTo 0
            End If
        Next ws
    End Sub
    

    Please try running the updated code and let me know if it successfully searches for the address paths within the VBA coding of each sheet and lists them in the 'Linked Docs' worksheet.

  9. User

    When I run the code I am getting error 'Method or data member not found' on '.codeModule' in the line 'moduleCode = ws.codeModule.Lines(1, ws.codeModule.CountOfLines)'

  10. Assistant

    I apologize for the confusion. The error occurs because the CodeModule property is not available for worksheets. It is only available for workbook modules, sheet modules, and user forms.

    To search for the address paths within the VBA project of each sheet, you can use the VBComponents property of the workbook to access the code within each sheet module. Here's the updated code:

    Sub SearchLinkedDocs()
        Dim wb As Workbook
        Dim linkedDocsWs As Worksheet
        Dim lastRow As Long
        Dim component As Object
        Dim moduleCode As String
        Dim addressPath As String
        Dim startPos As Long
        Dim endPos As Long
        
        ' Set the workbook
        Set wb = ThisWorkbook
        
        ' Set the 'Linked Docs' worksheet
        Set linkedDocsWs = wb.Sheets("Linked Docs")
        
        ' Clear the previous data in 'Linked Docs' worksheet
        linkedDocsWs.Range("L2:M" & linkedDocsWs.Cells(Rows.Count, "L").End(xlUp).Row).ClearContents
        
        ' Loop through all the components (sheet modules) in the workbook
        For Each component In wb.VBProject.VBComponents
            ' Check if the component is a sheet module (1 = vbext_ct_Document)
            If component.Type = 1 Then
                On Error Resume Next
                ' Get the module code of the sheet
                moduleCode = component.CodeModule.Lines(1, component.CodeModule.CountOfLines)
                
                ' Search for address paths in the module code
                startPos = InStr(1, moduleCode, "G:\Shared drives")
                Do Until startPos = 0
                    endPos = InStr(startPos, moduleCode, """")
                    If endPos > startPos Then
                        addressPath = Mid(moduleCode, startPos, endPos - startPos)
                        
                        ' Find the last row in 'Linked Docs' worksheet
                        lastRow = linkedDocsWs.Cells(Rows.Count, "L").End(xlUp).Row + 1
                        
                        ' Add the address path and sheet name to 'Linked Docs' worksheet
                        linkedDocsWs.Cells(lastRow, "L").Value = addressPath
                        linkedDocsWs.Cells(lastRow, "M").Value = "'" & component.Name
                    End If
                    startPos = InStr(startPos + 1, moduleCode, "G:\Shared drives")
                Loop
                On Error GoTo 0
            End If
        Next component
    End Sub
    

    Please try running the updated code and let me know if it correctly searches for the address paths within the VBA project of each sheet and lists them in the 'Linked Docs' worksheet.

  11. User

    The amended code searched and retrieved the information required but only the Modules in the workbook. It did not search the VBA code of the sheets in the workbook.

  12. Assistant

    Apologies for the confusion. To search and retrieve information from the VBA code of the sheets, we need to access the CodeName property of the worksheet object to access the sheet module code.

    Please try the updated code below:

    Sub SearchLinkedDocs()
        Dim wb As Workbook
        Dim linkedDocsWs As Worksheet
        Dim lastRow As Long
        Dim ws As Worksheet
        Dim moduleCode As String
        Dim addressPath As String
        Dim startPos As Long
        Dim endPos As Long
        
        ' Set the workbook
        Set wb = ThisWorkbook
        
        ' Set the 'Linked Docs' worksheet
        Set linkedDocsWs = wb.Sheets("Linked Docs")
        
        ' Clear the previous data in 'Linked Docs' worksheet
        linkedDocsWs.Range("L2:M" & linkedDocsWs.Cells(Rows.Count, "L").End(xlUp).Row).ClearContents
        
        ' Loop through all the sheets in the workbook
        For Each ws In wb.Sheets
            On Error Resume Next
            ' Get the module code of the sheet
            moduleCode = ws.CodeName
            
            ' Search for address paths in the module code
            startPos = InStr(1, moduleCode, "G:\Shared drives")
            Do Until startPos = 0
                endPos = InStr(startPos, moduleCode, """")
                If endPos > startPos Then
                    addressPath = Mid(moduleCode, startPos, endPos - startPos)
                    
                    ' Find the last row in 'Linked Docs' worksheet
                    lastRow = linkedDocsWs.Cells(Rows.Count, "L").End(xlUp).Row + 1
                    
                    ' Add the address path and sheet name to 'Linked Docs' worksheet
                    linkedDocsWs.Cells(lastRow, "L").Value = addressPath
                    linkedDocsWs.Cells(lastRow, "M").Value = "'" & ws.Name
                End If
                startPos = InStr(startPos + 1, moduleCode, "G:\Shared drives")
            Loop
            On Error GoTo 0
        Next ws
    End Sub
    

    Please run the updated code and let me know if it successfully searches for the address paths within the VBA code of each sheet and lists them in the 'Linked Docs' worksheet.

  13. User

    The code did not retrive any data

  14. Assistant

    Apologies for the confusion. To search for address paths within the VBA code of sheets, we will need to access the CodeModule of the sheet object, similar to how we access the CodeModule of modules in the workbook.

    Please try the updated code below:

    Sub SearchLinkedDocs()
        Dim wb As Workbook
        Dim linkedDocsWs As Worksheet
        Dim lastRow As Long
        Dim ws As Worksheet
        Dim moduleCode As String
        Dim addressPath As String
        Dim lineIndex As Long
        Dim startPos As Long
        Dim endPos As Long
        
        ' Set the workbook
        Set wb = ThisWorkbook
        
        ' Set the 'Linked Docs' worksheet
        Set linkedDocsWs = wb.Sheets("Linked Docs")
        
        ' Clear the previous data in 'Linked Docs' worksheet
        linkedDocsWs.Range("L2:M" & linkedDocsWs.Cells(Rows.Count, "L").End(xlUp).Row).ClearContents
        
        ' Loop through all the sheets in the workbook
        For Each ws In wb.Sheets
            On Error Resume Next
            lineIndex = 1
            ' Get the module code of the sheet
            Do Until ws.CodeModule.ProcStartLine(lineIndex, 0) = 0
                moduleCode = ws.CodeModule.Lines(ws.CodeModule.ProcStartLine(lineIndex, 0), ws.CodeModule.ProcCountLines(ws.CodeModule.ProcStartLine(lineIndex, 0), 0))
                
                ' Search for address paths in the module code
                startPos = InStr(1, moduleCode, "G:\Shared drives")
                Do Until startPos = 0
                    endPos = InStr(startPos, moduleCode, """")
                    If endPos > startPos Then
                        addressPath = Mid(moduleCode, startPos, endPos - startPos)
                        
                        ' Find the last row in 'Linked Docs' worksheet
                        lastRow = linkedDocsWs.Cells(linkedDocsWs.Rows.Count, "L").End(xlUp).Row + 1
                        
                        ' Add the address path and sheet name to 'Linked Docs' worksheet
                        linkedDocsWs.Cells(lastRow, "L").Value = addressPath
                        linkedDocsWs.Cells(lastRow, "M").Value = "'" & ws.Name
                    End If
                    startPos = InStr(startPos + 1, moduleCode, "G:\Shared drives")
                Loop
                
                lineIndex = lineIndex + 1
            Loop
            On Error GoTo 0
        Next ws
    End Sub
    

    Please run the updated code and verify if it correctly searches for the address paths within the VBA code of each sheet and lists them in the 'Linked Docs' worksheet.

  15. User

    I am getting an Error - Method or data member not found - on .codeModule - in the line - Do Until ws.codeModule.ProcStartLine(lineIndex, 0) = 0

  16. Assistant

    Apologies for the confusion once again. It seems that accessing the CodeModule for worksheet objects is not possible using VBA.

    To work around this limitation, we can use the OLEObjects collection to search for embedded objects within the worksheet, which may include linked files. Here's an updated code snippet to perform the search:

    Sub SearchLinkedDocs()
        Dim wb As Workbook
        Dim linkedDocsWs As Worksheet
    

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.