gpt-3.5-turbo-0613· openai
Suzhen/CodeChat
I want a VBA code that will go through all the sheets in workbook 'Service Providers'and…
- 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.
- 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 SubHere's how to run the code:
- Open your workbook "Service Providers.xlsx".
- Press "Alt + F11" to open the VBA editor.
- Insert a new module by clicking on "Insert" > "Module".
- Copy and paste the above code into the module.
- Modify the file name if it's different from "Service Providers.xlsx".
- Save the workbook.
- 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.
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.