首页 文章

Powershell Excel自动Bloomberg插件

提问于
浏览
0

我有自动运行Excel的问题,包括Bloomberg加载项 .

当我手动打开Excel工作表时,Bloomberg加载项的数据函数会自动运行 . 但是当我用Powershell打开同一张纸并保存时,没有任何加载的数据

代码 - 启动Excel并打开工作簿:

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

我试图通过这些方法强制重新计算:

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

但没有任何效果 . 奇怪,因为,正如我所提到的,手动打开Excel工作表会导致Bloomberg的数据自动加载 .

Do you have some experience with this Bloomberg add-in automation? 我想查看包含的.xla宏(BloombergUI.xla,BloombergHistory.xla),但它们受密码保护 . 也许,有没有选项强制在Excel中运行所有加载项? Or is there any call like $xls.Application.Run() that can run this add-in?

谢谢

整码:

$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)

2 回答

  • 0

    这段代码对我有用:

    $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()
    
  • 0

    使用“New-Object -ComObject Excel.Application”打开excel时,根本不加载bloomberg插件 . 您可以在vba环境中通过alt F11仔细检查

相关问题