Application.Run timing problem

[matteobusetti]

Hi all,

I am writing a macro that has to open another workbook (wb2), run a macro in wb2, copy some data and paste it to the first workbook.

However, given it takes some time for Application.Run to actually populate the excel cells in wb2, it seems like the macro i am writing selects the range to copy and copies it before Application.Run has populated the cells and therefore copy empty cells.

Is there a way to tell VBA to wait until Application.Run has populated the entire wb2 before proceeding with selecting the range to copy?

Many thanks
Matteo

Sub Import_Data1()

' Import_Data1 Macro


    'set current workbook
    Set wb1 = ActiveWorkbook
    Set ws1 = wb1.Worksheets("Performance")
    
    'open IB API workbook
    Set wb2 = Workbooks.Open("Workbook2")
    Set ws2 = wb2.Worksheets("Portfolio")
    
    'Selects tab
    Sheets("Portfolio").Select
    
    'enter username
    Range("B5").Select
    ActiveCell.FormulaR1C1 = "username"
    
    'run macro in IB API file to get portfolio data
    Application.Run "TwsDde.xls!Sheet15.subscribeToPorts"
    
    'select first 20 rows and copy
    Range("A8:B28").Select
    Selection.Copy

 

[RedBeard]

Some ideas here: https://www.myonlinetraininghub.com/pausing-or-delaying-vba-using-wait-sleep-or-a-loop

 

[matteobusetti]

Some ideas here: https://www.myonlinetraininghub.com/pausing-or-delaying-vba-using-wait-sleep-or-a-loop

Thank you but none of the solutions proposed solve the problem - i.e. the selection is still executed before value are populated by application.run

Any other ideas on what might cause this?
Thanks!

 

[JoeMo]

Untested. Does this help after you adjust the delay time for your macro to populate values?

'run macro in IB API file to get portfolio data
    Application.Run "TwsDde.xls!Sheet15.subscribeToPorts"
    T = Now
    Do While TW <= T + TimeSerial(0, 0, 2)  'Adjust delay time here
        TW = Now
        DoEvents
    Loop
    'select first 20 rows and copy
    Range("A8:B28").Select
    Selection.Copy

 

[matteobusetti]

Untested. Does this help after you adjust the delay time for your macro to populate values?

'run macro in IB API file to get portfolio data
    Application.Run "TwsDde.xls!Sheet15.subscribeToPorts"
    T = Now
    Do While TW <= T + TimeSerial(0, 0, 2)  'Adjust delay time here
        TW = Now
        DoEvents
    Loop
    'select first 20 rows and copy
    Range("A8:B28").Select
    Selection.Copy

Work great! thanks!!

 

[JoeMo]

Work great! thanks!!

You are welcome - thanks for the reply.