Sub UpdateMasterSheet()
Dim masterWB As Workbook
Dim masterWS As Worksheet
Dim sourceWB As Workbook
Dim sourceWS As Worksheet
Dim folderPath As String
Dim folder As Object
Dim subFolder As Object
Dim pipingFolder As Object
Dim file As Object
Dim fs As Object
Dim targetRow As Long
Dim lastCol As Long
Dim maxCorrosionRate As Double
Dim nextInspectionDate As Date
Dim lastInspectionDate As Date ' Added variable for Last Inspection Date
Dim pdfFilePath As String
' Set the path to the main folder
folderPath = "C:\Users\MICHAEL SERRAO\Desktop\Final Inspection plan\" ' Update the path as
needed
' Create FileSystemObject
Set fs = CreateObject("Scripting.FileSystemObject")
' Open the master workbook and set the target worksheet
Set masterWB = Workbooks("Non-Insulated Piping Inspection Master Sheet.xlsx") ' Ensure the
master workbook is open
Set masterWS = masterWB.Sheets("Piping Inspection Master Sheet") ' Ensure this sheet exists
' Start writing data from row 3
targetRow = 3
' Loop through each folder in the parent folder
For Each folder In fs.GetFolder(folderPath).Subfolders
If folder.Name Like "HP-*" Then ' Process only subfolders like HP-0, HP-1, etc.
' Loop through subfolders in Set 3 (fluid folders)
For Each subFolder In folder.Subfolders
' Loop through subfolders in Set 4 (piping folders)
For Each pipingFolder In subFolder.Subfolders
' Process files in piping folders
For Each file In pipingFolder.Files
If LCase(Right(file.Name, 5)) = ".xlsx" Then ' Process only Excel files
Debug.Print "Processing file: " & file.Path ' Log the file being processed
' Attempt to open the source workbook
On Error Resume Next
Set sourceWB = Workbooks.Open(file.Path)
If sourceWB Is Nothing Then
Debug.Print "Failed to open: " & file.Path
On Error GoTo 0
GoTo NextFile
End If
On Error GoTo 0
' Process the "Pipe Line Data" sheet
If SheetExists(sourceWB, "Pipe Line Data") Then
Set sourceWS = sourceWB.Sheets("Pipe Line Data")
masterWS.Cells(targetRow, 1).Value = sourceWS.Range("C6").Value ' Tag no.
masterWS.Cells(targetRow, 2).Value = sourceWS.Range("C9").Value ' Master
Diameter
masterWS.Cells(targetRow, 3).Value = sourceWS.Range("C7").Value ' Fluid
Abbreviation
masterWS.Cells(targetRow, 4).Value = sourceWS.Range("C8").Value ' Fluid Family
masterWS.Cells(targetRow, 5).Value = sourceWS.Range("C10").Value ' Piping Class
' Add a hyperlink to the Drawing No. (assumes PDF file is in the same folder)
pdfFilePath = pipingFolder.Path & "\" & sourceWS.Range("C13").Value & ".pdf"
masterWS.Hyperlinks.Add _
Anchor:=masterWS.Cells(targetRow, 6), _
Address:=pdfFilePath, _
TextToDisplay:=sourceWS.Range("C13").Value ' Drawing No.
masterWS.Cells(targetRow, 7).Value = sourceWS.Range("C12").Value ' P&ID
Else
Debug.Print "Sheet 'Pipe Line Data' not found in: " & file.Path
End If
' Process the "Maintenance Activities" sheet
If SheetExists(sourceWB, "Maintenance Activities") Then
Set sourceWS = sourceWB.Sheets("Maintenance Activities")
masterWS.Cells(targetRow, 8).Value = sourceWS.Range("E6").Value ' Insulation
removal
masterWS.Cells(targetRow, 9).Value = sourceWS.Range("E11").Value ' Scaffolding
Requirement
Else
Debug.Print "Sheet 'Maintenance Activities' not found in: " & file.Path
End If
' Process the "CML READING" sheet
If SheetExists(sourceWB, "CML READING") Then
Set sourceWS = sourceWB.Sheets("CML READING")
' Find the last column with data in row 10 starting from column H
lastCol = sourceWS.Cells(10, sourceWS.Columns.Count).End(xlToLeft).Column
' Column K: Max Corrosion Rate (Find max value between columns 5 and 6 of the
target column)
If lastCol >= 8 Then ' Check if columns H, I, J, K have data
' Find the maximum of target column 5 and 6
maxCorrosionRate = Application.WorksheetFunction.Max(sourceWS.Cells(5,
lastCol).Value, sourceWS.Cells(6, lastCol).Value)
masterWS.Cells(targetRow, 11).Value = maxCorrosionRate ' Max Corrosion Rate
End If
' Column L: Next Inspection Date (target column + 1, row 8)
If lastCol >= 8 Then ' Check if there are enough columns to get the next inspection
date
nextInspectionDate = sourceWS.Cells(8, lastCol + 1).Value
masterWS.Cells(targetRow, 12).Value = nextInspectionDate ' Next Inspection
Date
End If
' Column J: Last Inspection Date (target column, row 8)
If lastCol >= 8 Then ' Ensure there is data in the last column
lastInspectionDate = sourceWS.Cells(8, lastCol).Value
masterWS.Cells(targetRow, 10).Value = lastInspectionDate ' Last Inspection Date
End If
Else
Debug.Print "Sheet 'CML READING' not found in: " & file.Path
End If
' Increment the target row
targetRow = targetRow + 1
' Close the source workbook
sourceWB.Close SaveChanges:=False
End If
NextFile:
Next file
Next pipingFolder
Next subFolder
End If
Next folder
MsgBox "Data collection completed!"
End Sub
' Function to check if a sheet exists in a workbook
Function SheetExists(wb As Workbook, sheetName As String) As Boolean
Dim ws As Worksheet
On Error Resume Next
Set ws = wb.Sheets(sheetName)
SheetExists = Not ws Is Nothing
On Error GoTo 0
End Function
