VBA code to close Excel Workbook

[Cime14]

Hi,

I am using a Task Scheduler to open Excel Workbook at desired date/time. When open I use option Private Sub workbook_open() to run my macros. When all is done workbook should close (can be w/o saving) and be ready for new scheduled time execution. Problem is I cannot make it close... Below I am attaching code. Can anyone help?

Private Sub workbook_open()

Application.DisplayAlerts = False

Dim SapGui
Dim Applic
Dim connection
Dim session
Dim WSHShell

Shell "C:\Program Files (x86)\SAP\FrontEnd\SAPgui\saplogon.exe", vbNormalFocus

Set WSHShell = CreateObject("WScript.Shell")
Do Until WSHShell.AppActivate("SAP Logon ")
Application.Wait Now + TimeValue("0:00:01")
Loop

Set WSHShell = Nothing
Set SapGui = GetObject("SAPGUI")
Set Applic = SapGui.GetScriptingEngine

On Error Resume Next

Set connection = Applic.OpenConnection("SG00 - P34: SHAPE Production ERP (with SSO)", True)
Set connection = Applic.OpenConnection("SP01 - P34 : SHAPE ERP Production (with SSO)", True)

Dim App
Set SapGuiAuto = GetObject("SAPGUI")
Set App = SapGuiAuto.GetScriptingEngine
Set connection = App.Children(0)
Set session = connection.Children(0)

Dim Datex As String
Workbooks("Test2.xlsm").Activate
Worksheets("Sheet1").Select
Datex1 = Range("B4").Text
Datex2 = Range("B5").Text
Datex3 = Range("B6").Text

session.findById("wnd[0]").maximize
session.findById("wnd[0]/tbar[0]/okcd").Text = "/nXXXX"
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/ctxtSD_SAKNR-LOW").Text = Datex1
session.findById("wnd[0]/usr/ctxtSD_SAKNR-LOW").caretPosition = 8
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/ctxtSD_BUKRS-LOW").Text = Datex2
session.findById("wnd[0]/usr/ctxtSD_BUKRS-LOW").SetFocus
session.findById("wnd[0]/usr/ctxtSD_BUKRS-LOW").caretPosition = 4
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/usr/ctxtPA_STIDA").Text = Datex3
session.findById("wnd[0]/usr/ctxtPA_STIDA").SetFocus
session.findById("wnd[0]/usr/ctxtPA_STIDA").caretPosition = 10
session.findById("wnd[0]").sendVKey 0
session.findById("wnd[0]/tbar[1]/btn[8]").press
session.findById("wnd[0]/mbar/menu[0]/menu[3]/menu[2]").Select
session.findById("wnd[1]/tbar[0]/btn[0]").press
session.findById("wnd[1]/usr/ctxtDY_PATH").Text = "xxxx"
session.findById("wnd[1]/usr/ctxtDY_FILENAME").Text = "xxxx.txt"
session.findById("wnd[1]/usr/ctxtDY_FILENAME").caretPosition = 20
session.findById("wnd[1]/tbar[0]/btn[11]").press
session.findById("wnd[0]/tbar[0]/btn[12]").press

On Error Resume Next

Set SapGuiAuto = GetObject("SAPGUI")
Set SAPApp = SapGuiAuto.GetScriptingEngine
Set SAPCon = SAPApp.Children(0)
Set session = SAPCon.Children(0)

session.findById("wnd[0]/tbar[0]/okcd").Text = "/n/sapapo/rrp1"
session.findById("wnd[0]/tbar[0]/btn[0]").press
session.findById("wnd[0]/mbar/menu[4]/menu[12]").Select
session.findById("wnd[1]/usr/btnSPOP-OPTION1").press

Workbook.Close SaveChanges:=False

End Sub

 

[Joe4]

I think it should be:
ActiveWorkbook.Close SaveChanges:=False

 

[Cime14]

Thanks @Joe4 but it doesn't work. Workbook remains open...

 

[Joe4]

Which workbook, exactly, are you trying to close?
The one containing this VBA code, or another one?

If it is the one containing the VBA code, you could try:
ThisWorkbook.Close SaveChanges:=False

Note that it you use a command like this instead:
Application.Quit
it will close ALL open Excel workbooks in that session, and will close the Excel session.

 

[Cime14]

I guess issue is with Task Scheduler...if I run macro with any of the suggested codes workbook closes but if workbook is opened through task scheduler than close command is not working

 

[rondeondo]

I'm having a similar issue. I have an excel file macro enabled. I can run it from a batch file or from task scheduler. Can even run the batch from the task scheduler. If I run the batch from the command line it opens excel and my macro enabled workbook, runs the macros and quits. If I run the same batch file from the task scheduler then the excel instance fails to close after the end of the code. The task scheduler is using my user for running the task so should have my permissions. I have found that network drives can't be referred to in scheduled tasks. Perhaps this is the issue

 

[Joe4]

I'm having a similar issue. I have an excel file macro enabled. I can run it from a batch file or from task scheduler. Can even run the batch from the task scheduler. If I run the batch from the command line it opens excel and my macro enabled workbook, runs the macros and quits. If I run the same batch file from the task scheduler then the excel instance fails to close after the end of the code. The task scheduler is using my user for running the task so should have my permissions. I have found that network drives can't be referred to in scheduled tasks. Perhaps this is the issue

Your question is a bit different than the original question asked. So it would be best for you to post it in your own new thread, so that it shows up on the "Unanswered threads" listing, and has a better chance of being answered. Be sure to post your VBA code in that post too, as people will almost certainly want/need to see it.

 

[rondeondo]

Agreed. I'd come in from the previous response rather than the OP. Have sorted my issue now.
Re the OP I suspect you're right with the use of "This" when I work with workbook closing I usually have a variable of type workbook set earlier, as I often open two files to copy data between, so then the close refers to the specific workbook variable...
ie when opening I'll have Set WbkOne as open..... then to close WbkOne.close.....