VBScript for terminating SAP generated Workbook Spreadsheet

Question

Can somebody help with this script I am using for closing/terminating SAP Workbook? This worked for a long time until the time that the company updated our MS Office to a new version (MS Office 365 ProPlus). The intention of this code is to force close a certain SAP generated spreadsheets that keeps on auto-pop up when extracting a SAP reports. This old code was shared by @ScriptMan before in this forum.

SAP_Workbook = "ZZFRAR080_EXTRACTED.XLSX" 
on error resume next
do
 err.clear
 Set xclApp = GetObject(, "Excel.Application")
 If Err.Number = 0 Then exit do
 'msgbox "Wait for Excel session"
 wscript.sleep 2000
 loop
do 
 err.clear
 Set xclwbk = xclApp.Workbooks.Item(SAP_Workbook)
 If Err.Number = 0 Then exit do
 wscript.sleep 2000
loop
on error goto 0 
Set xclSheet = xclwbk.Worksheets(1)
xclApp.Visible = True
xclapp.DisplayAlerts = false
xclapp.ActiveWorkbook.Close
Set xclwbk = Nothing
Set xclsheet = Nothing
'xclapp.Quit
set xclapp = Nothing

Based on my initial checking, the issue is found in this part as it keeps on looping endlessly (detected it by putting a MsgBox after the loop). I have noticed that the new version of Excel 2016 makes the generated spreadsheets as new instance/session like it keeps on creating new Excel.exe in the process tab.

do
 err.clear
 Set xclwbk = xclApp.Workbooks.Item(SAP_Workbook)
 If Err.Number = 0 Then exit do
 wscript.sleep 2000
loop

I wish someone can help me in this issue – Alvin Roy Nemenzo Mar 07 '18 at 15:55 — Alvin Roy Nemenzo, Mar 07 '18 at 15:55

ScriptMan · Answer 1 · 2018-03-28T13:43:02.047

You could try the following:

SAP_Workbook = "ZZFRAR080_EXTRACTED.XLSX" 

Set Wshell = CreateObject("WScript.Shell")
Wshell.Run "c:\tmp\SAP_Workbook_Close.vbs" & " " & SAP_Workbook, 1, False
. . .

SAP_Workbook_Close.vbs:

myFileName = wscript.arguments(0)

on error resume next
do
 err.clear
 Set xclApp = GetObject(, "Excel.Application")
 'xclApp.WindowState = -4140 'xlMinimized
 If Err.Number = 0 Then exit do
 'msgbox "Wait for Excel session"
 wscript.sleep 2000
loop

do
 err.clear
 Set xclwbk = xclApp.Workbooks.Item(myFileName)
 'xclApp.WindowState = -4140 'xlMinimized
 If Err.Number = 0 Then exit do
 'msgbox "Wait for SAP workbook"
 wscript.sleep 2000
loop

on error goto 0
Set xclSheet = xclwbk.Worksheets(1)

xclapp.ActiveWorkbook.Close

xclApp.Visible = True
xclapp.DisplayAlerts = false

'xclApp.WindowState = -4143 'xlNormal

 Set xclwbk = Nothing
 Set xclsheet = Nothing
 'xclapp.Quit
 set xclapp = Nothing

Regards, ScriptMan

VBScript for terminating SAP generated Workbook Spreadsheet

1 Answers1