[Solved] Book 1

  • Hi everyone,

    I have come across a trait of Excel which I'm not sure to get around, but I really need to.

    At the moment I have an Excel add-in that opens up an Admin-type file.
    If the user runs a macro that needs to open up this Admin-type file (file containing user info etc etc) then the
    admin file, the data retrieved and then closed (all pretty straight forward)-however (and here is the problem as I see it) the Book1.xls workbook that existed before the Admin-type file was opened is now GONE...

    I have found that this occurs in normal circumstances (without a macro) in that if a workbook is immediately opened when Excel is opened then the Book1.xls workbook no longer appears (it closes).

    I tried this by opening Excel (without my add-in) and then just opening a file and found that Book1.xls no longer appears.

    Why is this??? :o

    It's just that I would like this book to still be there when my add-in opens (and then closes) the admin file.

    Any thoughts, suggestions or questions would be very much appreciated.

    Thanks in advance...

    DW (the_sleeper)

  • Hi Sleeper,

    This is one of Excel's little quirks and I hope my explanation is easily understood.

    When you open up Excel, it automatically creates a new, blank workbook called Book1.xls. If you then open a different workbook that you had previously saved, then Book1.xls disappears as it was not saved nor did it contain any data.

    Now, if you were to open Excel, and then using FILE and NEW, you were to create 2 new workbooks, when you click on the Windows menu, you would see Book1, Book2, and Book3.

    Based on the first part, if you were to open Excel, type your name into any cell, and then open another previously saved workbook, then you would still see Book1 in your list because it now contains data.

    Therefore, to keep things simple, before running your admin add-in, either enter some data into Book1 or save Book1. Then, when the admin add-in shuts down, Book1 should still be there and intact.

    Hope this helps.

  • You could also just add a line at the end of your macro that checks to see if any workbooks are open. If there are none, then create one. The line would read
    If Workbooks.Count = 0 Then Workbooks.Add
    The name might not be Book1, but is that important?

Participate now!

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