SAP Analysis Office Excel Auto refresh with powershell (without Excel Macro)

example_script_analysis_office.ps1

param(
    [string]$baseDir = "C:\SomeFolderToStoreTheData\",
    [string]$bw_client =  "000",
    [string]$bw_user = "YOURUSERNAME",
    [string]$bw_password = "YOURPASSWORD",
    [string]$filePath = "Path to Analysis Office Excel.xlsx",
    [string]$year_column = "Analysis Technical Name of Year column"
)
# Essentially the Powershell version of https://blogs.sap.com/2016/12/18/automated-updating-of-data-in-excel-files-bex-ao-via-vbavbscript/

# Not used
# $baseDir = (join-path $baseDir $some_other_variable) + "\"

# Create directory
New-Item -ItemType directory -Path $baseDir

# Start Excel
$xlApp = New-Object -ComObject Excel.Application
$xlApp.Visible = $true

$xlApp.DisplayAlerts = $false

function ActivateAnalysisOffice ($xlApp) {
    $xlApp.Application.StatusBar = "Making sure Analysis for Office addin is active..."
    foreach ($AddIn in $xlApp.Application.COMAddIns) {
        if ($AddIn.progID -eq "SapExcelAddIn")
        {
            if($AddIn.Connect){
                $AddIn.Connect = $false
                $AddIn.Connect = $true
            }else{
                $AddIn.Connect = $true
            }
        }
    }    
    $lresult = $xlApp.Application.Run("SAPLogon", "DS_1", $bw_client, $bw_user, $bw_password)
    $lresult = $xlApp.Application.Run("SAPExecuteCommand", "RefreshData", "DS_1")
}



$xlBook = $xlApp.Workbooks.Open($filePath)
ActivateAnalysisOffice($xlApp)  # Now log in to the BW system
$xlApp.Application.Run("SAPSetRefreshBehaviour", "Off")
#' Now log in to the BW system
for($year = 2004;$year -le 2017; $year++){
    $xlApp.Application.StatusBar = 'Downloading data for ' + $year
    $xlApp.Application.Run("SAPSetRefreshBehaviour", "Off")
    $xlApp.Application.Run("SAPSetFilter", "DS_1", $year_column, [string] $year, "TEXT")
    $xlApp.Application.Run("SAPSetRefreshBehaviour", "On")
    $xlBook.SaveAs($baseDir + "DATA_" + $year + ".xlsx", 51)
    $xlBook.SaveAs($baseDir + "DATA_" + $year + ".csv", 6)
}
$xlBook.Close()

Hi @christlc,

did you ever manage to set this powershell as an automated task via Windows Task Scheduler?
Some hints on what to do in case i constantly get the error:
"Cannot run the macro 'SAPSetRefreshBehaviour'. The macro may not be available in this workbook or all macros may be
disabled."

Even though i applied DCOM settings and ensure the SAP Plugin is loaded?

Thank you.

Max