excel - VBA是否可以等到Bloomberg字段填充后再关闭工作表

Question

我正在编写VBA代码来执行以下操作：

打开一个Excel文件->从Bloomberg填充一系列单元格->关闭并保存Excel文件。

他们用我编写的代码的方式将Excel文件关闭，然后用Bloomberg价格填充单元格。知道如何修改它，以便它可以在关闭文件之前等待计算完成吗？

Sub GetSpreads()

Workbooks.Open Filename:= _
        "G:\Quant\Projects\Corp PD+LGD\PD Database\PD Database.xlsm", UpdateLinks:=0

    Dim ws As Worksheet
    Dim rngTkr As Range
    Dim strTkr As String
    Dim c As Range
    Dim strFld As String
    Dim ws2 As Worksheet
    Dim dtBeg As Date
    Dim dtEnd As Date
    Dim strDtBeg As String
    Dim strdtEnd As String

    Windows("PD Database.xlsm").Activate

    Set ws = ActiveWorkbook.Sheets("Sheet3")
    Set ws2 = ActiveWorkbook.Sheets("Sheet3")

    ws.Activate
    Range("B2").Select
    With ws
        Set rngTkr = .Range(Selection, Selection.End(xlDown)) ' ticker range
        i = 1
        For Each c In rngTkr

            strTkr = """" & c.Value & " Corp"""
            strFld = """" & "BLOOMBERG_MID_G_SPREAD" & """"
            strSort = """" & "Sort=D" & """"
            dtBeg = Cells(1, 9) ' adjust start date as you want
            dtEnd = Cells(2, 9) ' adjust end date as you want
            strDtBeg = """" & dtBeg & """"
            strdtEnd = """" & dtEnd & """"
            ws2.Range("N1").Offset(1, i).Value = "=BDH(" & strTkr & "," & strFld & "," & strDtBeg & "," & strdtEnd & "," & strSort & ")"
            ws2.Range("N1").Offset(0, i).Value = c.Value
            i = i + 2
        Next
    End With

Workbooks("PD Database.xlsm").Close savechanges:=True

End Sub

Answer 1

如果我没记错的话，那是没有真正的方法的-因为BBG使用某种DDE机制，所以Workbook.Calculate不会等待彭博社更新。

一种方法是每秒检查一次，以便通过检查范围内的值来检查是否刷新了值。

有关详细信息，请参见http://www.vonixx.com/tips/tips34.htm。