通过VBS运行含Bloomberg刷新的Excel宏时触发1004运行时错误

我来帮你拆解这个问题——直接在Excel里运行宏一切正常，但通过VBS调用时触发1004运行时错误，核心原因基本是后台Excel实例的运行环境和你手动打开的Excel存在差异，下面是具体的排查点和修复方案：

可能的触发原因 #

• VBS创建的Excel实例默认是后台不可见状态，而Bloomberg的RefreshAllStaticData宏可能依赖Excel的可见UI才能完成数据刷新（比如需要加载Bloomberg插件的交互组件）。
• 宏里依赖的ActiveWorkbook在后台环境中可能指向错误的工作簿，毕竟后台运行时没有用户操作来确保激活状态，导致后续SaveAs操作失败。
• Application.OnTime在无UI的Excel实例中可能无法正常调度后续宏，或者触发权限限制。
• VBS运行的用户上下文可能没有目标保存文件夹的写入权限。

具体修复方案 #

1. 让Excel实例可见，确保Bloomberg插件正常工作 #

修改你的VBS脚本，添加objXL.Visible = True让Excel前台运行，同时可以加一段延迟等待Bloomberg插件加载完成：

dim objXL
dim objWkb
dim strPath
strPath = "S:\Back Office\Tradar\Daily Report Files_Bloomberg\Custom_Daily_Report_BDP_20190807 with Macro_Template.xlsm"
Set objXL = CreateObject("Excel.Application")
objXL.Visible = True ' 关键：让Excel可见，保证Bloomberg插件能正常加载和运行
objXL.DisplayAlerts = False ' 关闭不必要的弹窗干扰
Set objWkb = objXL.Workbooks.Open(strPath)
WScript.Sleep 5000 ' 延迟5秒，给Bloomberg插件足够的加载时间
objXL.Run "'" & objWkb.Name & "'!Master1"
objWkb.Close SaveChanges:=False
objXL.Quit ' 记得退出Excel，避免残留后台进程
Set objWkb = Nothing
Set objXL = Nothing

2. 替换ActiveWorkbook为明确的工作簿对象 #

宏里依赖ActiveWorkbook是个隐患，尤其是在后台环境中。直接使用ThisWorkbook（当前带宏的工作簿）或者传递工作簿对象，避免激活状态错误：

Sub Master1()
    Dim targetWB As Workbook
    Set targetWB = ThisWorkbook ' 明确指向当前运行宏的工作簿
    Application.Run "RefreshAllStaticData"
    ' 把工作簿路径传递给ConvertTocsv，避免依赖ActiveWorkbook
    Application.OnTime Now + TimeValue("00:00:15"), "'" & targetWB.Name & "'!ConvertTocsv """ & targetWB.FullName & """"
End Sub

Sub ConvertTocsv(wbFullPath As String)
    Dim targetWB As Workbook
    Set targetWB = Workbooks.Open(wbFullPath)
    Dim strfilename As String
    strfilename = "S:\Back Office\Tradar\DailyReportBDP\Custom_Daily_Report_BDP_" & Format(Now(), "YYYYMMDD") & ".csv"
    
    Application.DisplayAlerts = False
    ' 直接用工作簿对象调用SaveAs，不再依赖ActiveWorkbook
    targetWB.SaveAs Filename:=strfilename, FileFormat:=xlCSV, CreateBackup:=True, Local:=True
    Application.DisplayAlerts = True
    
    Information.Show
    Dim returnvalue As Variant
    returnvalue = Shell("notepad.exe " & strfilename, vbNormalFocus)
    targetWB.Close SaveChanges:=False
End Sub

3. 替换OnTime为主动等待Bloomberg刷新完成 #

如果OnTime在后台环境还是有问题，可以放弃固定延迟，改为主动检查Bloomberg数据是否加载完成，比如监控某个包含Bloomberg公式的单元格：

' 先在模块顶部声明Sleep函数
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Sub Master1()
    Dim targetWB As Workbook
    Set targetWB = ThisWorkbook
    Application.Run "RefreshAllStaticData"
    
    ' 等待Bloomberg数据加载完成（替换为你的Bloomberg数据所在单元格）
    Do While IsError(targetWB.Sheets("你的工作表名称").Range("A1").Value)
        DoEvents ' 让Excel处理后台刷新请求
        Sleep 1000 ' 每秒检查一次
    Loop
    
    ' 直接调用转换宏，不需要延迟调度
    ConvertTocsv targetWB.FullName
End Sub

4. 验证路径权限 #

确保运行VBS的用户账户对S:\Back Office\Tradar\DailyReportBDP文件夹有写入权限——可以手动尝试在该路径创建一个文本文件，确认没有权限限制。

额外注意事项 #

• 如果Bloomberg插件在VBS启动的Excel里没加载，可以在VBS里添加一行：objXL.AddIns("Bloomberg Excel Tools").Installed = True（需要确认插件的准确名称，可在Excel插件管理里查看）。
• 一定要在VBS里加上objXL.Quit，否则会有Excel进程残留后台，占用资源。

内容的提问来源于stack exchange，提问作者Navid