Create invisible workbook

  • I have a code that builds about 200 workbooks based on a list that is on the parent Workbook. Because this takes about 30 minutes, I want the user to be able to continue to do work (with all code activity done in the background).
    Making the Parent workbook invisible was easy, but I am unable to find how to create new workbooks without them popping up as visible. Here is the current code snippet:


    Code
    NewBook as Object
    Set NewBook = Workbooks.Add


    I do not seem to have the option to tell it to create the workbook as Visible = False. I can hide it after its created but that does not accomplish the goal.

  • Hi,


    Firstly it's better coding practice to dimension a workbook object variable specifically as Workbook rather than as Object.


    Perhaps try...


    Code
    Dim objNewBook As Workbook
    Set objNewBook = Workbooks.Add
    ActiveWindow.Visible = False


    Alternatively if you are just creating the workbooks use...


    Code
    objWorkbook.Close


    Regards,


    Tom Rowe

  • Code
    ActiveWindow.Visible = False


    This successfully hides the workbook... but it does it after the workbook has been created, not at the same time. The goal was for the workbook to be created in the background without ever making an appearance.


    Generally when the workbook makes an appearance, it creates an opportunity for the user to cause an error (for instance, if the user is typing in a word document, and the excel workbook screen pops up, this sometimes causes the words to be typed onto the spreadsheet instead of the word document).

  • Hi,


    I don't think that the user would probably have enough time to erroneously type something into the spreadsheet...


    Not sure if you can add a workbook without it opening. Maybe some of the other Ozgrid experts may know if this is possible.


    Another approach would be to just to open the workbook once with...


    Code
    Set objNewBook = Workbooks.Add


    Leave this workbook open. Then create the rest of the workbooks. with the SaveAs method...


    Code
    objWorkbook.SaveAs


    Regards,


    Tom Rowe...

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!