**Scenario:** I have two workbooks, one of them contains VBA (WB1) code and the other (WB2) contains calls to an API for data collection (e.g. Bloomberg Add in function). When the code in WB1 is run, it starts a loop of identifiers, for each, it opens an instance of WB2, and tries to recalculate all functions. Once that is done, it copies some of the data of WB2 to WB1, saves WB2 with the identifier name, closes it and moves on to the next identifier.

**Issue:** As each of the API calls in WB2 take some time to process ad retrieve data, the VBA script does not wait for the functions to be calculated, it just copies the same data and moves on in the loop. Consequently, the data copied to WB1 is incorrect.

**What was tried so far:** I used a series of Calculate commands in VBA, also used loops to with DoEvents and tried to set up a counter in WB2 with the number of cells with data still pending calculation. In all these cases, the functions are still not fully calculated.

**Obs.** In this case, as this is not a specific problem to a single API (e.g. Bloomberg) the solution needs to come from a VBA command in the script of WB1.

**Code so far:**

```
Private Sub DownloadData()

    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

    ' clear raw data sheets
    Dim mainwb As Workbook
    Set mainwb = ThisWorkbook
    ThisWorkbook.Worksheets(wsRawClassData.Name).UsedRange.ClearContents

    Dim wsas As Variant
    wsas = Evaluate(ThisWorkbook.Names("WSATickers").Value)

    ' path
    Dim xlsPath As String
    xlsPath = Evaluate(ThisWorkbook.Names("Path").Value)
    If xlsPath = "" Then
        xlsPath = ThisWorkbook.Path
    End If

    Dim c As Integer
    For c = 1 To 100
        If wsas(c, 1) = "" Then Exit For

        Dim objXL
        Set objXL = CreateObject("Excel.Application")

        Dim objXLWB
        Set objXLWB = Workbooks.Open(xlsPath & "WB2.xlsm")

        objXLWB.Worksheets("Data").Range("Identifier").Value = wsas(c, 1)
        Application.Calculation = xlManual
        Application.Calculation = xlAutomatic

        'wait for initial calculations
        Do While objXL.CalculationState <> xlDone
            DoEvents
        Loop

        ' Recalculation forcing:
        objXLWB.Activate
        Application.Calculation = xlManual
        Application.Calculation = xlAutomatic
        Application.CalculateFull
        Application.Calculation = xlAutomatic
        Dim wsobj As Variant
        For Each wsobj In objXLWB.Worksheets
            wsobj.Calculate
                Do While objXLWB.Worksheets("Data").Range("calcpend").Value <> 0
                    Application.Wait (Now + TimeValue("0:00:02"))
                Loop
        Next wsobj
        Application.Calculation = xlAutomatic
        mainwb.Activate

        Do Until objXLWB.Worksheets("Data").Range("calcpend").Value = 0
            DoEvents
        Loop

        ThisWorkbook.Worksheets(wsRawData.Name).Range("A" & (c + 1)).Value = wsas(c, 1)

        ' save, close, quit
        objXLWB.SaveAs Filename:=xlsPath & wsas(c, 1) & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled
        objXLWB.Close
        objXL.Quit

    Next c

End Sub

```