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

I want a VBA code that will search all the sheets and their respective VBA coding in my…

  1. User

    I want a VBA code that will search all the sheets and their respective VBA coding in my workbook 'Service Providers' and extract the line of text that begins G: within the apostrohes " " The data must then be listed in sheet 'Linked Docs' in column L staring from L2 with the location of each find in column M. The sheet 'Linked Docs' must be omitted from the search.

  2. Assistant

    Here is the VBA code that you can use to achieve the desired outcome:

    Sub ExtractFilePaths()
        Dim wb As Workbook
        Dim ws As Worksheet
        Dim searchWs As Worksheet
        Dim searchSheetName As String
        Dim codeModule As CodeModule
        Dim lineNum As Long
        Dim codeLine As String
        Dim filePath As String
        Dim startIdx As Long
        Dim endIdx As Long
        Dim resultRow As Long
        
        ' Set the workbook and worksheet variables
        Set wb = ThisWorkbook
        Set searchWs = wb.Sheets("Linked Docs")
        searchSheetName = searchWs.Name
        
        ' Clear the existing data in the 'Linked Docs' sheet
        searchWs.Range("L2:M" & searchWs.Cells(searchWs.Rows.Count, "L").End(xlUp).Row).ClearContents
        
        ' Set the initial result row
        resultRow = 2
        
        ' Loop through all the sheets in the workbook
        For Each ws In wb.Sheets
            ' Skip the 'Linked Docs' sheet
            If ws.Name <> searchSheetName Then
                ' Loop through the code modules in the sheet
                For Each codeModule In ws.VBProject.VBComponents(ws.CodeName).CodeModules
                    ' Get the total number of lines in the code module
                    lineNum = codeModule.CountOfLines
                    
                    ' Loop through each line in the code module
                    For i = 1 To lineNum
                        codeLine = codeModule.Lines(i, 1)
                        
                        ' Check if the line starts with 'G:'
                        If Left(Trim(codeLine), 2) = "G:" Then
                            ' Extract the file path between the apostrophes
                            startIdx = InStr(codeLine, """")
                            endIdx = InStrRev(codeLine, """")
                            filePath = Mid(codeLine, startIdx + 1, endIdx - startIdx - 1)
                            
                            ' Write the file path and its location to the 'Linked Docs' sheet
                            searchWs.Range("L" & resultRow).Value = filePath
                            searchWs.Range("M" & resultRow).Value = "'" & ws.Name & "'!A" & i
    
                            ' Increment the result row
                            resultRow = resultRow + 1
                        End If
                    Next i
                Next codeModule
            End If
        Next ws
    End Sub
    

    You can assign this code to a button or run it from the VBA Editor. It will search through all the sheets and their respective VBA coding in the workbook named 'Service Providers' and extract the line of text that begins with "G:" and is enclosed within the apostrophes (""). The extracted file path will be listed in column L of the 'Linked Docs' sheet, and the location of each find will be listed in column M. The 'Linked Docs' sheet will be omitted from the search.

    Make sure to save your workbook before running the code, as it may modify the file.

    Note: Please update the sheet names and workbook name as per your actual workbook.

  3. User

    Error Method or data member not found on 'ws.VBProject' on the line 'For Each codeModule In ws.VBProject.VBComponents(ws.CodeName).CodeModules'

  4. Assistant

    Apologies for the oversight. The error you encountered is likely because your workbook does not have the appropriate security settings enabled to access the VBA Project. To resolve this issue, you can follow these steps:

    1. Open the Excel file where you want to run the VBA code.
    2. Go to the "Developer" tab on the Excel ribbon. If the "Developer" tab is not visible, you'll need to enable it by going to "File" > "Options" > "Customize Ribbon" and checking the "Developer" option.
    3. Click on the "Visual Basic" button in the "Code" group. This will open the VBA Editor window.
    4. In the VBA Editor, go to "Tools" > "References" and make sure the following reference is checked: "Microsoft Visual Basic for Applications Extensibility x.x" (the version number can vary, but it usually starts with 5.3).

    After enabling the appropriate reference, you should be able to run the code without encountering the "Method or data member not found" error.

    Let me know if you need further assistance!

  5. User

    In my workbook 'Service Providers', search all worksheets including their respective VBA coding, and find all the text between 'G:' ending with either '.docm' or '.docx' or '.txt' or '.csv' or '.xlsx' or '.xlsm'. The list will be copied to column L starting from L2 in sheet 'Linked Docs' and the location of each find copied to column M. Please omit the sheet 'Linked Docs' from the search

  6. Assistant

    Certainly! Here's an updated version of the VBA code that searches for the specified text between 'G:' and the file extensions you mentioned, across all worksheets including their VBA coding. It then copies the findings to column L starting from L2 in the 'Linked Docs' sheet, along with their locations in column M. The 'Linked Docs' sheet is excluded from the search:

    Sub ExtractFilePaths()
        Dim wb As Workbook
        Dim ws As Worksheet
        Dim searchWs As Worksheet
        Dim searchSheetName As String
        Dim codeModule As Object
        Dim lineNum As Long
        Dim codeLine As String
        Dim filePath As String
        Dim startIdx As Long
        Dim endIdx As Long
        Dim resultRow As Long
        
        ' Set the workbook and worksheet variables
        Set wb = ThisWorkbook
        Set searchWs = wb.Sheets("Linked Docs")
        searchSheetName = searchWs.Name
        
        ' Clear the existing data in the 'Linked Docs' sheet
        searchWs.Range("L2:M" & searchWs.Cells(searchWs.Rows.Count, "L").End(xlUp).Row).ClearContents
        
        ' Set the initial result row
        resultRow = 2
        
        ' Loop through all the sheets in the workbook
        For Each ws In wb.Sheets
            ' Skip the 'Linked Docs' sheet
            If ws.Name <> searchSheetName Then
                ' Loop through the code modules in the sheet
                For Each codeModule In ws.CodeName
                    ' Get the total number of lines in the code module
                    With codeModule
                    lineNum = .CountOfLines
                    
                    ' Loop through each line in the code module
                    For i = 1 To lineNum
                        codeLine = .Lines(i, 1)
                        
                        ' Check if the line starts with 'G:' and ends with supported extensions
                        If Left(Trim(codeLine), 2) = "G:" And (Right(codeLine, 5) = ".docm" Or Right(codeLine, 5) = ".docx" Or Right(codeLine, 4) = ".txt" Or Right(codeLine, 4) = ".csv" Or Right(codeLine, 5) = ".xlsx" Or Right(codeLine, 5) = ".xlsm") Then
                            ' Extract the file path between the apostrophes
                            startIdx = InStr(codeLine, """")
                            endIdx = InStrRev(codeLine, """")
                            filePath = Mid(codeLine, startIdx + 1, endIdx - startIdx - 1)
                            
                            ' Write the file path and its location to the 'Linked Docs' sheet
                            searchWs.Range("L" & resultRow).Value = filePath
                            searchWs.Range("M" & resultRow).Value = "'" & ws.Name & "'!A" & i
    
                            ' Increment the result row
                            resultRow = resultRow + 1
                        End If
                    Next i
                    End With
                Next codeModule
            End If
        Next ws
    End Sub
    

    Make sure to adjust the sheet names according to your workbook. Please let me know if you need any further assistance!

  7. User

    Error 'For each may only iterate over a collection object or an array' on 'ws.CodeName' in the line 'For Each codeModule In ws.CodeName'

  8. Assistant

    Apologies for the confusion. The error occurs because ws.CodeName is not a collection or an array. To access the code modules in a worksheet, you can use the VBComponents property of the VBProject. Here's the updated code:

    Sub ExtractFilePaths()
        Dim wb As Workbook
        Dim ws As Worksheet
        Dim searchWs As Worksheet
        Dim searchSheetName As String
        Dim codeModule As Object
        Dim lineNum As Long
        Dim codeLine As String
        Dim filePath As String
        Dim startIdx As Long
        Dim endIdx As Long
        Dim resultRow As Long
        
        ' Set the workbook and worksheet variables
        Set wb = ThisWorkbook
        Set searchWs = wb.Sheets("Linked Docs")
        searchSheetName = searchWs.Name
        
        ' Clear the existing data in the 'Linked Docs' sheet
        searchWs.Range("L2:M" & searchWs.Cells(searchWs.Rows.Count, "L").End(xlUp).Row).ClearContents
        
        ' Set the initial result row
        resultRow = 2
        
        ' Loop through all the sheets in the workbook
        For Each ws In wb.Sheets
            ' Skip the 'Linked Docs' sheet
            If ws.Name <> searchSheetName Then
                ' Loop through the code modules in the sheet
                For Each codeModule In ws.VBProject.VBComponents
                    ' Get the total number of lines in the code module
                    With codeModule.CodeModule
                        lineNum = .CountOfLines
                        
                        ' Loop through each line in the code module
                        For i = 1 To lineNum
                            codeLine = .Lines(i, 1)
                            
                            ' Check if the line starts with 'G:' and ends with supported extensions
                            If Left(Trim(codeLine), 2) = "G:" And (Right(codeLine, 5) = ".docm" Or Right(codeLine, 5) = ".docx" Or Right(codeLine, 4) = ".txt" Or Right(codeLine, 4) = ".csv" Or Right(codeLine, 5) = ".xlsx" Or Right(codeLine, 5) = ".xlsm") Then
                                ' Extract the file path between the apostrophes
                                startIdx = InStr(codeLine, """")
                                endIdx = InStrRev(codeLine, """")
                                filePath = Mid(codeLine, startIdx + 1, endIdx - startIdx - 1)
                                
                                ' Write the file path and its location to the 'Linked Docs' sheet
                                searchWs.Range("L" & resultRow).Value = filePath
                                searchWs.Range("M" & resultRow).Value = "'" & ws.Name & "'!A" & i
                                
                                ' Increment the result row
                                resultRow = resultRow + 1
                            End If
                        Next i
                    End With
                Next codeModule
            End If
        Next ws
    End Sub
    

    Please ensure that you adjust the sheet names according to your workbook. Let me know if you need any further assistance!

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