2

I am using WshShell to run a batch file from VBA within Excel.

  • The batch file is VERY simple, one line that runs a number crunching program. I can run the batch file directly without problems.
  • I am using WshShell or WScript.Shell because I want VBA to wait to load the results back in Excel once the calculation is done.
  • I have references to "Microsoft Scripting Runtime" and "Windows Script Host Object Model"
  • I have been using the same code for a long time. It worked fine until 2-3 weeks ago. Now it closes Excel. If I have a few spreadsheets opened, it closes them all.
  • I have two versions but they both crash at the "Set WinSh = ..." line. Excel is not responding for a few (~10) seconds and then it closes.
  • I am using Office 365 MSO (16.0.12527.21294) 32 bit. It is managed by my organization.
  • I tried the same code in Word and it crashes as well.

Anyone know if recent updates could do this? or of another method I could use?

The subs are:

Public Sub RunBatch(FPath As String)
    Dim WinSh As Object
    Dim StrCmd As String
    Dim ErrCode As Long
    Set WinSh = New WshShell
    StrCmd = Chr(34) & FPath & Chr(34)
    ErrCode = WinSh.Run(StrCmd, WindowStyle:=1, WaitOnReturn:=True)
End Sub
  
Public Sub RunBatch2(FPath As String)
    Dim WinSh As Object
    Set WinSh = VBA.CreateObject("WScript.Shell")
    WinSh.Run FPath, 1, True
End Sub
user692942
  • 16,398
  • 7
  • 76
  • 175
MJ_Nadeau
  • 83
  • 9
  • What I would do, is the reverse: Run the number crunching from the bat file and hang on until it is done the launch a vbscript script that opens Excel and runs the macro. – Tarik Dec 02 '20 at 16:03
  • You can do it with win32: http://vbnet.mvps.org/index.html?code/faq/waitforsingleobject.htm (Pass `cmd.exe c:\your\bat\file.bat` as the command line) - You could move the current code temporarily to a .vbs file and see if it fails with an error message you can investigate. – Alex K. Dec 02 '20 at 16:07
  • Thanks for the suggestions. I cannot start from a batch file easily as it is part of a longer process where Excel prepares the data, saves an ascii file for the number crunching and then runs it. The funny thing is that it use to work well. I tried a .vbs file and it worked but when I transfer the code back to VBA it crashes again. I could re-write the whole ting in VB.net and control Excel that way but this is a few 1000s lines of VBA code..... Marie – MJ_Nadeau Dec 02 '20 at 16:50
  • @MJ_Nadeau Perhaps something configuration wise has changed on your machine over the past 2-3 weeks, have you tried other machines to see if the behaviour is the same? – user692942 Dec 04 '20 at 09:37
  • @Lankymart We tried it on 5 computers, 4 with office 32 bit and one with office 64 bit. All show the same behaviour but they are all managed by the same organisation. The reference file for WshShell (wshom.ocx) is from 2018. One change is the 2020-11 Servicing Stack update for windows 10 1809. I did not restart one of the computers so the 2020-11 cumulative update is not operational. There is no event we could trace in the event log. I would be grateful if someone would try on a different general setup but with win 10 1809.and office 365. – MJ_Nadeau Dec 05 '20 at 10:35
  • Possibly the managing org has deployed some kind of GPO that's blocking the scripting host, perhaps ask them if that's the case? – Alex K. Dec 06 '20 at 23:17

1 Answers1

1

The source of the problem is either an update or a change in the security policies of Cisco AMP. This blocks the use of Windows Scripting Host. The security department of my organisation is working to find a solution to the problem.

In the meantime, I found a way around it. I use shell (which does not wait for the batch to end before resuming the VBA code) to run the batch which writes a simple text file at the end of the process and I wait for the text file to appear in 1 second increments. It is crude but it works so far.

Public Sub OneRunBatch()

Dim xlWB As Workbook
Dim fso1 As New FileSystemObject
Dim BatFile As Object
Dim IsDone As Boolean
Dim OutFileName As String
Dim DoneFileName As String
Dim OutPath As String
Dim DeleteBatFile As Boolean

Set xlWB = ThisWorkbook
OutPath = xlWB.Path
OutFileName = "HW.bat"
DoneFileName = "Done.txt"
DeleteBatFile = False

Set BatFile = fso1.CreateTextFile(OutPath & "\" & OutFileName)
BatFile.WriteLine "cd /d " & OutPath
BatFile.WriteLine "echo Hello World"
BatFile.WriteLine "dir > " & DoneFileName
BatFile.Close

IsDone = False
Call Shell(OutPath & "\" & OutFileName, vbNormalFocus)

Do Until IsDone
    If fso1.FileExists(OutPath & "\" & DoneFileName) Then IsDone = True
    Application.Wait (Now + TimeValue("00:00:01"))
Loop

fso1.DeleteFile (OutPath & "\" & DoneFileName)
If DeleteBatFile Then fso1.DeleteFile (OutPath & "\" & OutFileName)

End Sub
MJ_Nadeau
  • 83
  • 9