Powershell Excel automatic Bloomberg add-in

Question

I have a problem with automatic run of Excel with Bloomberg add-in included.

When I manually open Excel worksheet, data functions from Bloomberg add-in automatically run. But when I open same sheet with Powershell and save, there aren't any loaded data

Code - Start Excel and open Workbook:

$xls = New-Object -ComObject Excel.Application
$xls_workbook = $xls.Workbooks.Open("Market_data.xlsx")
$xls_workbook.Activate()

I have tried to force recalculation by these methods:

$xls.Calculate()
$xls.CalculateFull()
$xls.CalculateFullRebuild()
$xls.Workbooks.Application.CalculateFullRebuild()
$xls_workbook.Worksheets(1).Calculate()

But nothing worked. Strange, because, as I have mentioned, manual opening of Excel sheet causes that data from Bloomberg are loaded automatically.

Do you have some experience with this Bloomberg add-in automation? I wanted to check also .xla macros that are included (BloombergUI.xla, BloombergHistory.xla) but they are protected by passwords. Maybe, is there any option to force running of all Add-ins in Excel? Or is there any call like $xls.Application.Run() that can run this add-in?

Thank you

Whole code:

$xls = New-Object -ComObject Excel.Application
$xls_workbook = $xls.Workbooks.Open("MarketData.xlsx")
$xls_workbook.Activate()

#calculation
$xls.Calculate()
#$xls_workbook.Aplication.Run("RefreshAllStaticData") - THIS RETURNS ERROR, THAT MACRO IS NOT AVAILABLE OR MACROS ARE DISABLED

#my current option of waiting
$internal_timeout = new-timespan -Seconds $timeout
$sw = [diagnostics.stopwatch]::StartNew()
while ($sw.elapsed -lt $internal_timeout){
}

#maybe next option, how to wait until job finished
#$job = Start-Job -ScriptBlock {
   #docalculation
#}
#Wait-Job $job -Timeout $timeout | out-null

$date = Get-Date -Format M_dd_yyyy
$file_to_save = "MarketData_$date.xlsx"
$xls_workbook.SaveAs($file_to_save)

$xls_workbook.Close();
$xls.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($xls)

The addin probably doesn't have enough time to load the data. You can either wait before closing (but how long is difficult to say), or better, have a formula that counts the number of "#N/A Requesting Data" cells, check it periodically until it reaches 0 then close the workbook. — assylias, Nov 04 '16 at 13:35
See also: http://stackoverflow.com/questions/8669845/bloomberg-data-doesnt-populate-until-excel-vba-macro-finishes — assylias, Nov 04 '16 at 13:37
Maybe there is problem of my waiting loop. I have added whole code to my Question above. Maybe you can check, if it gives sense. Thank you very much! $timeout is parameter and i have tried to set it to many options like 15seconds, 30 seconds etc. ) — Martin Fedy Fedorko, Nov 04 '16 at 13:56

score 1 · Answer 1 · answered May 12 '17 at 13:14

This code worked for me:

    $xls = New-Object -ComObject Excel.Application
    $xls.Workbooks.Open("C:\blp\API\Office Tools\BloombergUI.xla")
    Write-Debug "$file_path$file_name$file_ext"
    $xls_workbook = $xls.Workbooks.Open("$file_path$file_name$file_ext")
    $xls_workbook.Activate()
    $xls_workbook.Application.Run("RefreshAllWorkbooks")
    $xls_workbook.Application.Run("RefreshAllStaticData")
    $xls.CalculateFull()

    Start-Sleep -s $timeout


    $date = Get-Date -Format M_dd_yyyy
    $file_to_upload = "$file_path$file_name$date$file_ext"
    Write-Debug $file_to_upload
    $xls_sheet = $xls_workbook.Sheets.Item(1)
    $bl_value = $xls_sheet.Cells.Item(2,9).Text
    Write-Host $bl_value
    $xls_workbook.SaveAs($file_to_upload)

    $xls_workbook.Close()

score 0 · Answer 2 · answered May 09 '17 at 16:51

0

the bloomberg addin is not loaded at all when you use "New-Object -ComObject Excel.Application" to open excel. you can double check by alt+F11 in vba environment

answered May 09 '17 at 16:51

dfgdfg

1

Powershell Excel automatic Bloomberg add-in

2 Answers2