Empresa: NYK
Gestor: Bruno
Projeto: GM
Tech Guide
LCC Documentation:
The Tech Guide as well as the documentation written down has been put together to serve as support material for technical professionals who have in-depth knowledge of:
> The tool
> Calculus
> Programming & Logic
> The business
!
Documentation for Subroutine: CheckingLCCandPC
The CheckingLCCandPC subroutine validates the existence of a file path provided in the workbook, retrieves the file's name and last modified date, and updates the corresponding cells in a worksheet. This subroutine is triggered by buttons labeled "CheckLCC" and "CheckPC," each corresponding to specific file paths and cell ranges.
Dim filePath As String
Dim fileName As String
Dim fileDate As Date
Dim targetSheet As Worksheet
Dim fileLogSheet As Worksheet
Dim callerButton As String
Dim fileNameCell As String
Dim LastModifiedCell As String
Dim DeciderCell As String
Purpose: These variables are declared to store file-related information, worksheet references, and the context provided by the button that triggers the subroutine.
filePath, fileName, and fileDate hold the file's location, name, and last modified date, respectively.
targetSheet and fileLogSheet refer to specific worksheets: "MENU" and "LCCPC_FILE_LOG."
callerButton captures which button was pressed.
fileNameCell, LastModifiedCell, and DeciderCell define specific cell references to be updated.
Set targetSheet = ThisWorkbook.Sheets("MENU")
Set fileLogSheet = ThisWorkbook.Sheets("LCCPC_FILE_LOG")
callerButton = Application.caller
-
Purpose: The subroutine first identifies the relevant sheets:
-
targetSheet is the "MENU" sheet, where user inputs and outputs are located.
-
fileLogSheet is the "LCCPC_FILE_LOG" sheet, used for logging operations.
-
-
The Application.caller property determines which button invoked the subroutine, assigning its name to callerButton.
If callerButton = "CheckLCC" Then
filePath = targetSheet.Range("E8").Value
fileNameCell = "E11"
LastModifiedCell = "F11"
DeciderCell = "E6"
ElseIf callerButton = "CheckPC" Then
filePath = targetSheet.Range("E13").Value
fileNameCell = "E16"
LastModifiedCell = "F16"
DeciderCell = "F6"
End If
-
Purpose: Based on the button pressed, the subroutine assigns specific file paths and cell locations:
-
For "CheckLCC," it uses file information from E8, updating E11 (file name), F11 (last modified), and E6 (log decision).
-
For "CheckPC," it uses file information from E13, updating E16 (file name), F16 (last modified), and F6 (log decision).
If Dir(filePath) = "" Then
MsgBox "The specified file does not exist: " & filePath, vbExclamation, "File Not Found"
Exit Sub
End If
-
Purpose: Ensures the file path provided exists:
If the Dir function returns an empty string, the file does not exist, and the subroutine exits after displaying an error message.
fileName = Dir(filePath)
fileDate = FileDateTime(filePath)
-
Purpose: If the file exists, its name and last modified date are retrieved:
fileName stores the name of the file at the provided path.
fileDate captures the last modified timestamp of the file.
targetSheet.Range(fileNameCell).Value = fileName targetSheet.Range(LastModifiedCell).Value = fileDate fileLogSheet.Range(DeciderCell).Value = 0
-
Purpose: Updates the worksheet cells with the retrieved information:
The file name and last modified date are written to fileNameCell and LastModifiedCell on the "MENU" sheet.
The "LCCPC_FILE_LOG" sheet's DeciderCell is reset to 0 to indicate the log has been updated.
Documentation for Subroutine: CheckingTTFiles
The CheckingTTFiles subroutine scans through a folder and its subfolders to find files containing the keyword "Timetable" in their names. It retrieves and logs relevant information (file name, last modified date, and file path) into two output worksheets: "MENU" and "TT_FILE_LOG." This subroutine is designed for efficient and automated mapping of Timetable files while providing feedback to the user.
Dim folderPath As String
Dim fileSearchWord As String
Dim outputSheetMenu As Worksheet
Dim outputSheetLog As Worksheet
Dim outputRowMenu As Long
Dim outputRowLog As Long
Dim fso As Object
Dim parentFolder As Object
Dim subFolder As Object
Dim file As Object
Purpose: Variables store folder paths, search keywords, references to worksheets, row numbers for data output, and objects for file system access.
folderPath: Stores the folder path to be searched.
fileSearchWord: Keyword to identify relevant files ("Timetable").
outputSheetMenu, outputSheetLog: References to the "MENU" and "TT_FILE_LOG" worksheets for data logging.
outputRowMenu, outputRowLog: Starting row indices for writing data in the respective worksheets.
fso, parentFolder, subFolder, file: Objects from the FileSystemObject library for navigating folders and files.
fileSearchWord = "Timetable"
Set outputSheetMenu = ThisWorkbook.Sheets("MENU")
Set outputSheetLog = ThisWorkbook.Sheets("TT_FILE_LOG")
outputSheetMenu.Range("E22:F500").ClearContents
outputSheetLog.Range("E7:H500").ClearContents
outputRowMenu = 22
outputRowLog = 7
Purpose:
Defines the keyword to search for in file names (fileSearchWord).
Initializes references to the output sheets and clears old data from specific ranges to prepare for fresh output.
Sets the starting rows for data insertion in the respective sheets.
folderPath = ThisWorkbook.Sheets("MENU").Range("E18").Value
If Len(Dir(folderPath, vbDirectory)) = 0 Then
MsgBox "The folder path does not exist: " & folderPath, vbExclamation, "Invalid Folder"
Exit Sub
End If
If Right(folderPath, 1) <> "\" Then
folderPath = folderPath & "\"
End If
Purpose:
Retrieves the folder path from cell E18 in the "MENU" sheet.
Validates if the folder path exists; exits the subroutine with a message if invalid.
Ensures the folder path ends with a backslash to maintain compatibility with file system operations.
Set fso = CreateObject("Scripting.FileSystemObject")
On Error Resume Next
Set parentFolder = fso.GetFolder(folderPath)
On Error GoTo 0
If parentFolder Is Nothing Then
MsgBox "The specified folder path is invalid or inaccessible.", vbExclamation, "Error"
Exit Sub
End If
Purpose:
Initializes a FileSystemObject for folder navigation.
Attempts to get the parent folder and handles errors gracefully if the folder is inaccessible.
For Each subFolder In parentFolder.SubFolders
For Each file In subFolder.Files
If InStr(1, file.Name, fileSearchWord, vbTextCompare) > 0 Then
outputSheetMenu.Cells(outputRowMenu, 5).Value = file.Name
outputSheetMenu.Cells(outputRowMenu, 6).Value = file.DateLastModified
outputSheetLog.Cells(outputRowLog, 5).Value = file.Name
outputSheetLog.Cells(outputRowLog, 6).Value = file.DateLastModified
outputSheetLog.Cells(outputRowLog, 7).Value = file.Path
outputRowMenu = outputRowMenu + 1
outputRowLog = outputRowLog + 1
End If
Next file
Next subFolder
Purpose: Iterates through each subfolder and file in the parent folder:
Checks if the file name contains the keyword "Timetable."
If a match is found, logs the file's name, last modified date, and path into both the "MENU" and "TT_FILE_LOG" sheets.
Advances the row counters for subsequent entries.
outputSheetMenu.Range("H22:H72").Copy
outputSheetLog.Range("H7").PasteSpecial xlPasteValues
outputSheetLog.Range("J6").Value = 0
outputSheetLog.Select
outputSheetLog.Range("A1").Select
outputSheetMenu.Select
outputSheetMenu.Range("E21").Select
Application.CutCopyMode = False
Purpose:
Copies data from the "MENU" sheet to the "TT_FILE_LOG" sheet for additional logging.
Resets a specific cell (J6) in the "TT_FILE_LOG" sheet to 0.
Finalizes the process by navigating and selecting specific cells, ensuring the workbook is ready for user interaction.
Documentation for Subroutine: CopyingLCCData
The CopyingLCCData subroutine automates the process of validating a source file's path, opening the source workbook, copying specific data ranges from its worksheets, and pasting the data into designated sheets in the current workbook. The process also includes clearing existing data, logging the operation, and ensuring all changes are tracked efficiently. The subroutine provides feedback to the user at every stage.
If ThisWorkbook.Sheets("MENU").Range("E8").Value = "" Then
MsgBox "No path has been defined."
Exit Sub
End If
If ThisWorkbook.Sheets("LCCPC_FILE_LOG").Range("E6").Value = 1 Then
MsgBox "You need to refresh the File Log before running this procedure."
Exit Sub
ElseIf ThisWorkbook.Sheets("MENU").Range("G11").Value = "Unchanged" Then
MsgBox "There haven't been any changes to this file"
Exit Sub
End If
Purpose:
Ensures prerequisites are met before proceeding:
The source file path must be defined in cell E8 of the "MENU" sheet.
The file log must be refreshed (value of E6 in "LCCPC_FILE_LOG" must not be 1).
The source file must have undergone changes (value in G11 of "MENU" must not be "Unchanged").
Provides error messages and exits the subroutine if validations fail.
Dim Wb As Workbook
Dim sourceFilePath As String
Dim LCCWorkbook As Workbook
Dim LCCDataSet As Worksheet
Dim LCCHeaders As Worksheet
Dim GMLCCDataSet As Worksheet
Dim GMLCCHeaders As Worksheet
Dim DataSetRange As Range
Dim HeadersRange As Range
Dim FinalSetlastRow As Long
Dim HeadersLastRow As Long
Dim GMDataSetRange As Range
Dim GMHeadersRange As Range
Dim fileLog As Worksheet
Set Wb = ThisWorkbook
sourceFilePath = Wb.Sheets("MENU").Range("E8").Value
If Dir(sourceFilePath) = "" Then
MsgBox "The source file does not exist: " & sourceFilePath, vbExclamation, "File Not Found"
Exit Sub
End If
Purpose:
Declares variables for workbook and worksheet references, ranges, and logging operations.
Validates if the source file exists; exits the subroutine with an error message if not.
Set GMLCCDataSet = Wb.Sheets("LCC_FINALSET")
Set GMLCCHeaders = Wb.Sheets("LCC_HEADERS")
Set fileLog = Wb.Sheets("LCCPC_FILE_LOG")
FinalSetlastRow = GMLCCDataSet.Cells(GMLCCDataSet.Rows.Count, 1).End(xlUp).Row
HeadersLastRow = GMLCCHeaders.Cells(GMLCCHeaders.Rows.Count, 1).End(xlUp).Row
Set GMDataSetRange = GMLCCDataSet.Range("A2:J" & FinalSetlastRow + 1)
Set GMHeadersRange = GMLCCHeaders.Range("A2:R" & HeadersLastRow + 1)
GMDataSetRange.Clear
GMHeadersRange.Clear
Purpose:
Identifies and clears existing data ranges in the "LCC_FINALSET" and "LCC_HEADERS" sheets.
Prevents conflicts between old and new data during the copying process.
Application.ScreenUpdating = False
Application.EnableEvents = False
Set LCCWorkbook = Workbooks.Open(sourceFilePath)
Set LCCDataSet = LCCWorkbook.Sheets("BASE_ESCALAS")
Set LCCHeaders = LCCWorkbook.Sheets("BASE_CABEÇALHO")
Set DataSetRange = LCCDataSet.Range("A4:J" & LCCDataSet.Cells(LCCDataSet.Rows.Count, 1).End(xlUp).Row)
DataSetRange.Copy
GMLCCDataSet.Range("A2").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Application.CutCopyMode = False
Set HeadersRange = LCCHeaders.Range("A4:R" & LCCHeaders.Cells(LCCHeaders.Rows.Count, 1).End(xlUp).Row)
HeadersRange.Copy
GMLCCHeaders.Range("A2").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Purpose:
Opens the source workbook in a controlled environment (disabling screen updates and events for efficiency).
Copies data from specified ranges in "BASE_ESCALAS" and "BASE_CABEÇALHO" of the source workbook.
Pastes data into "LCC_FINALSET" and "LCC_HEADERS" in the current workbook, maintaining values and number formats.
LCCWorkbook.Close SaveChanges:=False
fileLog.Range("A7").Value = Dir(sourceFilePath)
fileLog.Range("B7").Value = FileDateTime(sourceFilePath)
fileLog.Range("E6").Value = 1
Purpose:
Closes the source workbook without saving changes.
Logs the file name and last modified date in "LCCPC_FILE_LOG."
Updates the status cell E6 to indicate the operation is complete.
Application.CutCopyMode = False
Application.ScreenUpdating = True
Application.EnableEvents = True
Wb.Sheets("MENU").Select
MsgBox "Data copied successfully!", vbInformation, "Success"
Purpose:
Restores application settings (screen updates and event handling).
Provides feedback to the user through a success message.
Documentation for Subroutine: CopyingPCData
The CopyingPCData subroutine facilitates the transfer of data from an external workbook to the current workbook. It validates the file path, ensures data dependencies are met, clears existing data in the target sheet, copies new data from a specific worksheet in the source workbook, and logs the operation's details. The process is dynamic and provides user feedback at critical stages.
If ThisWorkbook.Sheets("MENU").Range("E13").Value = "" Then
MsgBox "No path has been defined."
Exit Sub
End If
If ThisWorkbook.Sheets("LCCPC_FILE_LOG").Range("F6").Value = 1 Then
MsgBox "You need to refresh the File Log before running this procedure."
Exit Sub
ElseIf ThisWorkbook.Sheets("MENU").Range("G16").Value = "Unchanged" Then
MsgBox "There haven't been any changes to this file"
Exit Sub
End If
Purpose:
Ensures that:
A file path is specified in cell E13 of the "MENU" sheet.
The file log status is refreshed (cell F6 of "LCCPC_FILE_LOG" must not equal 1).
The file has been updated (cell G16 of "MENU" must not display "Unchanged").
Displays error messages and exits the subroutine if any condition fails.
Dim Wb As Workbook
Dim sourceFilePath As String
Dim PCWorkbook As Workbook
Dim PCDataSet As Worksheet
Dim GMPCDataSet As Worksheet
Dim DataSetRange As Range
Dim GMDataSetRange As Range
Dim lastRow As Long
Dim fileLog As Worksheet
Set Wb = ThisWorkbook
sourceFilePath = Wb.Sheets("MENU").Range("E13").Value
If Dir(sourceFilePath) = "" Then
MsgBox "The source file does not exist: " & sourceFilePath, vbExclamation, "File Not Found"
Exit Sub
End If
Purpose:
Declares variables for handling workbook and worksheet references, data ranges, and logging.
Retrieves the source file path from the "MENU" sheet and validates its existence using the Dir function.
Displays an error message and exits if the file is missing.
Set GMPCDataSet = Wb.Sheets("PORTCALLS_SET")
Set fileLog = Wb.Sheets("LCCPC_FILE_LOG")
lastRow = GMPCDataSet.Cells(GMPCDataSet.Rows.Count, 1).End(xlUp).Row
Set GMDataSetRange = GMPCDataSet.Range("A2:AG" & lastRow + 1)
GMDataSetRange.Clear
Purpose:
Identifies the existing data range in the "PORTCALLS_SET" sheet.
Clears the identified range to ensure no residual data conflicts with the new data being imported.
Application.ScreenUpdating = False
Application.EnableEvents = False
Set PCWorkbook = Workbooks.Open(sourceFilePath)
Set PCDataSet = PCWorkbook.Sheets("Base")
Set DataSetRange = PCDataSet.Range("B7:AH" & PCDataSet.Cells(PCDataSet.Rows.Count, 2).End(xlUp).Row)
DataSetRange.Copy
GMPCDataSet.Range("A2").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Application.CutCopyMode = False
Purpose:
Opens the source workbook located at sourceFilePath while disabling screen updates and events for performance.
Identifies the data range in the "Base" sheet of the source workbook, starting from row 7 and extending to the last non-empty cell in column B.
Copies the data to the "PORTCALLS_SET" sheet in the current workbook, maintaining values and number formats.
PCWorkbook.Close SaveChanges:=False
fileLog.Range("A8").Value = Dir(sourceFilePath)
fileLog.Range("B8").Value = FileDateTime(sourceFilePath)
fileLog.Range("F6").Value = 1
Purpose:
Closes the source workbook without saving changes.
Logs the file name and last modified timestamp in the "LCCPC_FILE_LOG" sheet.
Updates the log status cell F6 to indicate the operation is complete.
Application.CutCopyMode = False
Application.ScreenUpdating = True
Application.EnableEvents = True
Wb.Sheets("MENU").Select
MsgBox "Data copied successfully!", vbInformation, "Success"
Purpose:
Restores application settings to their default state (reenabling screen updates and events).
Navigates back to the "MENU" sheet for user convenience.
Provides a success message to confirm that the operation was completed without errors.
Documentation for Subroutine: CopyingTT
The CopyingTT subroutine automates the process of importing data from Timetable workbooks into a central workbook. It validates prerequisites, opens specified workbooks, extracts data from multiple sheets, integrates the data into designated sheets, updates metadata, and logs file operations. The subroutine handles dynamic ranges and ensures consistent tracking of operations while providing user feedback.
If ThisWorkbook.Sheets("MENU").Range("E18").Value = "" Then
MsgBox "No path has been defined."
Exit Sub
End If
If ThisWorkbook.Sheets("TT_FILE_LOG").Range("I6").Value = 0 Then
MsgBox "There is no new or altered file."
Exit Sub
ElseIf ThisWorkbook.Sheets("TT_FILE_LOG").Range("J6").Value = 1 Then
MsgBox "You need to refresh the File Log before running this procedure."
Exit Sub
End If
Purpose:
Validates that:
A file path is defined in cell E18 of the "MENU" sheet.
At least one new or altered file is identified in "TT_FILE_LOG" (I6).
The log is refreshed (J6 must not equal 1).
Exits the subroutine with relevant messages if conditions are not met.
Dim Wb As Workbook
Dim TT As Workbook
Dim WbPlanSheet As Worksheet
Dim WbActualSheet As Worksheet
Dim WbProjectionSheet As Worksheet
Dim TTPlanSheet As Worksheet
Dim TTActualSheet As Worksheet
Dim TTProjectionSheet As Worksheet
Dim TimetableSheet As Worksheet
Dim TTLogSheet As Worksheet
Dim TTPath As String
Dim Vessel As String
Dim Voy As Integer
Dim TTDate As Date
Dim TTYear As Integer
Dim LastModifiedDate As Date
Dim WbLogLastRow As Long
Dim TTNextFreeRow As Long
Dim NewLastRow As Long
Dim ExistingBase As Boolean
Dim VesselRange As Range
Dim VoyRage As Range
Dim YearRange As Range
Dim LastModifiedRangePlan As String
Dim LastModifiedRangeActual As String
Dim LastModifiedRangeProjection As String
Dim i As Long
Dim j As Long
Purpose:
Declares variables for managing workbooks, worksheets, ranges, metadata (e.g., Vessel, Voy, TTDate, TTYear), and loop counters.
Tracks the last rows in data ranges and log sheets for efficient data insertion.
Set Wb = ThisWorkbook
Set WbPlanSheet = Wb.Sheets("TT_PLAN")
Set WbActualSheet = Wb.Sheets("TT_ACTUAL")
Set WbProjectionSheet = Wb.Sheets("TT_PROJECTION")
Set TTLogSheet = Wb.Sheets("TT_FILE_LOG")
WbLogLastRow = TTLogSheet.Cells(TTLogSheet.Rows.Count, 7).End(xlUp).Row
Application.ScreenUpdating = False
Purpose:
Initializes references to key worksheets.
Determines the last used row in the "TT_FILE_LOG" sheet for loop processing.
Turns off screen updating to improve performance during the operation.
For i = 7 To WbLogLastRow
If TTLogSheet.Range("H" & i).Value = "Will bring" Then
TTPath = TTLogSheet.Range("G" & i).Value
Set TT = Workbooks.Open(TTPath)
...
TT.Close SaveChanges:=False
End If
Next i
Purpose:
Iterates through the log sheet to identify Timetable files flagged for processing.
Opens each identified file for data extraction.
Set TTPlanSheet = TT.Sheets("Base Zero")
TTLastRow = TTPlanSheet.Cells(TTPlanSheet.Rows.Count, 2).End(xlUp).Row
TTNextFreeRow = WbPlanSheet.Cells(WbPlanSheet.Rows.Count, 4).End(xlUp).Row + 1
TTPlanSheet.Range("A4:L" & TTLastRow).Copy
WbPlanSheet.Range("D" & TTNextFreeRow).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
NewLastRow = WbPlanSheet.Cells(WbPlanSheet.Rows.Count, 4).End(xlUp).Row
Set VesselRange = WbPlanSheet.Range("A" & TTNextFreeRow & ":A" & NewLastRow)
VesselRange.Value = Vessel
Purpose:
Copies data from the "Base Zero" sheet in the source workbook to the "TT_PLAN" sheet in the central workbook.
Assigns metadata (Vessel, Voy, and TTYear) to the corresponding columns
Set TTActualSheet = TT.Sheets("Base Real")
TTLastRow = TTActualSheet.Cells(TTActualSheet.Rows.Count, 2).End(xlUp).Row
TTNextFreeRow = WbActualSheet.Cells(WbActualSheet.Rows.Count, 4).End(xlUp).Row + 1
TTActualSheet.Range("A4:K" & TTLastRow).Copy
WbActualSheet.Range("D" & TTNextFreeRow).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Purpose:
Extracts data from the "Base Real" sheet and pastes it into "TT_ACTUAL."
Mirrors the process used for the "Base Zero" sheet.
Set TTProjectionSheet = TT.Sheets("Output - Projection")
TTNextFreeRow = WbProjectionSheet.Cells(WbProjectionSheet.Rows.Count, 4).End(xlUp).Row + 1
TTProjectionSheet.Range("A4:I" & TTLastRow).Copy
WbProjectionSheet.Range("D" & TTNextFreeRow).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Purpose:
Processes data from the "Output - Projection" sheet in the source workbook to the "TT_PROJECTION" sheet in the central workbook.
TTLogSheet.Range("B" & j).Value = Dir(TTPath)
TTLogSheet.Range("C" & j).Value = FileDateTime(TTPath)
WbPlanSheet.Range(LastModifiedRangePlan).Value = LastModifiedDate
Purpose:
Logs file details (name and modification date) in "TT_FILE_LOG."
Updates metadata for copied data in each sheet.
DisposingSets
TTLogSheet.Range("J6").Value = 1
Application.ScreenUpdating = True
Wb.Sheets("MENU").Select
MsgBox "The operation has been completed Successfully!"
Purpose:
Cleans up references and restores application settings.
Notifies the user of successful completion.