5

after a day close to insanity as of why my script wouldn't work on the data provided by the customer, I figured out, that it is the word "encode" somewhere enclosed in double and single quotes in my command, that prevents Wscript.Shell from opening powershell (in which the command works as expected). The german languages' feature to append words like "Marke" and "Code" by adding a "n", hence "Markencode" allows for a lot of words containing "encode". :/

I've created some minimal vba example to show the issue. Does anyone know a way around it?

Option Explicit
Const HK = """"

Sub ScanDrive()
    Dim s As String, command As String
    Dim oExec As Object
    command = "echo 'encode'"
    Debug.Print (command)
    Set oExec = CreateObject("Wscript.Shell").Exec("powershell.exe -command " & HK & command & HK)
    
    Do While oExec.Status = 0
       Application.Wait (Now + TimeValue("0:00:01"))
    Loop
    Debug.Print (oExec.ExitCode)
    s = oExec.StdOut.ReadAll
    Debug.Print (s)
End Sub

The output is (VBA Version 7.1.1108 in Excel):

echo 'encode'
-536870873

Unfortunately I couldn't find anything for that exit code. Btw...putting in 'decode' instead of 'encode' works fine.

Daniel Kaupp
  • 165
  • 2
  • 12
  • 1
    I copied your code into a new Excel Workbook and ran the script: my output was different than yours: "echo 'encode' -1073741510 encode". `s` was able to print the value "encode". I am on American English language in Excel & Windows, so potentially this is just an issue with German. – Toddleson Aug 13 '21 at 15:06
  • I think my exit code is because I manually close the cmd window. It is stuck on Status = WshRunning until I manually close the window. – Toddleson Aug 13 '21 at 15:14
  • @Toddleson - What does Excel have to do with this? – Jeff Zeitlin Aug 13 '21 at 15:15
  • Thanks @Toddleson, still wondering why you have that errorcode then. When I drop the first two lines of the script (so basically the double quotes), it runs perfectly. But having those not in place caused another error. I'm on version VBA: Retail 7.1.1108 . – Daniel Kaupp Aug 13 '21 at 15:15
  • @JeffZeitlin I'm just mentioning what I'm using to run the VBA, we're trying to reproduce an error, which can be very specific sometimes. – Toddleson Aug 13 '21 at 15:16
  • @JeffZeitlin: I'm running this in Excel as well, forgot to mention that..will edit – Daniel Kaupp Aug 13 '21 at 15:16
  • 1
    OK, that's important - I usually use WScript.Shell from VBScript, which isn't _quite_ the VBA of Office. – Jeff Zeitlin Aug 13 '21 at 15:18
  • @Toddleson, you are talking about cmd window, should be powershell window, right? – Daniel Kaupp Aug 13 '21 at 15:28
  • @DanielKaupp Yeah, sorry, not cmd window, the title of the window is powershell.exe. I'm not too familiar with it's normal behaviour, but when I run the script this window does not close automatically, and when I manually close it, the exit code changes to -1073741510 – Toddleson Aug 13 '21 at 15:31
  • 1
    Unable to reproduce. Code exits with a `0` and the echo comes back `encode`. Also my window closes automatically. – HackSlash Aug 13 '21 at 15:40
  • @HackSlash and Toddleson can you please post your (excel) vba versions? Thanks – Daniel Kaupp Aug 13 '21 at 16:20
  • @DanielKaupp Excel 2007 v12.0.4518.1014 On Windows 7 64-Bit. using English (Canada) for Windows. Excel has English (Canada) and English (United States). – Toddleson Aug 13 '21 at 16:24
  • 1
    Is this example truly representative of the problem you're facing, or have you simplified a little from the actual use case? Maybe look here: https://stackoverflow.com/questions/707646/echo-equivalent-in-powershell-for-script-testing#:~:text=Write-Output%20has%20a%20weird%20side-effect%20where%20the%20content%20of%20the%20last%20call%20to%20Write-Output%20will%20be%20left%20on%20the%20stack%2C%20overriding%20any%20variable%20you%20attempt%20to%20explicitly%20return%20from%20a%20function and at the links in that comment. Consider using `Write-Host` not `echo` if you just want console output. – Tim Williams Aug 14 '21 at 00:40
  • @TimWilliams...Yes, I simplyfied it. Actual use case is to create md5-hashes from filenames...which works, unless for those having the string encode in it. Then the powershell window is not even started. – Daniel Kaupp Aug 16 '21 at 06:57

1 Answers1

1

As this seems to be no general problem, I worked around it by splitting the string between "en" and "code" and then let powershell put it back togheter.


'...
pos = InStr(fileName, "encode")
            If pos > 0 Then
                Dim l, r As String
                l = Left(fileName, pos + 1) & "'"
                r = "'" & Right(fileName, Len(fileName) - (pos + 1))
                'Split filename after "en" and before "code"
                fileName = "-join(" & l & " , " & r & " )"
                Debug.Print "Had to split the filename: " & fileName
            End If
'...

Perhaps this helps someone :)

Daniel Kaupp
  • 165
  • 2
  • 12