Refresh excel power queries with powershell and save

powershell-refresh-excel-powerqueries.ps1

# warning: 
# this is a rough script for refreshing excel files with power query connections
# it should be adjusted to fit your needs and environment

# usage:
# 1. save file as refresh_excel_file.ps1
# 2. open the run dialog with WIN+R
# 3.1. use the below command to run the script with the path to the excel file(change the filepath to your excel file)
    # powershell -NoExit -File "C:\Users\username\Desktop\refresh_excel_file.ps1" -Path "C:\Users\username\Desktop\file_with_queries.xlsx"
# 3.2. you can also run the script from a powershell window directly
    # C:\Users\username\Desktop\refresh_excel_file.ps1 -Path "C:\Users\username\Desktop\file_with_queries.xlsx"


param (
    # define the path to the excel file
    [string]$Path,

    # open excel in the foreground
    [switch]$Visible = $true,

    # show alerts like "do you want to save?"
    [switch]$Alerts
)

# validate the excel file path and extension
if (-not (Test-Path -Path $Path)) {
    Write-Error "File does not exist: $Path"
    exit
}

if (-not ($Path -match '\.xlsx?$')) {
    Write-Error "File is not an Excel file (.xlsx or .xls): $Path"
    exit
}

try {

    write-host "refreshing data in file: `n$Path"

    # start excel application
    $excel = New-Object -ComObject Excel.Application

    # set excel visibility and alerts
    $excel.Visible = $Visible
    $excel.DisplayAlerts = $Alerts

    # open the workbook
    $workbook = $excel.Workbooks.Open("$($Path)", 0, $false)

    # refresh all data connections
    $workbook.RefreshAll()

    # give a little delay for the refresh to start
    Start-Sleep -Seconds 10

    # wait for all querytables to finish refreshing
    Write-Host "waiting for querytables to complete..."
    foreach ($sheet in $workbook.Sheets) {
        foreach ($qt in $sheet.QueryTables) {
            while ($qt.Refreshing) {
                # Write-Host "querytable still refreshing on sheet: $($sheet.Name)..."
                Start-Sleep -Seconds 5
            }
            # release the querytable com object.
            [System.Runtime.InteropServices.Marshal]::ReleaseComObject($qt) | Out-Null
        }
        # release the worksheet com object.
        [System.Runtime.InteropServices.Marshal]::ReleaseComObject($sheet) | Out-Null
    }

    # wait for all listobjects (excel tables with data connections) to finish refreshing
    Write-Host "waiting for listobjects(excel tables with data connections) to complete..."
    foreach ($sheet in $workbook.Sheets) {
        foreach ($lo in $sheet.ListObjects) {
            if ($lo.QueryTable -ne $null) {
                while ($lo.QueryTable.Refreshing) {
                    # Write-Host "listobject still refreshing on sheet: $($sheet.Name)..."
                    Start-Sleep -Seconds 5
                }
            }
        }
    }

    # # additional wait time to ensure background queries are done
    # Write-Host "final check for background queries..."
    # $maxWait = 300
    # $elapsed = 0
    # while ($excel.CalculateBeforeSave -and $elapsed -lt $maxWait) {
    #     Write-Host "background queries still running..."
    #     Start-Sleep -Seconds 5
    #     $elapsed += 5
    # }


    # give a little delay for the refresh to finalize
    Write-Host "waiting a little longer to ensure all queries and tables have been refreshed..."
    Start-Sleep -Seconds 60

    Write-Host "all queries and tables have been refreshed"

    
    if ($workbook.ReadOnly) {
        Write-Host "Workbook opened in read-only mode... force save"
        # force save the workbook
        $workbook.SaveAs($Path)
    } else {
        # save the workbook
        $workbook.Save()
    }

    # close the workbook
    $workbook.Close($true)

    Write-Host "Refresh of file complete: $Path"
}
catch {
    Write-Error "Failed to process the Excel file: $Path"
    Wait-Event
}
finally {
    # quit excel
    if ($excel) {
        $excel.Quit()

        # release com objects
        [System.Runtime.InteropServices.Marshal]::ReleaseComObject($workbook) | Out-Null
        [System.Runtime.InteropServices.Marshal]::ReleaseComObject($excel) | Out-Null
        [System.GC]::Collect()
        [System.GC]::WaitForPendingFinalizers()
    }
}

# Write-Host "Exiting..."
# Start-Sleep -Seconds 10

# leave the window open for debugging
# Wait-Event