Workbooks.open return before focus (Excel 2010 and 2016)

Question

I have a macro that opens a new Workbook and then activate (focus) to first Workbook.

Code:
Set mainWorkbook = ActiveWorkbook
Set bdWorkbook = Workbooks.Open(FileName:="Another.xlsm", ReadOnly:=True)
mainWorkbook.Activate

I've got this code working in Excel 2007, but I've encountered an issue with the open workbook in Excel 2010 and later. The problem happens because Workbooks.Open returns to VBA before Excel Activate the new workbook [it works fine using debugger].

I can make an workarround using a Application.Wait (Now + TimeValue("0:00:01")), but......

EDIT: My code that dosen't work in Excel 2016

Sub Sample()
    Dim path As String

    path = "A_PATH_FROM_MY_SERVER"

    actualScreenUpdate = Application.ScreenUpdating
    Application.ScreenUpdating = False

    Set MainWB = ActiveWorkbook
    Workbooks.Open fileName:=path, UpdateLinks:=False, ReadOnly:=isReadOnly
    Set bdWB = ActiveWorkbook
    DoEvents

    MainWB.Activate
    Application.ScreenUpdating = actualScreenUpdate

    With Sheets(MY_BD_SHEET)
        bdWB.ActiveSheet.UsedRange.Copy .[A1]
        '....
    End With
End Sub

DoEvents between the last two lines? Otherwise but.... what? — Scott Holtzman
Quick Question. Why do you want to Activate it? Not sure if you have see THIS — Siddharth Rout
@ScottHoltzman I don't like to make workarrounds in my code. And how much wait do I need to put 1s? 10s? — Makah
Hi @SiddharthRout, I don't use Select and Activate in my code... Thats why I want to Open() and stay in my main workbook. — Makah

Siddharth Rout Siddharth Rout · Accepted Answer · 2016-04-01T18:15:58

Is this what you want? (Tried And Tested)

This will open the relevant workbook and minimize it thereby returning focus to your main workbook.

Sub Sample()
    Dim wbThis As Workbook, wbThat As Workbook

    Set wbThis = ThisWorkbook
    Set wbThat = Workbooks.Open("C:\Users\Siddharth\Desktop\Sample.xlsx")

    DoEvents

    Application.WindowState = xlMinimized

    ' OR

    ActiveWindow.WindowState = xlMinimized
End Sub

EDIT

After seeing your current Edit.

MainWB.Activate
Application.ScreenUpdating = actualScreenUpdate

You are activating when the ScreenUpdating = False? Set it to True and then Activate it :)

Workbooks.open return before focus (Excel 2010 and 2016)

1 Answers