PowerShell command doesn't work when called from VBA, but otherwise works

Question

Currently I can't use xlwings because I can't access Windows' cmd. But I can access PowerShell, so I tried to change xlwings specific VBA code that calls the cmd to call the PowerShell instead.

When calling PowerShell with commands from VBA, it does not work. If I paste the exact same commands in the PowerShell terminal, it works as expected.

I've tried to compare the (not working) command VBA passes to PowerShell with the (working) command I manually paste into PowerShells terminal. But they look exactly the same.

The original xlwings code that calls the cmd

RunCommand = PythonInterpreter & " -B -c ""import sys, os; sys.path[0:0]=os.path.normcase(os.path.expandvars(r'" & PYTHONPATH & "')).split(';'); " & PythonCommand & """ "

ExitCode = Wsh.Run("cmd.exe /C " & _
           DriveCommand & RunCommand & _
           """" & WORKBOOK_FULLNAME & """ ""from_xl""" & " " & _
           Chr(34) & Application.Path & "\" & Application.Name & Chr(34) & " " & _
           Chr(34) & Application.Hwnd & Chr(34) & _
           " 2> """ & LOG_FILE & """ ", _
           WindowStyle, WaitOnReturn)

And my slightly modified version

RunCommand = "C:\ProgramData\Anaconda3\pythonw.exe -B -c ""import sys, os; sys.path[0:0]=os.path.normcase(os.path.expandvars(r'" & PYTHONPATH & "')).split(';'); " & PythonCommand & """ "

ExitCode = Wsh.Run("Powershell.exe -ExecutionPolicy ByPass -NoExit " & _
           DriveCommand & RunCommand & _
           """" & WORKBOOK_FULLNAME & """ ""from_xl""" & " " & _
           Chr(34) & Application.Path & "\" & Application.Name & Chr(34) & " " & _
           Chr(34) & Application.Hwnd & Chr(34) & _
           " 2> """ & LOG_FILE & """ ", _
           WindowStyle, WaitOnReturn)

The resulting command from aboves code. Working when pasted into PowerShells terminal directly, not working when executed from VBA:

C:\ProgramData\Anaconda3\pythonw.exe -B -c "import sys, os; sys.path[0:0]=os.path.normcase(os.path.expandvars(r'C:\Users\<placeholder>\test1;')).split(';'); import test1;test1.hello_xlwings()" "C:\Users\<placeholder>\test1\test1.xlsm" "from_xl" "C:\Program Files (x86)\Microsoft Office\Office16\Microsoft Excel" "788640" 2> "C:\Users\<placeholder>\AppData\Roaming\xlwings.log"

I expect a simple "Hello, World!" in a specific Excel cell when clicking a button associated with the vba macro. Instead I get this error:

At line:1 char:213
+ ... X0RNZ\test1;')).split(';'); import test1;test1.hello_xlwings() C:\Use ...
+                                                                  ~
An expression was expected after "(".
    + CategoryInfo          : ParserError: (:) [], ParentContainsErrorRecordException
    + FullyQualifiedErrorId : ExpectedExpression

If I paste the command in the PowerShell terminal directly I get my expected result, "Hello, World!" shows up in my specific Excel cell.

mhu · Accepted Answer · 2019-05-21 12:44:49Z

0

You are missing the -Command parameter. Depending on what DriveCommand contains, you should add -Command before DriveCommand or RunCommand.

Make sure there is a semicolon between de PowerShell commands and specify the command as a scriptblock, example:

powershell.exe -ExecutionPolicy ByPass -NoExit -Command { cd "c:\folder";c:\folder\run.exe "param1" "param2" }

Run powershell /? for more examples.

edited May 21, 2019 at 12:44

answered May 21, 2019 at 11:32

mhu

18.1k10 gold badges67 silver badges97 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Tobias Over a year ago

Unfortunately adding the -Command parameter doesn't work, still the same error. Tried to put it before DriveCommand and before RunCommand, nothing worked. Since you asked what they contain: DriveCommand = cd C:\ProgramData\Anaconda3\" and

RunCommand = "C:\ProgramData\Anaconda3\pythonw.exe -B -c "import sys, os; sys.path[0:0]=os.path.normcase(os.path.expandvars(r'C:\Users\<placeholder>\test1;')).split(';'); import test1;test1.hello_xlwings()"

mhu Over a year ago

A semicolon is needed to split the commands and the command should be enclosed in curly brackets or quotes, see updated answer

Tobias Over a year ago

Tried to implement it this way but it still does not work, same error. My command is:

Powershell.exe -ExecutionPolicy ByPass -NoExit -Command { cd C:\folder\Anaconda3;C:\folder\pythonw.exe -B -c "import sys, os; sys.path[0:0]=os.path.normcase(os.path.expandvars(r'C:\folder\test1;')).split(';'); import test1;test1.hello_xlwings()" "C:\folder\test1.xlsm" "from_xl" "C:\folder\Microsoft Excel" "788640" 2> "C:\folder\xlwings.log" }

. My workaround is a seperate file.ps1 which I call with -File and passing the parameters. But I'd prefer to stick as close to the original code as possible.

Collectives™ on Stack Overflow

PowerShell command doesn't work when called from VBA, but otherwise works

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related