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("[Link]")
' Open the master workbook and set the target worksheet
Set masterWB = Workbooks("Non-Insulated Piping Inspection Master [Link]") ' Ensure the
master workbook is open
Set masterWS = [Link]("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 [Link](folderPath).Subfolders
If [Link] Like "HP-*" Then ' Process only subfolders like HP-0, HP-1, etc.
' Loop through subfolders in Set 3 (fluid folders)
For Each subFolder In [Link]
' Loop through subfolders in Set 4 (piping folders)
For Each pipingFolder In [Link]
' Process files in piping folders
For Each file In [Link]
If LCase(Right([Link], 5)) = ".xlsx" Then ' Process only Excel files
[Link] "Processing file: " & [Link] ' Log the file being processed
' Attempt to open the source workbook
On Error Resume Next
Set sourceWB = [Link]([Link])
If sourceWB Is Nothing Then
[Link] "Failed to open: " & [Link]
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 = [Link]("Pipe Line Data")
[Link](targetRow, 1).Value = [Link]("C6").Value ' Tag no.
[Link](targetRow, 2).Value = [Link]("C9").Value ' Master
Diameter
[Link](targetRow, 3).Value = [Link]("C7").Value ' Fluid
Abbreviation
[Link](targetRow, 4).Value = [Link]("C8").Value ' Fluid Family
[Link](targetRow, 5).Value = [Link]("C10").Value ' Piping Class
' Add a hyperlink to the Drawing No. (assumes PDF file is in the same folder)
pdfFilePath = [Link] & "\" & [Link]("C13").Value & ".pdf"
[Link] _
Anchor:=[Link](targetRow, 6), _
Address:=pdfFilePath, _
TextToDisplay:=[Link]("C13").Value ' Drawing No.
[Link](targetRow, 7).Value = [Link]("C12").Value ' P&ID
Else
[Link] "Sheet 'Pipe Line Data' not found in: " & [Link]
End If
' Process the "Maintenance Activities" sheet
If SheetExists(sourceWB, "Maintenance Activities") Then
Set sourceWS = [Link]("Maintenance Activities")
[Link](targetRow, 8).Value = [Link]("E6").Value ' Insulation
removal
[Link](targetRow, 9).Value = [Link]("E11").Value ' Scaffolding
Requirement
Else
[Link] "Sheet 'Maintenance Activities' not found in: " & [Link]
End If
' Process the "CML READING" sheet
If SheetExists(sourceWB, "CML READING") Then
Set sourceWS = [Link]("CML READING")
' Find the last column with data in row 10 starting from column H
lastCol = [Link](10, [Link]).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 = [Link]([Link](5,
lastCol).Value, [Link](6, lastCol).Value)
[Link](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 = [Link](8, lastCol + 1).Value
[Link](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 = [Link](8, lastCol).Value
[Link](targetRow, 10).Value = lastInspectionDate ' Last Inspection Date
End If
Else
[Link] "Sheet 'CML READING' not found in: " & [Link]
End If
' Increment the target row
targetRow = targetRow + 1
' Close the source workbook
[Link] 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 = [Link](sheetName)
SheetExists = Not ws Is Nothing
On Error GoTo 0
End Function