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:

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

AlaaEddin

New Member
Joined
May 2, 2018
Messages
25
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...
 

BlueAure

New Member
Joined
May 21, 2019
Messages
46
Add the save parameter to the workbook.close like it shows in the MSDN I sent earlier. Try:

Workbooks("BOOK1.XLS").Close SaveChanges:=True
 

AlaaEddin

New Member
Joined
May 2, 2018
Messages
25
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:

Watch MrExcel Video

Forum statistics

Threads
1,096,312
Messages
5,449,617
Members
405,573
Latest member
Masimo85

This Week's Hot Topics

Top