VBA Close Window

[MDuff]

Hi all I have been Playing with this simple code And I had a Question Every time the code runs with the part in bold it asks me if I want to save the Changes to the workbook (this work book has links) Is there I way I can have excel say yes or know to this with out having to click on it
Any help is very appreciated

private Sub Workbook_Open()
ActiveSheet.Unprotect Password:="12345"

Workbooks.Open Filename:= _
"\\Crsjofil001\Accounts\GEA\Warranty Management\HR Documents\Performance Appraisals\Performance Appraisals - 2nd Semester 2003\GEA Performance Appraisals Warranty Management II 2003 xls.xls"

ActiveWindow.Close
Windows("PA Summary WM.xls").Activate


ActiveSheet.Protect Password:="12345", DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

thanks Mike

 

[misiorto]

I would like to be able to close the files after code execution because usually I have around 200 or more files to open and run the cashflow macro, so it might be difficult for Excel to work with so many files open at one time.

 

[John Davis]

The only other thing I could suggest is to create another macros (perhaps a copy of Cash Flow) without the Activeworkbook.Close statement. And use that instead. That way you'll still have the option to run one which will work with Sub odpal_dir() and one which can operate separately. However, someone on the board may have better ideas.

 

[misiorto]

Thank You John.

 

[RoryA]

You could change the code to:

Sub cashflow(Optional blnClose as Boolean = True)
'Code (irrelevant)
If blnClose then ActiveWorkbook.Close SaveChanges:=True 'or ActiveWindows.Close 
End Sub

then use:

Application.Run "'" & nazwa_pliku & "'!CashFlow", False

in the other code.

 

[misiorto]

Thank You, rorya, it actually solves my problem. Just to make sure: to run a macro with a parameter I need to write simple macro:
Sub start()
Call cashflow()
End sub
Cause I can see that at least Excel 2003, after I've added the optional parameter to Sub cashflow, no longer considers "cashflow" to be a proper macro.

 

[RoryA]

Well, you can also type the name of the macro into the macro dialog and run it that way, but it will not appear in the list, no.

 

[misiorto]

Thank you once again for your help.

 

[HARSHUL]

Application.DisplayAlerts = False

It will work for any alerts.

 

[sobieskitech]

I'm having trouble getting Application.DisplayAlerts = False to activate in Excel 2010.
Sub Open_Compare_v2_xlsx_copy_TU_paste_to_CHECK()

'THIS OPENS COMPARE WORKBOOK
Workbooks.Open "\\...Compare v.2.xlsx"

'THIS DISABLES ANY AUTO FILTERS THAT MAY BE ACTIVE AT LAST SAVE
Set Lst = ActiveSheet.ListObjects(1)
If Lst.AutoFilter.FilterMode Then
Lst.AutoFilter.ShowAllData
End If

'THIS GETS DATA AND PASTES IT AS VALUE ONLY
Columns("T:U").Select
Selection.Copy
Windows("GLI CHECK.xlsm").Activate
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

'THIS CLOSES FRED'S VLT/iGEM COMPARE WORKBOOK
Windows("VLT_iGEM Compare v.2.xlsx").Activate
ActiveWindow.Close False
Application.DisplayAlerts = False <==this line of code isn't working for me

End Sub

 

[RoryA]

What is that line supposed to achieve given that it is at the end of the routine, and gets reset as soon as the code finishes?