0

I have tried for two days to find a way to use a VBscript to close excel.

I have a vbscript set up in task scheduler that calls a restAPI, then opens excel, updates data model in workbook called RepublicReport. (This works great)

The data model update triggers file save using THISWORBOOK event. (this works great)

Then the THISWORKBOOK event SAVE triggers a macro that creates new copy of the excel worksheet, and emails the worksheet to client. (This works great)

Then it closes excel file RepublicREport (this works great)

Then IS SUPPOSE to close excel all together (Not working!!!!).

For some reason, after it all runs it opens a new instance of excel back up as BOOK1.xls. I created a vbscript to come behind the task 15 minutes later and close excel again, but it isn't closing the file.

Here is my code for THISWORKBOOK save event. (yes I have two separate pieces of code that are trying to close the workbook). It closes the RepublicReport workbook but opens a new version of excel as Book1.xls

Private Sub Workbook_AfterSave(ByVal Success As Boolean)
Dim objExcel
Dim objWorkbook

Call Macro2

Application.DisplayAlerts = False
ThisWorkbook.Close
ThisWorkbook.Saved = True
On Error Resume Next
Set objExcel = GetObject("C:\Republic_Standard_Report\RepublicReport.xlsm").Application
Set objWorkbook = objExcel.Workbooks("RepublicReport.xlsm")
If (Not objWorkbook Is Nothing) Then
objExcel.Workbooks("RepublicReport.xlsm").Saved = True
objExcel.Workbooks("RepublicReport.xlsm").Close
End If
On Error GoTo 0
objExcel.Quit
End Sub

Here is my vbScript to close excel that is in a separate instance of TaskScheduler.

Dim objExcel
Dim objWorkbook

On error resume next
Set objExcel = CreateObject("Excel.Application")  'Reference to ANY excel file on local pc
set objWorkbook = objExcel.Workbooks("Book1.xlsx") 'Name of file you want to close
if (not objWorkbook is nothing) then

objExcel.Workbooks("Book1.xlsx").Saved=True
objExcel.Workbooks("Book1.xlsx").Close
end if
on error goto 0
objExcel.quit

I am still learning, but I have tried several solutions I found on the internet. Originally when I nested them in the same task, they hopped over each other. So I split them thinking it would help, any suggestions are greatly welcomed. THANK YOU IN ADVANCE!!!

user692942
  • 16,398
  • 7
  • 76
  • 175
powertrioapproach
  • 1
  • Although VBScript is mentioned it's not relevant to the question as the issue is Excel not closing properly which happens in the VBA macro and when called afterwards via VBScript *(again the it's calling the WorkBook with the VBA macro in)*. – user692942 Jul 20 '20 at 16:06
  • 1
    Thank you Lankymart, I was able to find a piece of code in the link that worked: Application.EnableEvents = False Application.DisplayAlerts = False If Application.Workbooks.Count = 1 Then 'Close Excel application ThisWorkbook.Save Application.Quit Else 'Close the active workbook With ActiveWorkbook .Close Savechanges:=True End With End If – powertrioapproach Jul 20 '20 at 22:15
  • Lankymart, as to your point about VBscript not being relevant, I wasn't clear I, I apologize. I was trying to state that I tried two different VBscript that came behind my initial VBscript (the one that called API and updated excel) that would close the excel file that remained open after 5min. I even put them in a different task but I could not get them to work. I found a Taskkill option that I was able to use, that killed the excel.exe but I like the solution you posted because they can be posted into the close event in excel and it closes everything at one time. THANKS AGAIN!!!! – powertrioapproach Jul 20 '20 at 22:20
  • No apology necessary, was merely pointing out the tag [tag:vbscript] was not relevant to the question. Glad the duplicate was useful. – user692942 Jul 20 '20 at 22:24

0 Answers0