Close Workbooks (1 Viewer)

[wsaccess]

Hi,

I need to close 3 workbooks from the current one.
So in the main workbook that I have open, I inserted a button, and added the following code to it:

Sub btnClose_Books()

Dim objXLBook As Workbook

For Each objXLBook In Application.Workbooks
If objXLBook.Name = "Book1" Then
objXLBook.Close (False)
ElseIf objXLBook.Name = "Book2" Then
objXLBook.Close (False)
ElseIf objXLBook.Name = "Book3" Then
objXLBook.Close (False)
End If

Next

End Sub

But after the current workbook, it doesn't even go to Next. It comes out of the Sub.

Any ideas please?

Thanks

 

[Rx_]

The answer is "it depends"
1. If one Excel Workbook has buttons that open up the other 3 workbooks, then automation code could be used that opens the other workbooks and keeps a pointer (an object variable) to each open workbook - this could be used to remotely manage data or other objects on the remote workbook including closing them.
2. If these workbooks are opened independent of the Excel workbook with the buttons to close the 3 remote workbooks, then these workbooks would need to have specific names and locations. Perhaps the code would go along:

Function BookOpen(wbName As String) As Boolean
On Error Resume Next ' it would be better to add error trap code
BookOpen = Len(Workbooks(wbName).Name)
End Function

then use:
If BookOpen(WbkNameOpen) Then Workbooks(WbkNameOpen).Close

Allow me to go one-step-beyond (the name of an old 1960's syfi)
Function GetThisWB() As String
GetThisWB = ThisWorkbook.Path & "\" & ThisWorkbook.Name
End Function
ThisWorkbook property is similar to the ActiveWorkbook property, but whereas ActiveWorkbook evaluates the workbook with the focus, ThisWorkbook refers to the workbook that's running the current code. This added flexibility is great because the active workbook isn't always the workbook that's running code.

The Workbooks collection is a container of all the open Workbook objects. It is a way to refer to open workbooks.

Private Sub UserForm_Activate() 
 'Populate list box with names of open workbooks
 Dim wb As Workbook
For Each wb In Workbooks
   ListBox1.AddItem wb.Name
  ' here, if the name is one to close - add the code here
Next 
End Sub

Ok, depending on what is needed, this sub should provide the 1st step
Then, use properties described above to add a If Then to see if any of the workbooks have the 3 names that need to be closed.

Public Sub CloseAllOpenWorkbooksInWorkbookCollection()
Dim wb As Workbook
For Each wb In Workbooks ' workbooks collection
    ' add code here for the workbook name if looking for specific workbook names - if it isn't the name, then skip the next line
    wb.Close False ' Or use True if you want to prompt user to save changes
Next wb
End Sub

 

[wsaccess]

Hi Rx,

Thanks for your reply. However, I am still unable to do it.
I guess my original post was not clear enough.
I open the current workbook. Then I open an internet explorer based application. That application opens 3 spreadsheets one by one, named Book1, Book2 & Book2. Once I copy the data from those 3 spreadsheets, I click the button on my current workbook to close them.
Then the application open 3 new spreadsheets for a different region, again named Book1, Book2 & Book3. I copy the data, and delete them through the button and so on.
This process is repeated many times.
So, the 3 workbooks that I need to close are not initially open when I open my current workbook.
They open later and need to be closed before I open the next 3.
Is that why the "Application.Workbooks" does not see them at all?

 

[Rx_]

current workbook (Lets call it MAIN) opens 3 workbooks - does some process with internet, then closes 3 workbooks.
Main then opens 3 more for processing, and repeats the cycle again.

Is that correct? Update this as needed.
I will try to send code later today.

Basically, MAIN needs to create a New Workbook object and keep the pointer so it can close it.

 

[wsaccess]

Not quite.
1. I open MAIN workbook.
2. I open Internet based application.
3. Internet application opens 3 workbooks (Book1,Book2,Book3)
4. I process data from the 3 new workbooks to the MAIN
5. I click the Button on MAIN to close the 3 workbooks. (This is what I am trying to do)

Steps 3-5 is repeated many times, as explained below:

5a. Internet application opens 3 new Workbooks
6a. I process data from these 3 again into the MAIN workbook.
7a. I click the Button on MAIN to close the 3 workbooks. (This is what I am trying to do)

 

[Rx_]

Sorry to be too busy yesterday to get back to you. This is good information.
Need More: When the Internet application opens 3 new workbooks:
1. I assume the workbooks are opened on your workstation (not on a remote session).
2. Do these workbooks have a naming convention? e.g [Regionx]+[todays date]+Sales1.xls
3 At this point, does the Internet application invoke the SaveAs in Excel? e.g. when you manually close the Excel does it ask you if you want to SaveAs or is the SaveAs already done?
4 What path does the Internet Application open the Excel? e.g. is it just your default location or did the Internet Application ask in advance what directory would you like to have the Excel saved into? When you use the SaveAs on an Excel workbook, the directory path should be evident.

The steps will be this, if we know the relative name and location of an Excel Workbook, use a GetWorkbook function to take automated control.
For quality assurance, I typically use remote automation to investigate several key cells to validate it is in fact the correct workbook.
Lets suppose cell A5 had "Region 666" and cell B7 had the Report's date. This information can be used in a SaveAs to rename the workbook.

Lucky for you, I have already posted some code examples on this site that should come in handy.

 

[wsaccess]

Hi Rx,

Thanks for your reply inspite of your busy schedule.

Here are the answers to your questions:

1. There is no naming convention.
When the internet application open the workbooks, they get opened as
"Book1","Book2", "Book3".
2. It neither prompts for any location nor ask to save before it opens. They just open.
3. When I try to close it, it asks "Do you want to save?" and I say "No". All that I need
from these 3 workbooks is the data which I copy to my MAIN workbook.
4. I suppose, I cant use the GetWorkBook function, since there is no location for
them?

Thanks

 

[arnelgp]

if those workbooks are already open, you just fetch the instance of excel that is current running, instead of creating new instance.

dim xlApp As Excel.Application
dim wb As Excel.WorkBook
'don't do this
'Set xlApp = New Excel.Application
'Set xlApp = CreateObject("Excel.Application")
'instead grab the current instance of excel
Set xlApp = GetObject(,"Excel.Application")
For each wb In xlApp
If wb.Name Like "Book*" Then wb.Close False
next

 

[Rx_]

Thanks for outlining the Excel creation, I think arnelgp suggestion will work.
Once you were able to explain the process and result, a solution was possible.

Pleas let us know either way what your solution was!
B.T.W. it wouldn't be difficult to add a Save As and include something like your Region and a time date stamp to archive this into a directory.

If you post a working solution, please go to the top of your first post and mark this as SOLVED. This will probably help many others.

 

[wsaccess]

How to I set wb in this case?

if those workbooks are already open, you just fetch the instance of excel that is current running, instead of creating new instance.

dim xlApp As Excel.Application
dim wb As Excel.WorkBook
'don't do this
'Set xlApp = New Excel.Application
'Set xlApp = CreateObject("Excel.Application")
'instead grab the current instance of excel
Set xlApp = GetObject(,"Excel.Application")
For each wb In xlApp
If wb.Name Like "Book*" Then wb.Close False
next

 

[Rx_]

for the Attached file, change the .PDF extension to a macro enabled .xlsm
Open the dashboard.xlsm
Make sure the Design mode is Off - Click on the simulate Web opening Excel button three times.

The Workbook1, 2, 3 will be on top of each other, cascade them.
you can open up the code window and put a debug pause on the loop for cmdCloseOneSpecificBook_click

this will help explain the Workbooks collection.
When you are ready to close all, just edit the code to get rid of Range("c3") and move the "*" back into the Like criteria.

The attached example: The Master Excel has a button that will create a new Excel Workbook (e.g. Workbook1, Workbook2, ...) to simulate the web application opening 3 workbooks (unsaved).

The Spinner control changes a Buddy Cell to a number, probably 1, 2 or 3.
The code behind the 2nd button uses that number - looks for all open workbooks - if the name and number match (e.g. Workbook2) it will close the workbook without asking Are You Sure.

Private Sub cmdCloseOneSpecificBook_Click()
Dim xlApp As Excel.Application
Dim wb As Excel.Workbook
Dim NumberInC3 As Integer
NumberInC3 = Me.Range("C3")

'instead grab the current instance of excel
Set xlApp = GetObject(, "Excel.Application")
For Each wb In xlApp.Workbooks
    Debug.Print "the workbook name in the collection is : " & xlApp.Workbooks(2).Name
    If wb.Name Like "Book" & NumberInC3 & "*" Then wb.Close False
Next
End Sub

 

[wsaccess]

Thanks Rx and arnelgp for your code and help.
Rx, the code in the file you sent works perfectly fine when I run the same file.
I copied the code in my MAIN workbook, used your file to open 3 new workbooks and the code worked fine.
However, when my internet application opened the 3 new workbooks, the same code didn't work!
For some really strange reason, the new workbooks opened by the internet application is not at all recognised by "xlapp.workbooks"
I guess I will have to close them manually for now.

 

[Rx_]

When you have Excel (your primary WB) open - does the Internet open 3 workbooks inside your existing Excel or each in their own Application.
Press Control+Alt+Delete how many instance of Excel are running?

My bad and I should have known better. Since my code spawns a new workbook in the collection of the existing Excel.

I posted some code about two years ago that finds a copy of Notepad by name.

https://msdn.microsoft.com/en-us/library/office/gg251785.aspx
On the Excel opened by the Internet - Do a Save As - and note the directory path.
Now, use the arguments shown in the link above to get that specific object.