My workbook ‘Service Providers’ has about fifty sheets. In the VBA coding of the sheets,…
- 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.
- 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 SubTo use this code, follow these steps:
- Press ALT + F11 to open the VBA editor in Excel.
- Insert a new module by clicking on "Insert" > "Module".
- Copy and paste the above code into the new module.
- 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.
- User
I am getting a Type Mismatch error on the line 'If InStr(1, cell.Value, "G:\Shared drives") > 0 Then'
- 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 Thenas follows:If InStr(1, CStr(cell.Value), "G:\Shared drives") > 0 ThenBy using the
CStrfunction, we convert the cell value to a string before applying theInStrfunction. 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 SubPlease try running the updated code and let me know if you encounter any further issues.
- User
No errors, but the code did not list any files found
- Assistant
Apologies for the oversight. The issue might be that the
UsedRangeproperty 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 SubPlease run the updated code and let me know if it correctly lists the address paths in the 'Linked Docs' worksheet.
- User
All the paths are in the VBA coding of each sheet
- 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 SubPlease 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.
- 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)'
- Assistant
I apologize for the confusion. The error occurs because the
CodeModuleproperty 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
VBComponentsproperty 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 SubPlease 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.
- 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.
- Assistant
Apologies for the confusion. To search and retrieve information from the VBA code of the sheets, we need to access the
CodeNameproperty 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 SubPlease 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.
- User
The code did not retrive any data
- Assistant
Apologies for the confusion. To search for address paths within the VBA code of sheets, we will need to access the
CodeModuleof the sheet object, similar to how we access theCodeModuleof 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 SubPlease 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.
- 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
- Assistant
Apologies for the confusion once again. It seems that accessing the
CodeModulefor worksheet objects is not possible using VBA.To work around this limitation, we can use the
OLEObjectscollection 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
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.