-
Close All Open Excel Files With VBA Open Close File issue unsolved
-
1 Attachment(s)
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
