Powershell, make a loop to export to xlxs file until it's no longer locked.

[evanefsvals]

Hi all,

Is there a way to have a check before I export to xlxs and wait until another process no longer locks the file?
(I have made a script that runs simultaneously in multiple places, and all instances are exporting to one specific file)

Thanks

[dfinke]

Review and try this.

# https://github.com/dfinke/ImportExcel/discussions/1710

function Test-FileLock {
    param (
        [string]$FilePath
    )
    try {
        # Attempt to open the file with exclusive access
        [IO.File]::Open($FilePath, 'Open', 'ReadWrite', 'None') | Out-Null
        return $false # File is not locked
    }
    catch {
        return $true # File is locked
    }
}

function Wait-ForFile {
    param (
        [string]$FilePath,
        [int]$MaxAttempts = 60,
        [int]$SleepSeconds = 5
    )
    $attempt = 1
    while ((Test-FileLock -FilePath $FilePath) -and $attempt -le $MaxAttempts) {
        Write-Host "File $FilePath is locked. Waiting... (Attempt $attempt/$MaxAttempts)"
        Start-Sleep -Seconds $SleepSeconds
        $attempt++
    }
    if ($attempt -gt $MaxAttempts) {
        Write-Error "File $FilePath is still locked after $MaxAttempts attempts. Aborting."
        return $false
    }
    return $true
}

# Example usage in your export script
$excelFile = "$PSScriptRoot\test.xlsx"

# Wait until the file is not locked
if (Wait-ForFile -FilePath $excelFile -SleepSeconds 1) {
    try {
        # Your export logic here, e.g., using ImportExcel module
        # Example: $data | Export-Excel -Path $excelFile -Append
        Write-Host "Exporting to $excelFile..."
        # Replace with your actual export command
        # $data | Export-Excel -Path $excelFile -Append
    }
    catch {
        Write-Error "Failed to export to $excelFile : $_"
    }
}
else {
    Write-Error "Could not export to $excelFile because it remained locked."
}