Results 1 to 5 of 5

Thread: Close workbook "excel window" on open

  1. #1
    New Member
    Join Date
    May 2018
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Lightbulb Close workbook "excel window" on open

    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 by AlaaEddin; May 21st, 2019 at 04:12 PM.

  2. #2
    New Member
    Join Date
    May 2019
    Location
    Indiana, USA
    Posts
    46
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Close workbook "excel window" on open

    Hi AlaaEddin,

    Have you tried using

    Code:
    workbook.close
    instead of application.quit? Aplication.quit kills all of the open Excel windows by ending the Excel process entirely. Workbook.Close closes a specific workbook. I have included the MSDN article for it below.

    https://docs.microsoft.com/en-us/off...workbook.close

  3. #3
    New Member
    Join Date
    May 2018
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Close workbook "excel window" on open

    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...

  4. #4
    New Member
    Join Date
    May 2019
    Location
    Indiana, USA
    Posts
    46
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Close workbook "excel window" on open

    Add the save parameter to the workbook.close like it shows in the MSDN I sent earlier. Try:

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

  5. #5
    New Member
    Join Date
    May 2018
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up Re: Close workbook "excel window" on open

    Quote Originally Posted by BlueAure View Post
    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:
    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ThisWorkbook.SaveEnd 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 
    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 by AlaaEddin; May 24th, 2019 at 07:20 AM.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •