我有自动运行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 回答
这段代码对我有用:
使用“New-Object -ComObject Excel.Application”打开excel时,根本不加载bloomberg插件 . 您可以在vba环境中通过alt F11仔细检查