Bloomberg公式刷新

Question

我正在尝试自动更新彭博。该过程分为三个部分，并在循环中运行。

a.从数据库复制代码

b.刷新彭博

c.运行分析并将结果复制到数据库和循环中，直到最后记录为止。

从我在这个网站和其他与彭博相关的讨论中得到的问题是，当宏运行时，Bloomberg公式不会刷新。

我如何破解我的代码以便彭博更新？

Sub Run_SplinterSequence()

'
'This macro automates the processing of a list of records.
'When executed the list of records is splintered into individual rows which are then run through an analysis process.
'The output results are then appended to the end of each record in the list.
'The process will loop through each row of data in the list until all rows are processed.
'
'Macro by SD.
'

Dim ClipSnip As String

Debug.Print "Running initial data preparation sequence..." & vbNewLine

'Copy 'Raw_Data' data to 'Process_Data' tab
Debug.Print "Transferring applicable data from 'Raw_Data' to 'Process_Data' tab..."
    Application.ScreenUpdating = False
    Sheets("Raw_Data").Select
    Range("A1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$P$110").AutoFilter Field:=11, Criteria1:="GO"
    Columns("B:P").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Process_Data").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Debug.Print "Data transfer completed." & vbNewLine


'Calculate total number of rows for splinter sequence
Debug.Print "Calculating total volume for splinter sequence..."

    Sheets("Process_Data").Select
    Range("P2").Select
    ActiveCell.FormulaR1C1 = "=COUNTA(C[-12])"
    Range("P2").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("Q2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

    Dim maxRowNum As Long
    maxRowNum = Range("Q2").Value

Debug.Print "-Data rows: " & maxRowNum & ", Record splinters: " & maxRowNum - 1 & "." & vbNewLine

Debug.Print "Initiating Splinter Sequence..." & vbNewLine

Call SplinterSplash

'Transfer record splinter values + execute data analysis process.

    Dim i As Long
    Dim timeval As Date

    Sheets("Splinter").Select
    timeval = Range("K3").Value


    For i = 2 To maxRowNum

Debug.Print "'Control Panel' data transfer sequence initiated..."
Debug.Print "Transferring data splinter: " & i - 1 & "..."

    '1st Currency (Base currency (currency 1))
    Sheets("Process_Data").Select
    Range("D" & i).Select
    Application.CutCopyMode = False
    Selection.Copy

    ClipSnip = CStr(Range("D" & i).Value)
    Debug.Print "Process_Data Cell D" & i & " Val: '" & CStr(ClipSnip) & "' Copied to Clipboard"

        Sheets("control panel").Select
        Range("D5").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False

        ClipSnip = CStr(Range("D5").Value)
        Debug.Print "control panel Cell D5 Val: '" & CStr(ClipSnip) & "' Pasted from Clipboard"

    '2nd Currency (Accounting currency (currency 2))
    Sheets("Process_Data").Select
    Range("E" & i).Select
    Application.CutCopyMode = False
    Selection.Copy

    ClipSnip = CStr(Range("E" & i).Value)
    Debug.Print "Process_Data Cell E" & i & " Val: '" & CStr(ClipSnip) & "' Copied to Clipboard"

        Sheets("control panel").Select
        Range("D7").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False

        ClipSnip = CStr(Range("D7").Value)
        Debug.Print "control panel Cell D7 Val: '" & CStr(ClipSnip) & "' Pasted from Clipboard"

    'Currency of the Principal Amount
    Sheets("Process_Data").Select
    Range("F" & i).Select
    Application.CutCopyMode = False
    Selection.Copy

    ClipSnip = CStr(Range("F" & i).Value)
    Debug.Print "Process_Data Cell F" & i & " Val: '" & CStr(ClipSnip) & "' Copied to Clipboard"

        Sheets("control panel").Select
        Range("D13").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False

        ClipSnip = CStr(Range("D13").Value)
        Debug.Print "control panel Cell D13 Val: '" & CStr(ClipSnip) & "' Pasted from Clipboard"

    'Tenor
    Sheets("Process_Data").Select
    Range("H" & i).Select
    Application.CutCopyMode = False
    Selection.Copy

    ClipSnip = CStr(Range("H" & i).Value)
    Debug.Print "Process_Data Cell H" & i & " Val: '" & CStr(ClipSnip) & "' Copied to Clipboard"

        Sheets("control panel").Select
        Range("D15").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False

        ClipSnip = CStr(Range("D15").Value)
        Debug.Print "control panel Cell D15 Val: '" & CStr(ClipSnip) & "' Pasted from Clipboard"


    'Strike % (Set OTM strike x% away from Spot)
    Sheets("Process_Data").Select
    Range("I" & i).Select
    Application.CutCopyMode = False
    Selection.Copy

    ClipSnip = CStr(Range("I" & i).Value)
    Debug.Print "Process_Data Cell I" & i & " Val: '" & CStr(ClipSnip) & "' Copied to Clipboard"

        Sheets("control panel").Select
        Range("D17").Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False

        ClipSnip = CStr(Range("D17").Value)
        Debug.Print "control panel Cell D17 Val: '" & CStr(ClipSnip) & "' Pasted from Clipboard" & vbNewLine

    Sheets("bloomberg link").Select
    Rows("39:39").Select

    Application.Run "RefreshEntireWorksheet"

    Debug.Print "Splinter transfer complete." & vbNewLine

    Debug.Print "Initiating time delay sequence..."
    Debug.Print "Pause delay set to " & timeval & ", Pause initiation time: " & Now()

    Application.Wait (Now + TimeValue(timeval))

    Debug.Print "Pause terminated @: " & Now() & vbNewLine

    'Tweaked here

    Sheets("control panel").Select
    Range("P16").Select
    Application.CutCopyMode = False
    Selection.Copy

    ClipSnip = CStr(Range("P16").Value)
    Debug.Print "Control Panel Cell P16 Val: '" & CStr(ClipSnip) & "' Copied to Clipboard"

        Sheets("Process_Data").Select
        Range("M" & i).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
         :=False, Transpose:=False

        ClipSnip = CStr(Range("M" & i).Value)
        Debug.Print "Process_Data Cell M" & i & " Val: '" & CStr(ClipSnip) & "' Pasted from Clipboard"

    Debug.Print "Initiating splinter analysis sequence..."

    Call RunAnalysis

    Calculate

    'Auto calculation off
    'Application.Calculation = xlManual

    Debug.Print "Splinter analysis complete." & vbNewLine

    Debug.Print "Transferring splinter: " & i - 1 & " output values to 'Process_Data' tab..."
'output data fields
    Sheets("control panel").Select
    Range("P13").Select
    Application.CutCopyMode = False
    Selection.Copy

    ClipSnip = CStr(Range("P13").Value)
    Debug.Print "Control Panel Cell P13 Val: '" & CStr(ClipSnip) & "' Copied to Clipboard"

        Sheets("Process_Data").Select
        Range("K" & i).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
         :=False, Transpose:=False

        ClipSnip = CStr(Range("K" & i).Value)
        Debug.Print "Process_Data Cell K" & i & " Val: '" & CStr(ClipSnip) & "' Pasted from Clipboard"


    Sheets("control panel").Select
    Range("Q13").Select
    Application.CutCopyMode = False
    Selection.Copy

    ClipSnip = CStr(Range("Q13").Value)
    Debug.Print "Control Panel Cell Q13 Val: '" & CStr(ClipSnip) & "' Copied to Clipboard"

        Sheets("Process_Data").Select
        Range("L" & i).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
         :=False, Transpose:=False

        ClipSnip = CStr(Range("L" & i).Value)
        Debug.Print "Process_Data Cell L" & i & " Val: '" & CStr(ClipSnip) & "' Pasted from Clipboard"


'    Sheets("control panel").Select
'    Range("P16").Select
'    Application.CutCopyMode = False
'    Selection.Copy
'
'    ClipSnip = CStr(Range("P16").Value)
'    Debug.Print "Control Panel Cell P16 Val: '" & CStr(ClipSnip) & "' Copied to Clipboard"
'
'        Sheets("Process_Data").Select
'        Range("M" & i).Select
'        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
'         :=False, Transpose:=False
'
'        ClipSnip = CStr(Range("M" & i).Value)
'        Debug.Print "Process_Data Cell M" & i & " Val: '" & CStr(ClipSnip) & "' Pasted from Clipboard"


    Sheets("control panel").Select
    Range("Q16").Select
    Application.CutCopyMode = False
    Selection.Copy

    ClipSnip = CStr(Range("Q16").Value)
    Debug.Print "Control Panel Cell Q16 Val: '" & CStr(ClipSnip) & "' Copied to Clipboard"

        Sheets("Process_Data").Select
        Range("N" & i).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
         :=False, Transpose:=False

        ClipSnip = CStr(Range("N" & i).Value)
        Debug.Print "Process_Data Cell N" & i & " Val: '" & CStr(ClipSnip) & "' Pasted from Clipboard"


    Sheets("control panel").Select
    Range("P18").Select
    Application.CutCopyMode = False
    Selection.Copy

    ClipSnip = CStr(Range("P18").Value)
    Debug.Print "Control Panel Cell P18 Val: '" & CStr(ClipSnip) & "' Copied to Clipboard"

        Sheets("Process_Data").Select
        Range("O" & i).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
         :=False, Transpose:=False

        ClipSnip = CStr(Range("O" & i).Value)
        Debug.Print "Process_Data Cell O" & i & " Val: '" & CStr(ClipSnip) & "' Pasted from Clipboard" & vbNewLine


    'Auto calculation On
    'Application.Calculation = xlAutomatic

    Debug.Print "Splinter output transfer complete!" & vbNewLine


    If i < maxRowNum Then
        Debug.Print "Splinter sequence: " & i - 1 & " of: " & maxRowNum - 1 & " complete, looping to next splinter sequence..." & vbNewLine
    Else
        Debug.Print "Splinter sequence: " & i - 1 & " of: " & maxRowNum - 1 & " complete." & vbNewLine
        Debug.Print "All splinter sequences completed successfully! ;D" & vbNewLine

    End If

    Next i

End Sub

Answer 1

要执行彭博数据刷新(就像手动按彭博加载项上的按钮一样)，您需要知道哪个AddIn文件和Sub名称。

从某个地方我忘了，一点谷歌，并发现它是BloombergUI.xla!RefreshAllStaticData为我所需要的。在调用它之前，确保AddIn在那里。

一旦它被调用，我设置了一个“计时器”来检查彭博的计算状态，然后再继续(通过计算#VALUE!).的数量)这就是我所观察到的，并对其进行编码。

Private Const BRG_ADDIN As String = "BloombergUI.xla"
Private Const BRG_REFRESH As String = "!RefreshAllStaticData"
Private TimePassed As Integer

Sub StartAutomation()
    Dim oAddin As Workbook
    On Error Resume Next
    Set oAddin = Workbooks(BRG_ADDIN)
    On Error GoTo 0
    If Not oAddin Is Nothing Then
        Application.Run BRG_ADDIN & BRG_REFRESH
        StartTimer
    End If
End Sub

Private Sub StartTimer()
    TimePassed = 0
    WaitTillUpdateComplete
End Sub

Sub WaitTillUpdateComplete()
    If WorksheetFunction.CountIf(ThisWorkbook.Names("BloombergDataRange").RefersToRange, "#VALUE!") = 0 Then
        Application.StatusBar = "Data update used " & TimePassed & "seconds, automation started at " & Now
    Else
        Application.StatusBar = "Waiting for Bloomberg Data to finish updating (" & TimePassed & " seconds)..."
        TimePassed = TimePassed + 1
        Application.OnTime Now + TimeSerial(0, 0, 1), "WaitTillUpdateComplete"
    End If
End Sub