Pass in multiple arguments in VBA to run a Python script

Question

Through VBA, I would like to execute a shell command that launches a Python script.

Private Type STARTUPINFO
  cb As Long
  lpReserved As String
  lpDesktop As String
  lpTitle As String
  dwX As Long
  dwY As Long
  dwXSize As Long
  dwYSize As Long
  dwXCountChars As Long
  dwYCountChars As Long
  dwFillAttribute As Long
  dwFlags As Long
  wShowWindow As Integer
  cbReserved2 As Integer
  lpReserved2 As Long
  hStdInput As Long
  hStdOutput As Long
  hStdError As Long
End Type

Private Type PROCESS_INFORMATION
  hProcess As Long
  hThread As Long
  dwProcessID As Long
  dwThreadID As Long
End Type

Private Declare Function WaitForSingleObject Lib "kernel32" (ByVal _
  hHandle As Long, ByVal dwMilliseconds As Long) As Long

Private Declare Function CreateProcessA Lib "kernel32" (ByVal _
  lpApplicationName As Long, ByVal lpCommandLine As String, ByVal _
  lpProcessAttributes As Long, ByVal lpThreadAttributes As Long, _
  ByVal bInheritHandles As Long, ByVal dwCreationFlags As Long, _
  ByVal lpEnvironment As Long, ByVal lpCurrentDirectory As Long, _
  lpStartupInfo As STARTUPINFO, lpProcessInformation As _
  PROCESS_INFORMATION) As Long

Private Declare Function CloseHandle Lib "kernel32" (ByVal _
  hObject As Long) As Long

Private Const NORMAL_PRIORITY_CLASS = &H20&
Private Const INFINITE = -1&

Public Sub ExecScript(cmdline As String)
  Dim proc As PROCESS_INFORMATION
  Dim start As STARTUPINFO
  Dim ReturnValue As Integer

  'Initialize the STARTUPINFO structure:
  start.cb = Len(start)

  'Start the shelled application:
  ReturnValue = CreateProcessA(0&, cmdline$, 0&, 0&, 1&, NORMAL_PRIORITY_CLASS, 0&, 0&, start, proc)

  'Wait for the shelled application to finish:
  Do
    ReturnValue = WaitForSingleObject(proc.hProcess, 0)
    DoEvents
  Loop Until ReturnValue <> 258

  ReturnValue = CloseHandle(proc.hProcess)

End Sub 

Public Function DataFormatting(inputData As Variant) As String
  Dim dataRank As Integer

  DataFormatting = "["
  For dataRank = 0 To UBound(inputData)
    inputData(dataRank) = Replace(inputData(dataRank), " ", "")
    If dataRank = 0 Then
      DataFormatting = DataFormatting & "\`" & """" & inputData(dataRank) & "\`" & """"
    Else
      DataFormatting = DataFormatting & ",\`" & """" & inputData(dataRank) & "\`" & """"
    End If
  Next dataRank
  DataFormatting = DataFormatting & "]" & """"
End Function

Public Sub RunPython()
  Dim oCmd as String, scriptPath as String, scriptName as String, campaign as String, datas as String 

  scriptPath = ActiveWorkbook.Path & "\Scripts\"
  scriptName = "EngineExecution.py"
  campaign = "Nom de la campagne"
  planningPeriods = checkboxSelected
  datas = DataFormatting(planningPeriods)

  oCmd = "pythonw.exe " & scriptPath & scriptName & " " & """" & campaign & """" & " " & """" & datas
  Call ExecScript(oCmd)
End Sub

When I execute this command with PowerShell, it works but not with VBA.

python .\EngineExecution.py "Nom de la campagne" "[\`"12/10/2020-18/10/2020\`",\`"05/10/2020-11/10/2020\`"]"

Here is the start of my Python code. Perhaps this is where the error comes from?

import sys
import json

campaign = sys.argv[1]
ppSelected = json.loads(sys.argv[2])

Could you please help me to make it work properly with VBA ?

Thanks in advance

Replace Call ExecScript(oCmd) with Debug.Print oCmd - does the output match the command that works in PowerShell? — Mathieu Guindon
– Mathieu Guindon, Commented May 4, 2020 at 14:45
@Mathieu Guindon . Your idea is good. I now have exactly the same syntax as the command that works with PowerShell. But it still doesn't work under VBA. — Christophe C.
– Christophe C., Commented May 4, 2020 at 15:20
What does it doesn't work mean? Errors? Undesired results? Nothing? Please describe what actually happens. Why are you creating a PROCESS and not just use Shell oCmd? Also you use different Python interpreters between VBA and PS: python vs pythonw. — Parfait
– Parfait, Commented May 4, 2020 at 15:22
@Parfait. Thanks for all the corrections. it is indeed clearer. The python script does not run through VBA while it works perfectly when I run the PowerShell command. Unfortunately, I have no error message. I think I must write the VBA command wrong but I can't find it.As for the process, I need it to wait for the end of execution — Christophe C.
– Christophe C., Commented May 4, 2020 at 15:26
testoCmdoutput on a cmd shell and be aware of the wrongstartlength computation. On x86 vba it doesn't matter, but on x64 it will faill. UseLenB(start)for correct value of start.cb. — ComputerVersteher
– ComputerVersteher, Commented May 5, 2020 at 9:48

Parfait · Accepted Answer · 2020-05-04 15:36:46Z

1

Calling an external command from VBA is very straightforward and does not require your extensive need of creating a process. Simply, call Shell and even build command line arguments more cleanly with arrays. Below updates your RunPython subrountine, assumming all arguments are correctly specified:

Public Sub RunPython()
    Dim args(0 To 3) As String
    Dim pyCmd As String
    Dim i As Integer

    args(0) = "python"
    args(1) = ActiveWorkbook.Path & "\Scripts\EngineExecution.py"
    args(2) = "Nom de la campagne"
    args(3) = DataFormatting(checkboxSelected)

    pyCmd = args(0)
    For i = 1 To UBound(args)
        pyCmd = pyCmd & " """ & args(i) & """"
    Next i        
    Debug.Print pyCmd                                ' CHECK COMMAND LINE CALL

    'RUN PYTHON SCRIPT WITH ARGS
    Shell pyCmd, vbNormalFocus
End Sub

answered May 4, 2020 at 15:36

Parfait

108k19 gold badges102 silver badges138 bronze badges

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

2 Comments

Christophe C. Over a year ago

Hello Parfait, I recognize that your code is indeed clearer but does not work better. What I don't understand is that pyCmd works correctly with PowerShell and no longer works with VBA. Do you have any idea? I added the beginning of the Python script. Maybe that will allow us to see more clearly.

ComputerVersteher Over a year ago

Issue is caused by different escape chars in cmd(\) and ps(`) as args contain dquotes that need escaping.

Christophe C. · Accepted Answer · 2020-05-05 12:38:05Z

0

Thank you all for your help. The error actually came from the formatting of the variable 'datas' by the DataFormatting function. Below is the code that works.

Public Function DataFormatting(inputData As Variant) As String
  Dim dataRank As Integer

  DataFormatting = "["
  For dataRank = 0 To UBound(inputData)
    If dataRank = 0 Then
      DataFormatting = DataFormatting & """""" & inputData(dataRank) & """"""
    Else
      DataFormatting = DataFormatting & "," & """""" & inputData(dataRank) & """"""
    End If
  Next dataRank
  DataFormatting = DataFormatting & "]"
End Function

answered May 5, 2020 at 12:38

Christophe C.

1474 silver badges14 bronze badges

Collectives™ on Stack Overflow

Pass in multiple arguments in VBA to run a Python script

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related