Batch file help. Creating folders with sub folders.

[[Ri...]]

I had AI convert the batch file to a PowerShell file, that will be my next test.

After I get IT to leave it alone. 

[[Je...]]

Hey Rich.

I've done the same thing in the past as far as using "move" and then lost the file.  It's the same as using "cut" instead of "copy" and then if the paste doesn't finish completely, the source file is lost.

It's like trying to beam someone up to the Enterprise but losing them in the void.

[[Ri...]]

I now have working bat and ps1 (PowerShell) files active.

I seem to be running into possibly one of two issues (or both) and looking for solutions.

1. Operator interference, operators may be closing the Calypso created excel file prior to the script running (Not sure if that would affect the script).

2. Network latency, not sure how what to do here. There are already small dwells to accommodate file creation, closing, moving etc.

Sample Bat and ps2 files attached. (p.s. If you can use or modify the bat or ps1 files for your own use, feel free to do so).

From 1 and 2 above, occasionally I find the excel file is still in the Results folder, when it should not be after the script has completed.

Any help is appreciated to solve this.

inspection_end.bat inspection_end.ps1

[[DW...]]

Please sign in to view this quote.

Please sign in to view this username.

 In your # ----Close Excel if running---- block of code, if the operators close the XLS file before the script fires and no other instances of Excel are open, the Get-Process cmdlet returns $null and the rest of the snippet doesn't execute except for the logging portion.

In your # --- Copy latest .xls file --- block of code, I would add in the parameter -ErrorAction Stop at the critical functions. Also, if an operation does not execute, add a retry function call that detects if Excel is still running or locked up and terminates Excel and then retries that operation. 

This is your script with the following improvements - 

 

1. Manual console toggle
2. Overwrite-on-start log file
3. Safe logging with retry
4. Excel auto-close at start
5. Folder-building logic
6. Integrated Copy-LatestXlsWithBackup with retry + Excel cleanup
7. Old copy/delete code removed and replaced with function call

 

# PowerShell script with improved logging, manual console toggle, and overwrite log file

# --- Manual toggle for console output ---
$EnableConsoleOutput = $false  # Set to $false to disable console output

# --- Initialize log file (overwrite) ---
$scriptFolder = Split-Path -Parent $MyInvocation.MyCommand.Definition
$logFile = Join-Path $scriptFolder "script_log.txt"
"============================================================" | Out-File -FilePath $logFile -Encoding UTF8
"[($(Get-Date))] INFO: Script started." | Out-File -FilePath $logFile -Append
if ($EnableConsoleOutput) { Write-Host "Script started." -ForegroundColor Green }

# --- Safe logging function with retry logic ---
function SafeAddContent($path, $value) {
    $maxRetries = 5
    $retryDelay = 2
    for ($i = 1; $i -le $maxRetries; $i++) {
        try {
            Add-Content -Path $path -Value $value
            break
        } catch {
            if ($i -eq $maxRetries) {
                Write-Host "Failed to write to log after $maxRetries attempts." -ForegroundColor Red
            } else {
                Start-Sleep -Seconds $retryDelay
            }
        }
    }
}

function LogInfo($message) {
    SafeAddContent $logFile "[($(Get-Date))] INFO: $message"
    if ($EnableConsoleOutput) { Write-Host "INFO: $message" -ForegroundColor Cyan }
}

function LogError($message) {
    SafeAddContent $logFile "[($(Get-Date))] ERROR: $message"
    if ($EnableConsoleOutput) { Write-Host "ERROR: $message" -ForegroundColor Red }
}

# ---Copy-LatestXlsWithBackup with Retry + Automatic Excel Cleanup ---
function Copy-LatestXlsWithBackup {
    [CmdletBinding()]
    param(
        [Parameter(Mandatory)]
        [string]$Source,

        [Parameter(Mandatory)]
        [string]$Destination,

        [Parameter(Mandatory)]
        [string]$BackupDir,

        [int]$MaxRetries = 5,
        [int]$RetryDelaySeconds = 2
    )

    function Stop-ExcelProcesses {
        try {
            $procs = Get-Process -Name "EXCEL" -ErrorAction SilentlyContinue
            if ($procs) {
                LogInfo "Excel detected; attempting cleanup..."
                $procs | ForEach-Object { Stop-Process $_ -Force -ErrorAction SilentlyContinue }
                LogInfo "Excel processes terminated."
            }
        } catch {
            LogError "Unable to terminate Excel processes. Reason: $($_.Exception.Message)"
        }
    }

    function Invoke-WithRetry {
        param(
            [scriptblock]$Script,
            [string]$Action,
            [int]$MaxAttempts,
            [int]$Delay
        )

        for ($attempt = 1; $attempt -le $MaxAttempts; $attempt++) {
            try {
                return & $Script
            }
            catch {
                $err = $_.Exception.Message
                if ($attempt -eq $MaxAttempts) {
                    throw "Failed '$Action' after $MaxAttempts attempts. Last error: $err"
                }

                LogError "Action '$Action' failed: $err"
                Stop-ExcelProcesses
                LogInfo "Retrying '$Action' in $Delay seconds... (Attempt $attempt/$MaxAttempts)"
                Start-Sleep -Seconds $Delay
            }
        }
    }

    try {
        if (-not (Test-Path $Source))      { throw "Source path not found: $Source" }
        if (-not (Test-Path $Destination)) { throw "Destination path not found: $Destination" }
        if (-not (Test-Path $BackupDir))   { 
            New-Item -ItemType Directory -Path $BackupDir -ErrorAction Stop | Out-Null 
            LogInfo "Backup directory created: $BackupDir"
        }

        Set-Location $Source -ErrorAction Stop

        # Get newest XLS
        $latestFile = Get-ChildItem -Filter "*.xls" -File -ErrorAction Stop |
                      Sort-Object CreationTime -Descending |
                      Select-Object -First 1

        if (-not $latestFile) { throw "No .xls files found in $Source" }

        LogInfo "Latest .xls file: $($latestFile.Name)"

        $timestamp = Get-Date -Format "yyyy-MM-dd_HHmmss"
        $newFile   = "{0}_{1}{2}" -f $latestFile.BaseName, $timestamp, $latestFile.Extension

        $destFile   = Join-Path $Destination $newFile
        $backupFile = Join-Path $BackupDir   $newFile

        # Copy to destination
        Invoke-WithRetry -Action "Copy to destination" `
            -MaxAttempts $MaxRetries -Delay $RetryDelaySeconds `
            -Script { Copy-Item $latestFile.FullName $destFile -Force -ErrorAction Stop }

        LogInfo "File copied to: $destFile"

        # Backup
        Invoke-WithRetry -Action "Backup file" `
            -MaxAttempts $MaxRetries -Delay $RetryDelaySeconds `
            -Script { Copy-Item $latestFile.FullName $backupFile -Force -ErrorAction Stop }

        LogInfo "Backup created: $backupFile"

        # Delete original
        Invoke-WithRetry -Action "Delete original" `
            -MaxAttempts $MaxRetries -Delay $RetryDelaySeconds `
            -Script { Remove-Item $latestFile.FullName -Force -ErrorAction Stop }

        LogInfo "Original file deleted: $($latestFile.FullName)"
    }
    catch {
        LogError "Copy-LatestXlsWithBackup failed. Reason: $($_.Exception.Message)"
    }
}

# --- Close Excel if running ---
LogInfo "Attempting to close Excel if running."
try {
    Get-Process EXCEL -ErrorAction SilentlyContinue | ForEach-Object { Stop-Process $_ -Force }
    LogInfo "Excel processes terminated successfully (if any were running)."
} catch {
    LogError "Failed to terminate Excel processes. Reason: $_"
}

# --- Define paths ---
$source = "C:\\Users\\Public\\Documents\\Zeiss\\CALYPSO\\workarea\\results"
$target = "V:\\CMM Data Files\\Excel Files\\Shoe Sphere-Micura"
$backupDir = "C:\\Backup"
LogInfo "Source path: $source"
LogInfo "Target path: $target"

# --- Check if target drive is available ---
if (-Not (Test-Path $target)) {
    LogError "Target path '$target' is not accessible. Exiting script."
    exit 1
}

# --- Check for setRunID2.txt ---
$runIdFile = Join-Path $scriptFolder "setRunID2.txt"
if (-Not (Test-Path $runIdFile)) {
    LogError "Required file 'setRunID2.txt' not found in $scriptFolder. Exiting script."
    exit 1
}
LogInfo "Found setRunID2.txt. Reading folder names."

# --- Read folder names ---
$lines = Get-Content $runIdFile
$parentFolder = $lines[0]
$subFolder1 = $lines[1]
$subFolder2 = $lines[2]
$progFolder = $lines[3]
LogInfo "Folder structure: $parentFolder\\$subFolder1\\$subFolder2\\$progFolder"

# --- Create folder structure ---
$fullPath = Join-Path $target "$parentFolder\\$subFolder1\\$subFolder2\\$progFolder"
try {
    if (-Not (Test-Path $fullPath)) {
        New-Item -ItemType Directory -Path $fullPath -Force | Out-Null
        LogInfo "Created folder structure at '$fullPath'."
    } else {
        LogInfo "Folder already exists: $fullPath"
    }
} catch {
    LogError "Failed to create folder structure at '$fullPath'. Reason: $_"
    exit 1
}

# --- Wait for 5 seconds ---
LogInfo "Waiting 5 seconds to ensure folders are ready."
Start-Sleep -Seconds 5

# --- Use Robust Function to Copy the XLS File ---
LogInfo "Starting robust copy of latest .xls file..."
Copy-LatestXlsWithBackup -Source $source -Destination $fullPath -BackupDir $backupDir

LogInfo "Script completed successfully."
SafeAddContent $logFile "============================================================"
if ($EnableConsoleOutput) { Write-Host "Script completed successfully." -ForegroundColor Green }
exit 0

 

 

 

 

 

[[Ri...]]

Thank you, I will have to put this thru testing soon.