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

I want a VBA code that will go through all the sheets in workbook 'Service Providers'and…

  1. User

    I want a VBA code that will go through all the sheets in workbook 'Service Providers'and find all linked documents that are stored in G: drive. It will list the name of the linked document in colum L starting from L2 and in column M it will insert the sheet name where the file name was found.

  2. Assistant

    Here is a VBA code that will fulfill your requirements:

    Sub FindLinkedDocuments()
        Dim wb As Workbook
        Dim ws As Worksheet
        Dim linkPath As String
        Dim fileName As String
        Dim targetSheet As String
        Dim rowNum As Long
        
        ' Set the workbook object
        Set wb = Workbooks("Service Providers.xlsx")
        
        ' Loop through all sheets in the workbook
        For Each ws In wb.Sheets
            ' Get the sheet name
            targetSheet = ws.Name
            
            ' Reset the row number for each sheet
            rowNum = 2
            
            ' Loop through each cell with a formula in the sheet
            For Each cel In ws.UsedRange.Cells.SpecialCells(xlCellTypeFormulas)
                ' Check if the formula is a hyperlink that starts with "G:"
                If cel.Hyperlinks.Count > 0 Then
                    If Left(cel.Hyperlinks(1).Address, 2) = "G:" Then
                        ' Get the linked path and file name
                        linkPath = Split(cel.Hyperlinks(1).Address, "\")(0)
                        fileName = Split(cel.Hyperlinks(1).Address, "\")(UBound(Split(cel.Hyperlinks(1).Address, "\")))
                        
                        ' Output the linked document name and sheet name to columns L and M respectively
                        ws.Range("L" & rowNum).Value = fileName
                        ws.Range("M" & rowNum).Value = targetSheet
                        
                        ' Increment the row number
                        rowNum = rowNum + 1
                    End If
                End If
            Next cel
        Next ws
    End Sub
    

    Here's how to run the code:

    1. Open your workbook "Service Providers.xlsx".
    2. Press "Alt + F11" to open the VBA editor.
    3. Insert a new module by clicking on "Insert" > "Module".
    4. Copy and paste the above code into the module.
    5. Modify the file name if it's different from "Service Providers.xlsx".
    6. Save the workbook.
    7. Press "F5" to run the code.

    The code will go through each sheet in the workbook and find all linked documents stored in the G: drive. The file names will be listed in column L starting from L2, and the sheet names will be inserted in column M where the file names were found.

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