none
How to Change the Active Workbook in Excel 2016 using vb.net Excel Interop without visibility RRS feed

  • Question

  • I have an ENORMOUS amount of legacy Excel Interop. and VBA code in a test laboratory. Excel 2010 works great, without issue. However, when executed with Excel 2016 I am unable to Activate another workbook in the collection (there are several). The test rooms are not going to like a splash of Excel all over their test screens caused by setting the Application visibility to true. The macros in the sheets "assume" they are in the active workbook. When checking the active workbook, it is not the one that workbook.activate() operated on.

    Help Please, at wits end with this.

    Ypsipilot

    Monday, January 8, 2018 6:57 PM

Answers

  • I have a solution to my issue!  I don't like it though.  I wish I could just simply focus on a particular workbook while they are hidden.  So here goes.  Several engineers were scouring the web for help on this because this issue could be a show stopper for our testing platforms - readers understand:  millions of dollars.

    The solution is to open a new window in the workbook.  This will promote the workbook to the active workbook.  But you have an extra window hanging around.  This piece of code should also trim the extra windows back before creating a new one.  The code is as follows:

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

    If objExcel.Application.Visible = False Then ' True Then
    	If objExcel.ActiveWorkbook.Name <> ExcelSelectedWB.Name Then
    		While ExcelSelectedWB.Windows.Count > 1
    			ExcelSelectedWB.Windows(-1).Close()
    		End While
    		ExcelSelectedWB.NewWindow()
    	End If
    End If
    

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

    And when the workbook is made visible that extra window needs to be closed (for proper appearance). But (horribly), in order to do this, the Application need to be set to Visible = True.  But it happens pretty fast.

    objExcel.Application.Visible = True
    					
    While ExcelSelectedWB.Windows.Count > 1
        ExcelSelectedWB.Windows(1).Close()
    End While
    
    And That's That!

    • Edited by Ypsipilot Thursday, January 11, 2018 3:10 PM
    • Marked as answer by Ypsipilot Thursday, January 11, 2018 3:10 PM
    Thursday, January 11, 2018 2:56 PM

All replies

  • Hello Ypsipilot,

    Could you please share us what's code you are using and detail us your repro steps and tell us your testing result so we could try to reproduce your issue and compare our test result. Thanks for understanding.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, January 9, 2018 2:43 AM
  • Sharing all of the code would be difficult since the Application executes a home spun language that the test engineers write. But I will try and explain what is going on in the core of the Application.

    This problem occurs because I have to support 2 workbooks - both open and hidden. The active workbook follows the last opened workbook. So, workbook A is opened and is the active workbook.  Then workbook B is opened.  Now workbook B has the focus / and is the active workbook. Now the script ask for a range on workbook A.  That range pertains to one of the 8 sheets in workbook A. The core application calls workbook A.Activate().  But Workbook B remains the active workbook.  No worries yet though.  In the core .NET code I have removed any dependency on the Active workbook by using a workbook object.  This works until the worksheet change event - written in VBA fires in workbook A.  The code in the change event is very legacy as it does not use Thisworkbook or Me in front of some worksheet code.  This is where it fails.  The change event code is as follows and the failure location is highlighted (it assumes it is in the Active workbook) we are currently playing with NewWindow() when the workbook changes and are able to promote workbook A to the Active workbook (and the event runs correctly)...  But, I would like a "sane" way to do this and there doesn't appear to be any.. :-O

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

    Private Sub Worksheet_Change(ByVal Target As Range)
        Application.ScreenUpdating = False
        Application.DisplayAlerts = False
        Application.EnableEvents = False

        Dim TestPoint_Type As Range
        Dim Load_Setpoint As Range
        Dim Cell_Value_Is_Number_Flag As Boolean

        Set TestPoint_Type = Worksheets("Test_Setup").Range("Testpoint_Type")
        Set Load_Setpoint = Worksheets("Test_Setup").Range("Load_Setpoint")

    Tuesday, January 9, 2018 4:19 PM
  • Hello Ypsipilot,

    Thanks for sharing the details and I think I could reproduce your issue now.

    I failed to activate workbookA while application visible is false too.

    As workaround, you could add ThisWorkbook in front of the workSheets to make it refers to workbookA it self.

    Besides, you could also use two application instances, One is used for opening WorkbookA and another is used for opening WorkbookB. So the code in the event could refer to WorkbookA since its application has only one workbook opened.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, January 10, 2018 6:35 AM
  • I have a solution to my issue!  I don't like it though.  I wish I could just simply focus on a particular workbook while they are hidden.  So here goes.  Several engineers were scouring the web for help on this because this issue could be a show stopper for our testing platforms - readers understand:  millions of dollars.

    The solution is to open a new window in the workbook.  This will promote the workbook to the active workbook.  But you have an extra window hanging around.  This piece of code should also trim the extra windows back before creating a new one.  The code is as follows:

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

    If objExcel.Application.Visible = False Then ' True Then
    	If objExcel.ActiveWorkbook.Name <> ExcelSelectedWB.Name Then
    		While ExcelSelectedWB.Windows.Count > 1
    			ExcelSelectedWB.Windows(-1).Close()
    		End While
    		ExcelSelectedWB.NewWindow()
    	End If
    End If
    

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

    And when the workbook is made visible that extra window needs to be closed (for proper appearance). But (horribly), in order to do this, the Application need to be set to Visible = True.  But it happens pretty fast.

    objExcel.Application.Visible = True
    					
    While ExcelSelectedWB.Windows.Count > 1
        ExcelSelectedWB.Windows(1).Close()
    End While
    
    And That's That!

    • Edited by Ypsipilot Thursday, January 11, 2018 3:10 PM
    • Marked as answer by Ypsipilot Thursday, January 11, 2018 3:10 PM
    Thursday, January 11, 2018 2:56 PM