none
Excel 2016 bug in Workbooks.Open of already open workbook; returns wrong object RRS feed

  • Question

  • I would have expected workbooks.open of an already open book to give a warning about "already open".

    It does not. Furthermore, it returns a variable pointing to another workbook, neither the active one nor the requested one.

    Anybody know why this happens? And why their Application.Hwnd changes ? See below for test code.

    In Excel 2010 and 2013, WB3 correctly points to  testWorkbooksOpenBug1 and WB1.Name raises an Automation error, as expected.

    Possibly related:

    https://stackoverflow.com/questions/48233967/excel-vba-workbooks-open-returns-the-wrong-object
    https://social.msdn.microsoft.com/Forums/en-US/f3703bab-76f6-4cc0-8a77-84af597d6550/workbooksopen-returns-wrong-object?forum=exceldev


    ----------------------------------------------------------------------------------------------------------------------------------------------

    Option Explicit

    Sub testWorkbooksOpenBug()
    Dim wb As Workbook, wb1 As Workbook, wb2 As Workbook, wb3 As Workbook
    Debug.Print "Test Workbooks.Open bug in Excel "; Application.Version
    ChDrive ThisWorkbook.Path
    ChDir ThisWorkbook.Path
    For Each wb In Workbooks
       If Not wb Is ThisWorkbook Then
          Debug.Print "Closing "; wb.FullName
          wb.Close
       End If
    Next
    Debug.Print "ActiveWorkbook "; ActiveWorkbook.Name; " Hwnd"; ActiveWorkbook.Application.Hwnd
    Debug.Print "ThisWorkbook   "; ThisWorkbook.Name; " Hwnd"; ThisWorkbook.Application.Hwnd

    ThisWorkbook.SaveCopyAs "testWorkbooksOpenBug1.xlsm"
    ThisWorkbook.SaveCopyAs "testWorkbooksOpenBug2.xlsm"

    Debug.Print "Opening        "; "testWorkbooksOpenBug1.xlsm"
    Set wb1 = Workbooks.Open("testWorkbooksOpenBug1.xlsm")
    Debug.Print "ActiveWorkbook "; ActiveWorkbook.Name; " Hwnd"; ActiveWorkbook.Application.Hwnd
    Debug.Print "WB1            "; wb1.Name; " Hwnd"; wb1.Application.Hwnd

    Debug.Print "Opening        "; "testWorkbooksOpenBug2.xlsm"
    Set wb2 = Workbooks.Open("testWorkbooksOpenBug2.xlsm")
    Debug.Print "ActiveWorkbook "; ActiveWorkbook.Name; " Hwnd"; ActiveWorkbook.Application.Hwnd
    Debug.Print "WB2            "; wb2.Name; " Hwnd"; wb2.Application.Hwnd

    Debug.Print "ThisWorkbook.Activate"
    ThisWorkbook.Activate
    Debug.Print "ActiveWorkbook "; ActiveWorkbook.Name; " Hwnd"; ActiveWorkbook.Application.Hwnd
    Debug.Print "ThisWorkbook   "; ThisWorkbook.Name; " Hwnd"; ThisWorkbook.Application.Hwnd

    Debug.Print "Opening(again) "; "testWorkbooksOpenBug1.xlsm"
    Set wb3 = Workbooks.Open("testWorkbooksOpenBug1.xlsm")
    Debug.Print "ActiveWorkbook "; ActiveWorkbook.Name; " Hwnd"; ActiveWorkbook.Application.Hwnd
    Debug.Print "WB3            "; wb3.Name; " Hwnd"; wb3.Application.Hwnd

    Debug.Print "Re-check workbook object variables"
    Debug.Print "WB1            "; wb1.Name; " Hwnd"; wb1.Application.Hwnd
    Debug.Print "WB2            "; wb2.Name; " Hwnd"; wb2.Application.Hwnd
    Debug.Print "WB3            "; wb3.Name; " Hwnd"; wb3.Application.Hwnd

    Debug.Print "Each wb In " & Workbooks.Count & " Workbooks"
    For Each wb In Workbooks
       Debug.Print wb.FullName; wb.Application.Hwnd
    Next
    End Sub

    'Test Workbooks.Open bug in Excel 16.0
    'ActiveWorkbook TestWorkbooksOpenBug.xlsm Hwnd 4332670
    'ThisWorkbook   TestWorkbooksOpenBug.xlsm Hwnd 4332670
    'Opening testWorkbooksOpenBug1.xlsm
    'ActiveWorkbook testWorkbooksOpenBug1.xlsm Hwnd 7409546
    'WB1            testWorkbooksOpenBug1.xlsm Hwnd 7409546
    'Opening testWorkbooksOpenBug2.xlsm
    'ActiveWorkbook testWorkbooksOpenBug2.xlsm Hwnd 7016956
    'WB2            testWorkbooksOpenBug2.xlsm Hwnd 7016956
    'ThisWorkbook.Activate
    'ActiveWorkbook TestWorkbooksOpenBug.xlsm Hwnd 4332670
    'ThisWorkbook   TestWorkbooksOpenBug.xlsm Hwnd 4332670
    'Opening(again) testWorkbooksOpenBug1.xlsm
    'ActiveWorkbook testWorkbooksOpenBug1.xlsm Hwnd 7409546
    'WB3            testWorkbooksOpenBug2.xlsm Hwnd 7409546
    'Re-check workbook object variables
    'WB1            testWorkbooksOpenBug1.xlsm Hwnd 7409546
    'WB2            testWorkbooksOpenBug2.xlsm Hwnd 7409546
    'WB3            testWorkbooksOpenBug2.xlsm Hwnd 7409546
    'Each wb In 3 Workbooks
    'F:\DOCS\Excel\TestWorkbooksOpenBug.xlsm 7409546
    'F:\DOCS\Excel\testWorkbooksOpenBug1.xlsm 7409546
    'F:\DOCS\Excel\testWorkbooksOpenBug2.xlsm 7409546
    Wednesday, April 10, 2019 11:19 AM