-
-
Save christlc/15951dfbceacfe78bce343a9955085fe to your computer and use it in GitHub Desktop.
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() |
@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
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