我正在运行一个批处理文件,该文件执行一个VBScript,它在Bloomberg终端上的Excel工作表中运行一个宏 .
excel表在单元格中包含许多BDP公式 . 一切都很好 . 最初,我有一个问题,从Bloomberg更新数据并运行宏,但这是通过使用bloombergui.xla.RefreshAllStaticData计时器解决的 .
在excel中手动执行时,宏运行完美但在尝试通过批处理和VBS自动执行时,我得到“运行时错误1004找不到bloombergui.xla ...” .
任何想法如何解决这个问题?我想我已经用尽谷歌的所有选项 .
宏:
Sub UpdateWeekly()
Application.Run "bloombergUI.xla!RefreshAllStaticData"
Application.OnTime (Now + TimeValue("00:00:25")), "WeeklyPDF"
End Sub
Sub WeeklyPDF()
Application.ScreenUpdating = True
ActiveSheet.Range("A1:V225").Select
Selection.ExportAsFixedFormat _
Type:=xlTypePDF, _
Filename:="O:\LOCATION" & Format(Date, "MMMM-DD-YYYY") & " " & "Weekly", _
Quality:=xlQualityStandard, _
IncludeDocProperties:=True, _
IgnorePrintAreas:=False, _
OpenAfterPublish:=False
Application.PrintCommunication = False
End Sub
VBScript中:
Dim args, objExcel
Set args = WScript.Arguments
Set objExcel = CreateObject("Excel.Application")
objExcel.Workbooks.Open args(0)
objExcel.Visible = True
objExcel.Run "UpdateCreditWeekly"
objExcel.ActiveWorkbook.Close(0)
objExcel.Quit
2 回答
当Excel以编程方式实例化时,Bloomberg加载项无法加载...我认为您需要每次都重新加载它 .
Ensure the AddIn is there before calling
Application.Run "bloombergUI.xla!RefreshAllStaticData"
If Add-in is not there then you need to Add/load add-in
OR
我相信你的语法不正确 .
此外,如果你因为我认为
XLA
是一个较旧的文件类型而被XLA
替换了're using a newer version of Excel you' .有关详细信息,请参阅以下链接, especially this one and this one .
更多信息:
MSDN:Application.Run method (Excel)
Ron de Bruin:How do I use Application.Run in Excel
Stack Overflow:Run Excel Macro from Outside Excel Using VBScript From Command Line
Stack Overflow:How do I call an xll addin function from vba?