Close All Open Excel Files With VBA Open Close File issue unsolved

Moderatort Notice: see post #15
https://excelfox.com/forum/showthrea...ll=1#post12553
for what this might all possibly be about...

---

vba will be placed in a sample1.xlsm
and there are excel files which are opened
i have to close all that opened file by vba
(any type of excel file it can be,i request sir only sample1.xlsm file will be opened and any other file opened then it should be close )
i need vba macro of the same sir so plz help me in solving this problem sir

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314837#p314837
https://www.eileenslounge.com/viewtopic.php?f=21&t=40701&p=314836#p314836
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314621#p314621
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314619#p314619
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314600#p314600
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314599#p314599
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314274#p314274
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314229#p314229
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA

The workbooks collections object can be used to reference all open workbooks
Using this object to close all open workbooks is very easy.

Code:

---

Sub ClsOpnWbs() ' http://www.excelfox.com/forum/showthread.php/2422-Close-the-open-excel-file
Dim AnyWb As Workbook
    For Each AnyWb In Workbooks
        If AnyWb.Name <> ThisWorkbook.Name Then
        'AnyWb.Close Savechanges = True '        'True:- To save any changes
        AnyWb.Close Savechanges = False '      '  False:- File is not saved. No changes will be saved.
        Else ' case AnyWb is this workbook
        ' Do nothing on this Loop
        End If
    Next AnyWb
End Sub

---

Code:

---

Sub STEP1()
Dim AnyWb As Workbook
    For Each AnyWb In Workbooks
        If AnyWb.Name <> Book1.xlsm Then Nothing
        Else
        AnyWb.Close Savechanges = True
        End If
    Next AnyWb
End Sub

---

code has errors 100% , i am unable to make the code for the same Doc Sir plz help

I see 2 problems

Problem 1 – text in VBA code
In VBA we must tell the coding that we want to give it a text
We do it like this ....... " Here is text "

Book1.xlsm is the text name , so we tell VBA that it is text like this
“Book1.xlsm”


Problem 2 Nothing

Nothing is special in VBA . It makes an object empty
Here are 3 macros to help explain what Nothing is...

This macro will work

Code:

---

Sub TestNothing1()
Dim Wb As Workbook
 Set Wb = ThisWorkbook
MsgBox prompt:="My text name for Wb is  " & Wb.Name
End Sub

---

This next code will error: It will not work. It will not work because you make Wb empty with Nothing

Code:

---

Sub TestNothing2()
Dim Wb As Workbook
 Set Wb = ThisWorkbook
 Set Wb = Nothing
MsgBox prompt:="My text name for Wb is  " & Wb.Name
End Sub

---

This next macro will work

Code:

---

Sub TestNothing3()
Dim Wb As Workbook
 Set Wb = ThisWorkbook
 Set Wb = Nothing
 Set Wb = ThisWorkbook
 MsgBox prompt:="My text name for Wb is  " & Wb.Name
End Sub

---

If you want nothing to be done, then put no coding - no coding= nothing will be done

---

So try

Code:

---

Sub STEP1()
Dim AnyWb As Workbook
    For Each AnyWb In Workbooks
        If AnyWb.Name <> "Book1.xlsm" Then
        '  put nothing here . So no code is here  so nothing is done
        Else
        AnyWb.Close Savechanges = True
        End If
    Next AnyWb
End Sub

---

Or just like this - no coding= nothing will be done

Code:

---

Sub STEP1()
Dim AnyWb As Workbook
    For Each AnyWb In Workbooks
        If AnyWb.Name <> "Book1.xlsm" Then
       
        Else
        AnyWb.Close Savechanges = True
        End If
    Next AnyWb
End Sub

---

This code is doing opposite Doc Sir I dont want "Book1.xlsm should be closed rest all files should be closed there can be 2 or 3 or file can be opened
plz recheckk Doc Sir

Quote:

---

Originally Posted by fixer

..... rest all files should be closed there can be 2 or 3 or file can be opened

---

This is contradiction?????

A file can be closed
A file van be left opened
A file cannot be open and closed – That is nonsense.


Try to explain again please

Or

Try this

Code:

---

Sub STEP_CloseOnly_Book1_xlsm()
Dim AnyWb As Workbook
    For Each AnyWb In Workbooks
        If AnyWb.Name = "Book1.xlsm" Then
        AnyWb.Close Savechanges = True
        Else
       
        End If
    Next AnyWb
End Sub

---

Or

Code:

---

Sub Close_Book1_xlsm()
 Workbooks("Book1.xlsm").Close Savechanges = True
End Sub

---

I think biggest problem is that you have difficulty to explain in English

Try again to explain

Explain again. Give examples . Give more detailed explanations

vba code macro will be placed in a sample1.xlsm
and there are excel files which are opened (it can be 2 or 3 or more also files are opened along with sample1.xlsm)
i have to close all that opened file by vba and keep only sample1.xlsm opened bcoz it contains vba code
(any type of excel file it can be,i request sir only sample1.xlsm file will be opened and any other file opened then it should be close )
i need vba macro of the same sir so plz help me in solving this problem sir

chill Doc Sir sorry for the same sir

Quote:

---

Originally Posted by fixer

chill Doc Sir sorry for the same sir

---

.. Is no problem :) Chill is good and healthy. - I know it is difficult for you to communicate….


But the same problem again . I think you are unable to explain

This is question :
vba code macro will be placed in a sample1.xlsm
and there are excel files which are opened (it can be 2 or 3 or more also files are opened along with sample1.xlsm)
i have to close all that opened file by vba and keep only sample1.xlsm opened bcoz it contains vba code
(any type of excel file it can be,i request sir only sample1.xlsm file will be opened and any other file opened then it should be close )

This is correct answer to question: http://www.excelfox.com/forum/showth...ll=1#post12537


Answer is correct to question.



If macro http://www.excelfox.com/forum/showth...ll=1#post12537 is wrong for you , then you have asked the wrong question



Try again to explain what you want

i need a macro
i will place the macro in any file and i will run that macro
and when i run that macro all files should be closed if there will be any opened file except the file in which macro is placed

Quote:

---

Originally Posted by fixer

i will place macro in any file and i will run that macro
and when i run that macro all files should be closed if there will be any opened file except the file in which macro is placed

---

Put this macro http://www.excelfox.com/forum/showth...ll=1#post12537 in any file.
Run the macro
All Excel files will be closed , except the file in which you have put the macro: The file with the macro will stay open. All other Excel files will be closed.

Code:

---

Sub STEP1()
Dim AnyWb As Workbook
    For Each AnyWb In Workbooks
        If AnyWb.Name <> "Book1.xlsm" Then
        AnyWb.Close Savechanges = False
        Else
        End If
    Next AnyWb
End Sub

---

problem solved code works perfect no doubt in it
But i have one question i just downloaded the file and by default it is in a opened state & after that i open vba macro placed file and when i run the code its not working
but i download the file if file is opened and i close that file and again i open that file and then i open the vba placed file and ran the macro then code works
Any idea about this problem
is this problem solvable bcoz i already have the code to close the file but u r a profession vba programmer so i thought if u provide me the code then problem can be solved

Quote:

---

Originally Posted by fixer

...But i have one question i just downloaded the file and by default it is in a opened state & after that i open vba macro placed file and when i run the code its not working
but i download the file if file is opened and i close that file and again i open that file and then i open the vba placed file and ran the macro then code works .....

---

Sorry, I do not understand what you are saying. I do not think anyone can understand what you are saying.
I think maybe you need to find somebody that can speak your language, and also the English language.
I think you need to find a translator to help you ask your questions.

---

I try now to geuss your question...
Is this your question?


Question Suggestion 1

I may have some Excel files open. ( We will call them the “already_opened” Files )
These I want to stay open.

I may then open some other Excel files. They may be any type of excel file . I want a macro that will close all these files that were opened. But I want the already_opened files to stay opened.

_._______________________________

I can answer Question Suggestion 1 if you wish?

Leave this topic it is solved No doubt Thnx Doc Sir for the Support and guidance i will manage that problem

---

Moderator notice
Problem is possibly related to this cross post
https://chandoo.org/forum/threads/vba-macro.44302/
An issue with possibly a file not in the current instance of Excel .....

and another go in July, 2020
https://www.mrexcel.com/board/thread...macro.1140953/
https://www.eileenslounge.com/viewto...p?f=30&t=35046
https://www.excelforum.com/excel-pro...ml#post5369738

vba is placed in a seperate file 1.xlsm
now what i need
there can be 2-3 or it can be 4 files opened along with this 1.xlsm
i will be in 1.xlsm file & i will ran the macro
now what i need is when i ran the macro it should go to next open file and use ALT+ F4(this is shorcut key to close the excel file) and that will be close and then again go to next open file and use ALT+ F4 and again if there is a file then do the same do till all files are closed except 1.xlsm
and i wanted to inform u sir by doing ALT+ F4 only my file is closing and i want to close all the file by this way only sir
So plz have a look into this problem and help me in solving this problem
we have to solve this problem only by vba only sir so plz help sir & mam

Try this

Code:

---

Sub CloseAllOtherWorkbooks()
'
' CloseAllOtherWorkbooks Macro
' Keep this workbook open and close all other workbooks in this instance
'
' Keyboard Shortcut: Ctrl+q
'
    Dim wbk As Workbook
    Dim strBookNames() As String
    Dim lngBooksCount As Long
   
    For Each wbk In Application.Workbooks
        If wbk.Name <> ThisWorkbook.Name Then
            lngBooksCount = lngBooksCount + 1
            ReDim Preserve strBookNames(1 To lngBooksCount)
            strBookNames(lngBooksCount) = wbk.Name
        End If
    Next wbk
    For lngBooksCount = 1 To lngBooksCount
        'Change to True if the files have to be saved before closing
        Workbooks(strBookNames(lngBooksCount)).Close False
    Next lngBooksCount
    Set wbk = Nothing
    Erase strBookNames
   
End Sub

---

Thnx ExcelFox for helping me in solving this problem
Actually i downloaded the file and i open the macro file and i ran it but its not working i tried many code to close the file but its not closing
when i close the file and then i open then it is closing the code is perfect no doubt
i learned that once i download the file and it opens by default then macro will not work until its close and reopen again
i searched on google there was alt+F4 which is closing that file but i think if we use that also in the vba code then it will also not work i will manage it

Not sure I understand that. So you're saying the code I sent works, right?

Assuming this is closed here

No doubt ur code is perfect sir