Wokbooks.Open() is closing Book1

[DisgruntledVBANovice]

Hi all,

I am very much a novice VBA programmer, and I am having a hard time figuring out why Excel is doing what it is doing.

I have some code that is part of a user form. When the form opens, I open another Excel file that has the default settings for the form stored in it. After grabbing the values out of this file that I need, I then close it. What I am finding though, is that if this form is opened from a blank workbook (such as opening Excel via the start menu), then when I open the settings form, Excel is closing Book1. I have tried setting the originally open workbook to a variable (thinking that if the workbook was in a variable, Excel would not close it automatically).

If I do the same thing, but either open an existing workbook or create a blank workbook (essentially Book2), then everything works as expected (my settings file opens and closes and the original workbook is left open).

Is there something I am doing wrong or is there a way to keep Excel from closing the default Book1 when I open a different file?

Thanks!

 

[steve the fish]

Excel wont close a file unless something tells it to close the file. Opening a workbook doesnt close another unless there is code in there telling it to do so.

 

[DisgruntledVBANovice]

I'd like to believe that, but I don't have any calls to close the original workbook.

I was thinking this was some in-built behavior similar to how Excel responds normally when doing the following:

1. Open Excel by launching the application (NOT opening a file)
2. Book1 opens by default
3. File -> Open -> Select a file, say MyFile.xlsx
4. The result is that now only MyFile.xlsx is open (Book1 was closed automatically, even though I never closed it)

I was guessing the VBA was doing something similar and I wanted to stop it from doing so. I should also mention that all of my code is in an Add-In (not the individual file).

 

[DisgruntledVBANovice]

Without overwhelming you with the code, here is a small snippet that demonstrates that Excel is closing Book1 without me asking:

Debug.Print "Workbooks open (before opening settings): " & Workbooks.count
Set settingsWb = Workbooks.Open(Form_Settings_Path)
Debug.Print "Workbooks open (after opening settings): " & Workbooks.count

I get the following output:

Workbooks open (before opening settings): 1
Workbooks open (after opening settings): 1

I expected there to be 2 workbooks open after the call to Workbooks.Open().

 

[Joe4]

What I am finding though, is that if this form is opened from a blank workbook (such as opening Excel via the start menu), then when I open the settings form, Excel is closing Book1. I have tried setting the originally open workbook to a variable (thinking that if the workbook was in a variable, Excel would not close it automatically).

Trying saving Book1 first, before doing your other steps with your form. If the form is saved first, then it shouldn't be automatically closed when you open another file.

 

[DisgruntledVBANovice]

I am sure that would work, but I have no idea if the user actually wants to save Book1. I did figure out a way around this though. The following code solved my issue:

Debug.Print "Workbooks open (before opening settings): " & Workbooks.count
If ActiveWorkbook.Name = "Book1" Then
    ActiveWorkbook.Saved = False
End If
Set settingsWb = Workbooks.Open(Form_Settings_Path)
Debug.Print "Workbooks open (after opening settings): " & Workbooks.count