Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save yuhisern7/44dab95ef1ee2ddb7297a75fb5329716 to your computer and use it in GitHub Desktop.
Save yuhisern7/44dab95ef1ee2ddb7297a75fb5329716 to your computer and use it in GitHub Desktop.
SAP Analysis Office Excel Auto refresh with powershell (without Excel Macro)
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()
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment