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,

Thank you very much for your post.
I have the problem that when the Excel file opens, a prompt opens that queries the DS filters. We have 8 DS filters. Filters 2-8 can be confirmed with your method but for the DS_1 filter it does not work and the prompt does not close so the data cannot be refreshed.

Do you have any suggestions?

Thank you and best regards

@Mirkolando it's been 6 years since I last used this, now I have switched roles and no longer have access to SAP.
I would recommend going through https://blogs.sap.com/2016/12/18/automated-updating-of-data-in-excel-files-bex-ao-via-vbavbscript/
or a quick google "SAPSetFilter".

@christlc Thank you very much for your answer. I have solved the first problem, now a new one has arisen. I want to update several Excel files. Everything works fine with my first file. Unfortunately, the Analysis plug-in is not activated for all other files when they open. Do you have any idea what the problem could be?

$CurrentDateFileName = Get-Date -Format "yyyyMMdd"

$OutputPath_Exchange_Rates = "C:\Admin"
$OutputPath_BTQ_Stock = "C:\Admin"
$OutputPath_DEALS_Price = "C:\Admin"
$OutputPath_DEALS_Sell_in_YTD_QTY = "C:\Admin"
$OutputPath_DEALS_Sell_in_YTD = "C:\Admin"
$OutputPath_DEALS_Sell_in = "C:\Admin"


$FilePath_Exchange_Rates = 'C:\Users\...'
$FilePath_BTQ_Stock = 'C:\Users\...'
$FilePath_DEALS_Price = 'C:\Users\...'
$FilePath_DEALS_Sell_in_YTD_QTY = 'C:\Users\...'
$FilePath_DEALS_Sell_in_YTD = 'C:\Users\...'
$FilePath_DEALS_Sell_in = 'C:\Users\...'

function ActivateAnalysisOffice ($Excel) {
    $Excel.Application.StatusBar = "INFO - Making sure Analysis for Office addin is active..."
    foreach ($AddIn in $Excel.Application.COMAddIns) {
        if ($AddIn.progID -eq "SapExcelAddIn")
        {
            if($AddIn.Connect){
                $AddIn.Connect = $false
                $AddIn.Connect = $true
            }else{
                $AddIn.Connect = $true
            }
        }
    }    
}

########################################## File1 #################################

# start Excel
$Excel = New-Object -ComObject Excel.Application
$Excel.Visible = $True

$workbook = $Excel.Workbooks.Open($FilePath_Exchange_Rates)
ActivateAnalysisOffice($Excel)

$Excel.Application.Run("SAPSetRefreshBehaviour", "Off")
#Application.Run("SAPSetVariable", <variable technical name>, <variable value>, "INPUT_STRING", <variable datasource>)

#$Excel.Application.Run("SAPSetFilter","DS_1", "ZCMCALYEAR01", 2024, "INPUT_STRING")
$Excel.Application.Run("SAPSetVariable", "ZCMCALYEAR01", 2024, "INPUT_STRING", "DS_1")
$Excel.Application.Run("SAPSetVariable", "ZCMCALYEAR01", 2024, "INPUT_STRING", "DS_2")
$Excel.Application.Run("SAPSetVariable", "ZCMCALYEAR01", 2024, "INPUT_STRING", "DS_3")
$Excel.Application.Run("SAPSetVariable", "ZCMCALYEAR01", 2024, "INPUT_STRING", "DS_4")
$Excel.Application.Run("SAPSetVariable", "ZCMCALYEAR01", 2024, "INPUT_STRING", "DS_5")
$Excel.Application.Run("SAPSetVariable", "ZCMCALYEAR01", 2024, "INPUT_STRING", "DS_6")
$Excel.Application.Run("SAPSetVariable", "ZCMCALYEAR01", 2024, "INPUT_STRING", "DS_7")
$Excel.Application.Run("SAPSetVariable", "ZCMCALYEAR01", 2024, "INPUT_STRING", "DS_8")
$Excel.Application.Run("SAPSetRefreshBehaviour", "On")
$Excel.Application.Run("SAPExecuteCommand", "RefreshData", "DS_1")
$Excel.Application.StatusBar = "OK - Refresh Completed"


$Excel.Application.StatusBar = "OK - Refresh Completed"


#Check if file exists in given path
while (-not (Test-Path -path $OutputPath_Exchange_Rates)) {
    Write-Host "WARN - File not found. Waiting for 30 Seconds..."
    Start-Sleep -Seconds 30

    $workbook.SaveAs($OutputPath_Exchange_Rates, 6)
}

Write-Host "OK - File found. Waiting for 15 Seconds to generate file."
Start-Sleep -Seconds 30

$workbook.Close($false)
$Excel.Quit()

[System.Runtime.InteropServices.Marshal]::ReleaseComObject($workbook) | Out-Null
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($Excel) | Out-Null


########################################## File2 ######################################
# start Excel
$Excel = New-Object -ComObject Excel.Application
$Excel.Visible = $True


$workbook = $Excel.Workbooks.Open($FilePath_BTQ_Stock, $null, $false, [Type]::Missing, [Type]::Missing, [Type]::Missing, $true)
ActivateAnalysisOffice($Excel)

$Excel.Application.Run("SAPSetRefreshBehaviour", "Off")
#Application.Run("SAPSetVariable", <variable technical name>, <variable value>, "INPUT_STRING", <variable datasource>)

#$Excel.Application.Run("SAPSetFilter","DS_1", "ZCMCALYEAR01", 2024, "INPUT_STRING")
$Excel.Application.Run("SAPSetRefreshBehaviour", "On")
$Excel.Application.Run("SAPExecuteCommand", "RefreshData", "DS_1")
$Excel.Application.StatusBar = "OK - Refresh Completed"


$Excel.Application.StatusBar = "OK - Refresh Completed"


#Check if file exists in given path
while (-not (Test-Path -path $OutputPath_BTQ_Stock)) {
    Write-Host "WARN - File not found. Waiting for 30 Seconds..."
    Start-Sleep -Seconds 30

    $workbook2.SaveAs($OutputPath_BTQ_Stock, 6)
}

Write-Host "OK - File found. Waiting for 15 Seconds to generate file."
Start-Sleep -Seconds 30

$workbook.Close($false)
$Excel.Quit()

[System.Runtime.InteropServices.Marshal]::ReleaseComObject($workbook) | Out-Null
[System.Runtime.InteropServices.Marshal]::ReleaseComObject($Excel) | Out-Null ```

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