Close workbook "excel window" on open

AlaaEddin

New Member
Joined
May 2, 2018
Messages
25
Hey what I need is simple buy I am not able to perform it

I am using the following Vba code:

Code:
Private Sub Workbook_Open()
    Workbooks("close opened.xlsm").Close
End Sub

I need the window which contain the workbook to be closed totally not only close it at the same window I have made .bat file to open the excel file and I linked it with windows time schedule the idea is that I have the following files:
1- root.xlsm "Vba code gose here"
2- file1.xlsx "User1 will work on it"
3- file2.xlsx "User2 will work on it"

now the root file is a combination between file1 and file2 which is linked with some values from those 2 files depends on user1 and user2 data entry the idea is that file1 has some cells which need to get the value from file2 and the opposite is true and I done that by this idea:
file1 push data to root.xlsmfile2 push data to root.xlsmfile1 get the data from
file2 by getting them from root.xlsmfile2 get the data from file1 by getting them from root.xlsm

so I need the root.xlsm to be up to date and to do so you need to open the file to get the new values and close it and save

so what I need is:
1- perform is to open root.xlsm file after every 1 hour by linking .bat with windows time schedule which I have already done
2- close the workbook "window" after update the values without the need to close all opened excel filesI have done that using
Code:
Application.Quit

I was able to get new values and everything is find except that if there are opened excel files it force me to close them all.Any Ideas?

Note: Maybe someone will ask me why you don't link file1 with file2 directly the reason behind that is due to permissions concern.
 
Last edited:

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Hello BlueAure, Thank you for your reply,

Actually yes I tried it but the problem is that in this excel file I have cells linked with other workbook so it needs to be update I want the excel file to be opened automatically which I have done that but I need it to update the new linked values, using workbook.close isn't saving the values so I don't know if there's something more should be added beside workbook.close...
 
Upvote 0
Add the save parameter to the workbook.close like it shows in the MSDN I sent earlier. Try:

Workbooks("BOOK1.XLS").Close SaveChanges:=True
 
Upvote 0
Workbooks("BOOK1.XLS").Close SaveChanges:=True
Actually SaveChanges:=True not saving the updated links for some reason I tried it, and it wasn't saving the links...
Actually I used the following code and my problem fixed:

Code:
[COLOR=#24292E][FONT=SFMono-Regular]Private Sub Workbook_BeforeClose(Cancel As Boolean)
[/FONT][/COLOR][COLOR=#24292E][FONT=SFMono-Regular]ThisWorkbook.Save[/FONT][/COLOR]<code style="box-sizing: border-box; font-family: SFMono-Regular, Consolas, "Liberation Mono", Menlo, Courier, monospace; font-size: 11.9px; background: transparent; border-radius: 3px; margin: 0px; padding: 0px; border: 0px; word-break: normal; display: inline; line-height: inherit; overflow: visible; overflow-wrap: normal;">End Sub
Sub WorkBook_Open()
ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources, Type:=xlExcelLinks
If Application.Workbooks.Count = 1 Then 'quit Excel if only one workbook open
    Application.Quit
ElseIf Application.Workbooks.Count > 1 Then
    ActiveWorkbook.Close True
End If
Application.Quit 
</code>End Sub

I added the code in ThisWorkbook also I saved the file as xlsm "Maco Enabled" the reason of the if condition is because of the following reason:

When
Code:
ActiveWorkbook.Close True
executed when this is the only activated workbook it doesn't save the changes but if there was another workbook opened so it will close the workbook window which include the VBA code and it will keep the other which is going to save the changes.

So when there's only 1 workbook opened execute
Code:
Application.Quit
which is going to save the updated links and when there's more than 1 workbook opened execute
Code:
ActiveWorkbook.Close True
which is going to close the window itself not the entire excel.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,495
Messages
6,113,992
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top