SAP analysis for office automation

sapo.ps1

# CONFIGURE
## set environment variables SAP_ANALYSIS_USER and SAP_ANALYSIS_PASS

# TO ENABLE EXECUTION (if you see SecurityError:UnauthorizedAccess)
## 1. open a powershell console as Administrator
## 2. run "set-executionpolicy remotesigned"

# TO RUN
## 1. open powershell
## 2. run "./sapo.ps1 -file FULL_PATH_TO_EXCEL.xlsx"

# TO KILL ALL EXCEL FOR A USER
## taskkill /f /fi "USERNAME eq %USERNAME%" /im Excel.exe 
param(
    [string]$client =  "001",
    [string]$username = $Env:SAP_ANALYSIS_USER,
    [string]$password = $Env:SAP_ANALYSIS_PASS,
    [string]$file = "C:\FULLPATH\Example.xlsx"
)   

function ActivateComAddin ($xlApp) {
    foreach ($AddIn in $xlApp.Application.COMAddIns) {
        if ($AddIn.progID -eq "SapExcelAddIn")
        {
            if($AddIn.Connect){
                $AddIn.Connect = $false
                $AddIn.Connect = $true
            }else{
                $AddIn.Connect = $true
            }
        }
    }    
}

Write-Output "Opening excel..."
$xlApp = New-Object -ComObject Excel.Application
$xlApp.Visible = $true
$xlApp.DisplayAlerts = $false

Write-Output "Open excel file $file"
try {
    $xlBook = $xlApp.Workbooks.Open($file)
} catch {
    Write-Output "Cannot load excel file $file."
    $xlApp.quit()
    exit 1
}

Write-Output "Activating SAP Analysis for Office Addin..."
ActivateComAddin($xlApp) 

$masked = "************$($password.Substring($password.Length - 1))"
Write-Output "Loggin in with CLIENT: $client, USERNAME: $username, PASSWORD: $masked"
$logonResult = $xlApp.Application.Run("SAPLogon", "DS_1", $client, $username, $password)
if( $logonResult = 1 ) {
    Write-Output "Refreshing data..."
    $refreshResult = $xlApp.Application.Run("SAPExecuteCommand", "Refresh")
    if( $refreshResult = 1 ) { 
        Write-Output "Refresh OK!, saving..."
        $xlBook.save()
        Write-Output "Saved!"
    } else {
        Write-Output "Error refreshing data."
    }
} else { 
    Write-Output "Username or password NOT valid." 
}

Write-Output "Wrapping up..."
$xlBook.close()
$xlApp.quit()