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

[fixer]

Moderatort Notice: see post #15
https://excelfox.com/forum/showthread.php/2422-Close-All-Open-Excel-Files-With-VBA-Open-Close-File-issue-unsolved?p=12553&viewfull=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.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=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=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=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=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=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=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=314274#p314274)
https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314229#p314229 (https://www.eileenslounge.com/viewtopic.php?f=27&t=40621&p=314229#p314229)
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA (https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA)

[DocAElstein]

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

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

[fixer]

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

[DocAElstein]

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

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

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

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

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

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

[fixer]

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

[DocAElstein]

..... 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

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

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

[DocAElstein]

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

[fixer]

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

[fixer]

chill Doc Sir sorry for the same sir

[DocAElstein]

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/showthread.php/2422-Close-the-open-excel-file?p=12537&viewfull=1#post12537


Answer is correct to question.



If macro http://www.excelfox.com/forum/showthread.php/2422-Close-the-open-excel-file?p=12537&viewfull=1#post12537 is wrong for you , then you have asked the wrong question



Try again to explain what you want

[fixer]

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

[DocAElstein]

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 placedPut this macro http://www.excelfox.com/forum/showthread.php/2422-Close-the-open-excel-file?p=12537&viewfull=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.

[fixer]

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

[DocAElstein]

...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?

[fixer]

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/threads/close-xls-file-by-a-macro.1140953/
https://www.eileenslounge.com/viewtopic.php?f=30&t=35046
https://www.excelforum.com/excel-programming-vba-macros/1322765-close-xls-file-by-a-macro.html#post5369738

[fixer]

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

[Excel Fox]

Try this



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

[fixer]

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

[Excel Fox]

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

[Excel Fox]

Assuming this is closed here (http://www.excelfox.com/forum/showthread.php/2426-VBA-To-Close-All-Other-Workbooks-Except-Active-One)

[fixer]

No doubt ur code is perfect sir