All the documents linked to in my workbook 'Service Providers' are stored in this…
- User
All the documents linked to in my workbook 'Service Providers' are stored in this location 'G:\Shared drives\Swan School Site Premises\PREMISES MANAGEMENT\SERVICE PROVIDERS\zzzz ServProvDocs' Most of the links are accessed through VBA codes. I want to first create a table of all the files stored in sheet 'Linked Docs' in the following way. Column A, the Name of the document Column B, the file type i.e. docm, docx, pdf, xlsm, xlsx, csv, etc.
- Assistant
To create a table of all the files stored in the specified location in Excel VBA, you can use the following code:
Sub CreateFileTable() Dim folderPath As String Dim files As Object Dim file As Object Dim rowNum As Long 'Set the folder path folderPath = "G:\Shared drives\Swan School Site Premises\PREMISES MANAGEMENT\SERVICE PROVIDERS\zzzz ServProvDocs" 'Clear previous data from the Linked Docs sheet Sheets("Linked Docs").Range("A:B").Clear 'Get the list of files in the folder Set files = CreateObject("Scripting.FileSystemObject").GetFolder(folderPath).Files 'Set the starting row number rowNum = 2 'Loop through each file and add to the table For Each file In files 'Check if the file is not a folder If Not file.Attributes = 16 Then 'Add the file name to column A Sheets("Linked Docs").Cells(rowNum, 1).Value = file.Name 'Add the file type to column B Sheets("Linked Docs").Cells(rowNum, 2).Value = Right(file.Name, Len(file.Name) - InStrRev(file.Name, ".")) 'Increment the row number rowNum = rowNum + 1 End If Next file End SubYou can adjust the folderPath variable to match your specific folder location. This code assumes that the worksheet "Linked Docs" already exists in your workbook.
To use this code, follow these steps:
- Press Alt+F11 to open the VBA Editor.
- Insert a new module by clicking Insert > Module.
- Copy and paste the above code into the new module.
- Modify the folderPath variable to match your folder location if necessary.
- Run the code by pressing F5 or by going to the "Developer" tab and clicking "Macros" and selecting "CreateFileTable".
After running the code, the "Linked Docs" sheet will be populated with the file names and corresponding file types from the specified folder location.
- User
The VBA code worked perfectly. I now have a list of files in the said folder with Name of file in column A and FIle type in Column B. What I would like to do now is as follows. For each file name, find the sheet that contains a reference to it and list the sheet Name in column C. If another sheet also has a reference to the document then list the other sheet name in column D, and so on. Most of the references to files will be in the sheet VBA code.
- Assistant
To find the sheets that contain references to each file in the "Linked Docs" sheet and list them in columns C, D, etc., you can use the following code:
Sub FindReferencingSheets() Dim linkedDocsSheet As Worksheet Dim fileRow As Long Dim fileName As String Dim referencingSheets As String Dim sheet As Worksheet Dim codeModule As Object Dim lineNum As Long 'Set the "Linked Docs" sheet Set linkedDocsSheet = ThisWorkbook.Worksheets("Linked Docs") 'Loop through each file name in column A For fileRow = 2 To linkedDocsSheet.Cells(Rows.Count, "A").End(xlUp).Row 'Get the file name fileName = linkedDocsSheet.Cells(fileRow, "A").Value 'Clear previous referencing sheets linkedDocsSheet.Cells(fileRow, "C").Resize(1, 100).ClearContents 'Loop through each sheet in the workbook For Each sheet In ThisWorkbook.Sheets 'Check if the sheet has a VBA project If sheet.Type = xlWorksheet And sheet.CodeName <> "Linked_Docs" Then Set codeModule = sheet.CodeModule lineNum = 1 'Search for the file name in the sheet's code Do While lineNum < codeModule.CountOfLines lineNum = codeModule.Find(fileName, lineNum, 1, -1, -1, False, False, False) 'If the file name is found in the code If lineNum > 0 Then 'Add the sheet name to the referencing sheets referencingSheets = referencingSheets & sheet.Name & "," lineNum = lineNum + 1 End If Loop End If Next sheet 'Remove trailing comma and add referencing sheets to column C onwards If Len(referencingSheets) > 0 Then referencingSheets = Left(referencingSheets, Len(referencingSheets) - 1) linkedDocsSheet.Cells(fileRow, "C").Value = referencingSheets End If 'Reset referencing sheets variable for the next file referencingSheets = "" Next fileRow End SubHere's how to use this code:
- Press Alt+F11 to open the VBA Editor.
- Insert a new module by clicking Insert > Module.
- Copy and paste the above code into the new module.
- Run the code by pressing F5 or by going to the "Developer" tab and clicking "Macros" and selecting "FindReferencingSheets".
After running the code, the "Linked Docs" sheet will be updated with the referencing sheets for each file. The referencing sheets will be listed in columns C, D, etc. based on the number of referencing sheets for each file. If a file has no referencing sheets, the corresponding cell will be empty.
- User
I am getting an error 'Method or data member not found' on '.codeModule' in the line 'Set codeModule = sheet.codeModule'
- Assistant
Apologies for the confusion. I realize that the
CodeModuleproperty is not available for worksheets but only for modules and user forms.To modify the code and handle sheets that contain VBA code, you can use the
VBComponentsproperty to access the modules associated with each sheet. Here's the updated code:Sub FindReferencingSheets() Dim linkedDocsSheet As Worksheet Dim fileRow As Long Dim fileName As String Dim referencingSheets As String Dim sheet As Worksheet Dim vbComp As Object Dim lineNum As Long 'Set the "Linked Docs" sheet Set linkedDocsSheet = ThisWorkbook.Worksheets("Linked Docs") 'Loop through each file name in column A For fileRow = 2 To linkedDocsSheet.Cells(Rows.Count, "A").End(xlUp).Row 'Get the file name fileName = linkedDocsSheet.Cells(fileRow, "A").Value 'Clear previous referencing sheets linkedDocsSheet.Cells(fileRow, "C").Resize(1, 100).ClearContents 'Loop through each sheet in the workbook For Each sheet In ThisWorkbook.Sheets 'Check if the sheet has a VBA component If sheet.Type = xlWorksheet And sheet.CodeName <> "Linked_Docs" Then 'Loop through each VBA component in the sheet For Each vbComp In sheet.Parent.VBProject.VBComponents 'Check if VBA component is a module If vbComp.Type = 1 Then Set codeModule = vbComp.CodeModule lineNum = 1 'Search for the file name in the module's code Do While lineNum < codeModule.CountOfLines lineNum = codeModule.Find(fileName, lineNum, 1, -1, -1, False, False, False) 'If the file name is found in the code If lineNum > 0 Then 'Add the sheet name to the referencing sheets referencingSheets = referencingSheets & sheet.Name & "," lineNum = lineNum + 1 End If Loop End If Next vbComp End If Next sheet 'Remove trailing comma and add referencing sheets to column C onwards If Len(referencingSheets) > 0 Then referencingSheets = Left(referencingSheets, Len(referencingSheets) - 1) linkedDocsSheet.Cells(fileRow, "C").Value = referencingSheets End If 'Reset referencing sheets variable for the next file referencingSheets = "" Next fileRow End SubPlease replace the previous code with this updated code, and then run the
FindReferencingSheetsmacro again. If there are any further issues, please let me know. - User
When I run the vba code, there are no errors. However, it appears that I am stuck in a loop
- Assistant
Apologies for the inconvenience caused. It seems there was an oversight in the code. To address the loop issue, modify the loop conditions and exit the loop once a match is found. Here's the updated code:
Sub FindReferencingSheets() Dim linkedDocsSheet As Worksheet Dim fileRow As Long Dim fileName As String Dim referencingSheets As String Dim sheet As Worksheet Dim vbComp As Object Dim lineNum As Long Dim foundMatch As Boolean 'Set the "Linked Docs" sheet Set linkedDocsSheet = ThisWorkbook.Worksheets("Linked Docs") 'Loop through each file name in column A For fileRow = 2 To linkedDocsSheet.Cells(Rows.Count, "A").End(xlUp).Row 'Get the file name fileName = linkedDocsSheet.Cells(fileRow, "A").Value 'Clear previous referencing sheets linkedDocsSheet.Cells(fileRow, "C").Resize(1, 100).ClearContents 'Loop through each sheet in the workbook For Each sheet In ThisWorkbook.Sheets 'Check if the sheet has a VBA component If sheet.Type = xlWorksheet And sheet.CodeName <> "Linked_Docs" Then 'Loop through each VBA component in the sheet For Each vbComp In sheet.Parent.VBProject.VBComponents 'Check if VBA component is a module If vbComp.Type = 1 Then Set codeModule = vbComp.CodeModule lineNum = 1 foundMatch = False 'Search for the file name in the module's code Do While lineNum < codeModule.CountOfLines And Not foundMatch lineNum = codeModule.Find(fileName, lineNum, 1, -1, -1, False, False, False) 'If the file name is found in the code If lineNum > 0 Then 'Add the sheet name to the referencing sheets referencingSheets = referencingSheets & sheet.Name & "," foundMatch = True End If 'Increment the line number lineNum = lineNum + 1 Loop End If Next vbComp End If Next sheet 'Remove trailing comma and add referencing sheets to column C onwards If Len(referencingSheets) > 0 Then referencingSheets = Left(referencingSheets, Len(referencingSheets) - 1) linkedDocsSheet.Cells(fileRow, "C").Value = referencingSheets End If 'Reset referencing sheets variable for the next file referencingSheets = "" Next fileRow End SubPlease replace the previous code with this updated code, and then run the
FindReferencingSheetsmacro again. It should now exit the loop once a match is found and resolve the issue you were facing with an infinite loop. Let me know if you encounter any further issues. - User
It appears that I am either stuck in a loop or it will take a few minutes to complete the code.
- Assistant
I apologize for the inconvenience. The loop might be time-consuming if there are a large number of sheets or modules in your workbook. To optimize the code and speed up the process, you can add an additional check to skip empty modules or modules with no code lines. Here's the updated code:
Sub FindReferencingSheets() Dim linkedDocsSheet As Worksheet Dim fileRow As Long Dim fileName As String Dim referencingSheets As String Dim sheet As Worksheet Dim vbComp As Object Dim codeLines As Long Dim lineNum As Long Dim foundMatch As Boolean 'Set the "Linked Docs" sheet Set linkedDocsSheet = ThisWorkbook.Worksheets("Linked Docs") 'Loop through each file name in column A For fileRow = 2 To linkedDocsSheet.Cells(Rows.Count, "A").End(xlUp).Row 'Get the file name fileName = linkedDocsSheet.Cells(fileRow, "A").Value 'Clear previous referencing sheets linkedDocsSheet.Cells(fileRow, "C").Resize(1, 100).ClearContents 'Loop through each sheet in the workbook For Each sheet In ThisWorkbook.Sheets 'Check if the sheet has a VBA component If sheet.Type = xlWorksheet And sheet.CodeName <> "Linked_Docs" Then 'Loop through each VBA component in the sheet For Each vbComp In sheet.Parent.VBProject.VBComponents 'Check if VBA component is a module If vbComp.Type = 1 Then codeLines = vbComp.CodeModule.CountOfLines 'Skip empty modules or modules with no code lines If codeLines > 0 Then Set codeModule = vbComp.CodeModule lineNum = 1 foundMatch = False 'Search for the file name in the module's code Do While lineNum <= codeLines And Not foundMatch lineNum = codeModule.Find(fileName, lineNum, 1, -1, -1, False, False, False) 'If the file name is found in the code If lineNum > 0 Then 'Add the sheet name to the referencing sheets referencingSheets = referencingSheets & sheet.Name & "," foundMatch = True End If 'Increment the line number lineNum = lineNum + 1 Loop End If End If Next vbComp End If Next sheet 'Remove trailing comma and add referencing sheets to column C onwards If Len(referencingSheets) > 0 Then referencingSheets = Left(referencingSheets, Len(referencingSheets) - 1) linkedDocsSheet.Cells(fileRow, "C").Value = referencingSheets End If 'Reset referencing sheets variable for the next file referencingSheets = "" Next fileRow End SubPlease replace the previous code with this updated code, and then try running the
FindReferencingSheetsmacro again. It should skip empty modules or modules with no code lines, which can significantly improve the processing time. Let me know if the issue persists or if you have any further questions. - User
Is there a way to restrict the search to sheets where the sheet name is more than three 3 letters
- Assistant
Yes, you can modify the code to search only in sheets where the sheet name is more than three letters. Here's the updated code:
Sub FindReferencingSheets() Dim linkedDocsSheet As Worksheet Dim fileRow As Long Dim fileName As String Dim referencingSheets As String Dim sheet As Worksheet Dim vbComp As Object Dim codeLines As Long Dim lineNum As Long Dim foundMatch As Boolean 'Set the "Linked Docs" sheet Set linkedDocsSheet = ThisWorkbook.Worksheets("Linked Docs") 'Loop through each file name in column A For fileRow = 2 To linkedDocsSheet.Cells(Rows.Count, "A").End(xlUp).Row 'Get the file name fileName = linkedDocsSheet.Cells(fileRow, "A").Value 'Clear previous referencing sheets linkedDocsSheet.Cells(fileRow, "C").Resize(1, 100).ClearContents 'Loop through each sheet in the workbook For Each sheet In ThisWorkbook.Sheets 'Check if the sheet has a VBA component If sheet.Type = xlWorksheet And sheet.CodeName <> "Linked_Docs" Then 'Check if the sheet name is more than three letters If Len(sheet.Name) > 3 Then 'Loop through each VBA component in the sheet For Each vbComp In sheet.Parent.VBProject.VBComponents 'Check if VBA component is a module If vbComp.Type = 1 Then codeLines = vbComp.CodeModule.CountOfLines 'Skip empty modules or modules with no code lines If codeLines > 0 Then Set codeModule = vbComp.CodeModule lineNum = 1 foundMatch = False 'Search for the file name in the module's code Do While lineNum <= codeLines And Not foundMatch lineNum = codeModule.Find(fileName, lineNum, 1, -1, -1, False, False, False) 'If the file name is found in the code If lineNum > 0 Then 'Add the sheet name to the referencing sheets referencingSheets = referencingSheets & sheet.Name & "," foundMatch = True End If 'Increment the line number lineNum = lineNum + 1 Loop End If End If Next vbComp End If End If Next sheet 'Remove trailing comma and add referencing sheets to column C onwards If Len(referencingSheets) > 0 Then referencingSheets = Left(referencingSheets, Len(referencingSheets) - 1) linkedDocsSheet.Cells(fileRow, "C").Value = referencingSheets End If 'Reset referencing sheets variable for the next file referencingSheets = "" Next fileRow End SubPlease replace the previous code with this