none
Why Application.Workbooks.Add changes Application.Visible property. RRS feed

  • Question

  • Hello,
    following VBScript code snippet gives varying results on seemingly identically configured Windows machines. I cannot find out the cause of the difference. Could you?

    The snippet:

      Option Explicit

      Function Iif(exp, trueVal, falseVal)
          If exp = true Then
              Iif = trueVal
          Else
              Iif = falseVal
          End If
      End Function


      Dim App, WB, Sheet
      Dim ExcelVersion 
      Dim str

      str = ""

      Set App = CreateObject("Excel.Application")
      App.Visible = False
      App.DisplayAlerts = False
      ExcelVersion = App.Version

      WScript.Echo "App.Visible = " & Iif(App.Visible = TRUE, "True", "False")

      Set WB = App.Workbooks.Add
      WScript.Echo "Workbook added."

      WScript.Echo "App.Visible = " & Iif(App.Visible = TRUE, "True", "False")

      Set Sheet = WB.Sheets.Item(1)
      Sheet.Name = "Data"


      App.Visible = True

      
    Running this code snippet by Windows Script Host (cscript.exe) returns either:

        App.Visible = False
        Workbook added.
        App.Visible = False

    or:

        App.Visible = False
        Workbook added.
        App.Visible = True

    The only code executed between the "App.Visible" messages is:

      Set WB = App.Workbooks.Add

    This leads me to conclude the call to the method Add on the Workbooks collection makes the Excel application object visible. I cannot figure out why the method Add makes Excel visible on some computers and does not on others.

    I have tested on several Windows 7 with Office 365 or MS Office 2013 with mixed results. The fact the results vary makes me think it is either Excel or Windows settings which affect this functionality.

    Would you know the settings under which the Application.Workbooks.Add method changes the Application.Visible property to True?

    Thank you,
    Radovan

    • Edited by Radek7 Friday, July 7, 2017 12:12 PM
    Friday, July 7, 2017 12:10 PM

All replies

  • Hi Radek7,

    I try to test the code on my side with Office 365/ Office 2016 , OS : Windows 10.

    when I run the script I get result below.

    App.Visible = False
    Workbook added.
    App.Visible = False

    which is expected result as per code.

    after that I try to make a test with Office 365/ Office 2013 , OS : Windows 10.

    I got the same result.

    after that I try to make a test with Office 365/ Office 2010 , OS : Windows 10.

    again , I got the same result.

    I suggest you to try to make a test with Windows 10 machine and check the result.

    it is possible that OS played a role here and because of that you got the different result.

    Regards

    Deepak


    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.

    Monday, July 10, 2017 6:22 AM
    Moderator
  • Hello Deepak,
     thank you for your reply. 

     I ran the script on Windows 10 ([Version 10.0.14393]) and I am getting:

     C:\Temp>cscript .\mine_Excel.vbs
        Microsoft (R) Windows Script Host Version 5.812
        Copyright (C) Microsoft Corporation. All rights reserved.

        App.Visible = False
        Workbook added.
        App.Visible = False

    I know of 4 Windows 7 computers, however, where I can reliably reproduce the error with the output:

        App.Visible = False
        Workbook added.
        App.Visible = True

    ... and I need to run the script code on Windows 7 machines as well.

    Radovan

           
    Monday, July 10, 2017 11:42 AM
  • Hi Radek7,

    I try to make a test with Windows 7 service pack 1.

    the same result I got as before.

    you can see my testing result below.

    it is possible that because of some Windows update or some Office related updates , your result get changed and you got incorrect result.

    if you had install any updates recently then try to check those updates and if possible for you then try to remove it to solve the issue.

    otherwise , you need to uninstall updates one by one and you need to make attest after each uninstallation.

    you can try to submit your feedback to Excel user voice regarding this issue.

    Welcome to Excel’s Suggestion Box!

    Regards

    Deepak


    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, July 11, 2017 3:59 AM
    Moderator
  • Hi Radek7,

    As my experience, this code “Set WB = App.Workbooks.Add” would not make Excel application visible.

    When running this code on Windows 7 with the result “App.Visible = True”, will you see Excel application running on Desktop?

    Not sure whether it is related with lif function, I would suggest you output the Visible directly like below:

      WScript.Echo "App.Visible = " & App.Visible
    
      Set WB = App.Workbooks.Add
      WScript.Echo "Workbook added."
    
      WScript.Echo "App.Visible = " & App.Visible

    Best Regards,

    Edward


    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.

    Friday, July 14, 2017 8:24 AM
  • Hello Deepak,
     please take a look at the recording made on one of the 4 affected Windows 7 computers at https://youtu.be/aH2zWq7O0LY.

    I will try what you suggested to find out if it is because of a Windows or Office update.

    Thank you,
    Radovan

     

    Friday, July 14, 2017 2:51 PM
  • Hello Edward,
    simple answer to your question is yes. You can see for yourself at https://youtu.be/aH2zWq7O0LY.

    Radovan
    Friday, July 14, 2017 2:55 PM
  • Hi Radek7,

    for your reference, I am using version below.

    you can try to install this version to make a test.

    Regards

    Deepak


    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.

    Monday, July 17, 2017 3:34 AM
    Moderator