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

**fixer** · 03-03-2020, 09:52 AM

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

**DocAElstein** · 03-03-2020, 02:09 PM

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

**fixer** · 03-03-2020, 03:14 PM

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

**DocAElstein** · 03-03-2020, 03:56 PM

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

**fixer** · 03-03-2020, 04:08 PM

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** · 03-03-2020, 04:17 PM

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

**DocAElstein** · 03-03-2020, 04:28 PM

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** · 03-03-2020, 04:29 PM

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** · 03-03-2020, 04:30 PM

chill Doc Sir sorry for the same sir

**DocAElstein** · 03-03-2020, 04:38 PM

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