0

So, I'm using (after modification) this code, from here: How to set recurring schedule for xlsm file using Windows Task Scheduler

My error: Runtime error: Unknown runtime error.

I've searched far and wide to find an way to close the Excel process, but almost everybody uses .Quit sadly it gives the above error. I've also tried .Close, but that is not recognized

' Create a WshShell to get the current directory
Dim WshShell
Set WshShell = CreateObject("WScript.Shell") 

' Create an Excel instance
Dim myExcelWorker
Set myExcelWorker = CreateObject("Excel.Application") 

' Disable Excel UI elements
myExcelWorker.DisplayAlerts = False
myExcelWorker.AskToUpdateLinks = False
myExcelWorker.AlertBeforeOverwriting = False
myExcelWorker.FeatureInstall = msoFeatureInstallNone

' Tell Excel what the current working directory is 
Dim strSaveDefaultPath
Dim strPath
strSaveDefaultPath = myExcelWorker.DefaultFilePath
strPath = "C:\Users\hviid00m\Desktop"
myExcelWorker.DefaultFilePath = strPath

' Open the Workbook specified on the command-line 
Dim oWorkBook
Dim strWorkerWB
strWorkerWB = strPath & "\Status Report (Boxplots) TEST.xlsm"

Set oWorkBook = myExcelWorker.Workbooks.Open (strWorkerWB, , , , , , True)

' Build the macro name with the full path to the workbook
Dim strMacroName
strMacroName = "Refresh"
on error resume next 
myExcelWorker.Run strMacroName
if err.number <> 0 Then
WScript.Echo "Fejl i macro"
End If
err.clear
on error goto 0 
oWorkBook.Save 
' Clean up and shut down
' Don’t Quit() Excel if there are other Excel instances 
' running, Quit() will shut those down also
myExcelWorker.Quit <--- ERROR

Set oWorkBook = Nothing
Set myExcelWorker = Nothing
Set WshShell = Nothing
Community
  • 1
  • 1
Sinnich
  • 342
  • 5
  • 17

1 Answers1

0

Found some code on a different side. The reason why (as far as I understood) is that .Quit and .Close is for VBA not VBS.

' Clean up and shut down
' Don’t Quit() Excel if there are other Excel instances 
' running, Quit() will shut those down also
Dim objWMIService, objProcess, colProcess

Set objWMIService = GetObject("winmgmts:" & "{impersonationLevel=impersonate}!\\.\root\cimv2") 
Set colProcess = objWMIService.ExecQuery ("Select * from Win32_Process Where Name = "         & "'EXCEL.EXE'")

For Each objProcess in colProcess
objProcess.Terminate()
Next`

Set oWorkBook = Nothing
Set myExcelWorker = Nothing
Set WshShell = Nothing
Sinnich
  • 342
  • 5
  • 17
  • 1
    That is incorrect. `Quit` (on the application object) and `Close` (on workbooks) work just fine from VBScript. Also, `Quit` will not close other Excel instances, only all windows of the referenced instance. – Ansgar Wiechers Jan 14 '15 at 22:55
  • Sorry for no respons, have been on holiday. Well Quit and Close didn't work for me, if it did I wouldn't have said I've tried it and asked a question here :-) Just saying. – Sinnich Jan 27 '15 at 10:57