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

I have the following VBA code that searches all the VBA in my workbook 'Service…

  1. User

    I have the following VBA code that searches all the VBA in my workbook 'Service Providers' for file paths that start "G:\Shared drives". The code is effective and accurate. Can you please wirte me a similar code that seraches the workbook for file paths outside the workbook.

    Sub SearchVBALinkedDocs() 'SEARCHES VBA OF ALL SHEETS FOR LINK PATH TO G 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 wb = ThisWorkbook Set linkedDocsWs = wb.Sheets("Linked Docs") For Each ws In wb.Sheets On Error Resume Next lineIndex = 1 ' Check if the sheet has a code module If ws.Type = xlWorksheet Then ' Get the module code of the sheet moduleCode = wb.VBProject.VBComponents(ws.CodeName).codeModule.Lines(1, wb.VBProject.VBComponents(ws.CodeName).codeModule.CountOfLines) 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) lastRow = linkedDocsWs.Cells(linkedDocsWs.Rows.Count, "D").End(xlUp).Row + 1 linkedDocsWs.Cells(lastRow, "D").Value = addressPath linkedDocsWs.Cells(lastRow, "E").Value = "'" & ws.Name End If startPos = InStr(startPos + 1, moduleCode, "G:\Shared drives") Loop End If On Error GoTo 0 Next ws Call Module32.SearchMODLinkedDocs End Sub

  2. Assistant

    Sub SearchVBAExternalLinks() 'SEARCHES VBA OF ALL SHEETS FOR EXTERNAL LINK PATHS Dim wb As Workbook Dim externalLinksWs 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 wb = ThisWorkbook Set externalLinksWs = wb.Sheets("External Links") For Each ws In wb.Sheets On Error Resume Next lineIndex = 1 ' Check if the sheet has a code module If ws.Type = xlWorksheet Then ' Get the module code of the sheet moduleCode = wb.VBProject.VBComponents(ws.CodeName).codeModule.Lines(1, wb.VBProject.VBComponents(ws.CodeName).codeModule.CountOfLines) startPos = InStr(1, moduleCode, "G:") Do Until startPos = 0 endPos = InStr(startPos, moduleCode, """") If endPos > startPos Then addressPath = Mid(moduleCode, startPos, endPos - startPos) If Not InStr(1, addressPath, ThisWorkbook.Path) > 0 Then ' Exclude paths within the workbook lastRow = externalLinksWs.Cells(externalLinksWs.Rows.Count, "D").End(xlUp).Row + 1 externalLinksWs.Cells(lastRow, "D").Value = addressPath externalLinksWs.Cells(lastRow, "E").Value = "'" & ws.Name End If End If startPos = InStr(startPos + 1, moduleCode, "G:") Loop End If On Error GoTo 0 Next ws Call Module32.SearchMODExternalLinks End Sub

  3. User

    error subscript out of range

  4. Assistant

    The error "subscript out of range" typically occurs when you're trying to access an object or element that does not exist. In this case, it seems like the error is occurring when trying to set the worksheet object externalLinksWs to the worksheet "External Links".

    Please ensure that you have a worksheet named "External Links" in your workbook. If the worksheet does not exist, you'll need to create it before running the code.

    If the worksheet already exists, make sure that the name is entered correctly with the correct capitalization. Worksheet names are case-sensitive.

    Here's an example of how you can create the "External Links" worksheet:

    Set externalLinksWs = Worksheets.Add
    externalLinksWs.Name = "External Links"
    

    Make sure to include this code before the loop that searches for external link paths.

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