Close worbook vba not working. (1 Viewer)

[MsAccessNL]

Sub Closethis()
Thisworkbook.close
End sub

Doesn’t work if this sub is called from another function or from an event. Only if you run it direct. Chat said to run the code on time, but also nothing.

I have code to check for a value otherwise it will close the workbook. Any ideas to get this working?

 

[arnelgp]

Try Renaming your Closethis() sub to anything other than that name, ie:

Sub subCloseThis()
Thisworkbook.Close
End Sub

 

[Isaac]

Can you give an example of the calling code - the full calling code?
You might consider using true or false for saving

 

[skdn71]

Sub CloseThis()
Application.OnTime Now + TimeValue("00:00:01"), "ActualClose"
End Sub

Sub ActualClose()
ThisWorkbook.Close SaveChanges:=False
End Sub

 

[The_Doc_Man]

Just remember that "ThisWorkBook" is like the Access keyword "Me" in that it is dependent on the context of where it is used. For instance, you cannot use Me.Close from a utility routine in a general module. Further, "Me" can have different meanings at the same time depending on context (if you have two forms open at once). If you have the subroutine in a place different than the place where the workbook was opened, ThisWorkBook MIGHT be out-of-scope. That's why my special object support subs always take an argument as the name of the object or a ByRef type reference to the object. You can't rely on the special-case shortcuts if you have multiple potential scopes active at the same time.

 

[Isaac]

Just remember that "ThisWorkBook" is like the Access keyword "Me" in that it is dependent on the context of where it is used. For instance, you cannot use Me.Close from a utility routine in a general module. Further, "Me" can have different meanings at the same time depending on context (if you have two forms open at once). If you have the subroutine in a place different than the place where the workbook was opened, ThisWorkBook MIGHT be out-of-scope. That's why my special object support subs always take an argument as the name of the object or a ByRef type reference to the object. You can't rely on the special-case shortcuts if you have multiple potential scopes active at the same time.

agreed. this is why i asked for the calling code in case he's running automation from another program

 

[MsAccessNL]

Sub CloseAndQuitExcel()
    Application.OnTime DateAdd("s", 1, Now), "QuitExcelNow"
   
End Sub

Sub QuitExcelNow()
    Debug.Print "QuitExcelNow"
    'Debug.Print Application.Workbooks.Count
    ThisWorkbook.Saved = True
    'ActiveWorkbook.Close
    ThisWorkbook.Close SaveChanges:=False
    Application.Quit
End Sub

if i make a button close and call a close function directly, it wil work, but when i call this function from other code of somekind of event, it will not work. For instance on the thisworkbook.open event :

Private Sub Workbook_Open()
'Call HiddenMsg
If MsgBox("Close excel now", vbYesNo) = vbYes Then QuitExcelNow
End Sub

Excel wil stay open

 

[The_Doc_Man]

I might consider tossing a Debug.Print into that QuitExcelNow routine to show you ThisWorkbook.Worksheets.Count, to verify that you can in fact SEE the workbook before you try to close it. That would immediately resolve the visibility issue.

 

[Isaac]

please post ALL the code including the calling code, and tell us what application is running the calling code.

This is very germaine to the conversation, because if you have code from , say, Word or Outlook or Access that goes:

set myapp = createobject()
set wb=myapp.workbooks.open(path)
ThisWorkbook.Close

...Well then it's clearer why it's not working!
in 15 years of Excel vba, I've never known Workbook.Close to "not work". There's something else about your context that you must identify & share.

 

[Edgar_]

Not sure Application.Quit will run after you've chosen to close the workbook, so simply quit the application, I think you've already chosen to ignore changes by using the Saved=True property.

Sub QuitExcelNow()
    If Application.Workbooks.Count > 1 Then
        ThisWorkbook.Saved = True
        ThisWorkbook.Close
    Else
        Application.Quit
    End If
End Sub

The code above checks if the Excel instance is holding more than one workbook open, if so, it will just close the current workbook, otherwise, it will close the whole application to avoid the behavior you mention about Excel staying open.

Note: If you wish to close a workbook from another workbook, you will have to extract the instances from the ROT using the windows API.

 

[Isaac]

Not sure Application.Quit will run after you've chosen to close the workbook

I don't think it will, unless it's being controlled from another program using Automation, which is what I keep trying to get the OP to tell us.