0

I'm attempting to run an exe via command line that requires a JSON string as an argument. Inside that JSON string are dynamic start/end date variables, and a file name variable.

I'm able to run the command with the Shell function, however I need the waitTillComplete functionality of WScript.Shell.

This is how the string looks when I pass it to Call Shell() {which runs successfully}

C:\Users\API\Client.exe /api/v2/ro/my/deposits {\"startDateInOut\":\"2022-09-30\",\"enddate\":\"2022-10-08\"} deposit-2022-09-30_2022-10-08.JSON

After much searching I still cannot for the life of me figure out how to structure this so shell.Run() will accept it.

Here is what I've got so far:

Sub test()

    Dim shell As Object
    Set shell = VBA.CreateObject("WScript.Shell")
    Dim waitTillComplete As Boolean: waitTillComplete = True
    Dim style As Integer: style = 1
    Dim errorcode As Integer
    Dim path As String
    
    startDateInOut = "2022-10-01"
    EndDate = "2022-10-10"
    
    client = "C:\Users\API\Client.exe"
    arg_1 = "/api/v2/ro/my/deposits {\""startdate\"":\" & """" & startDateInOut & "\""" & ",\""enddate\"":\" & """" & EndDate & "\""}" & " " & "test.json"
    
    path = Chr(34) & client & Chr(34) & " " & Chr(34) & arg_1 & Chr(34)
    
    Debug.Print path
    goClient = shell.Run(path, style, waitTillComplete)

End Sub

If possible I would like to avoid using a batch file and keep everything inside of the vba module.

Any help would be greatly appreciated!

Shaun
  • 65
  • 1
  • 8
  • Is the name of the first json key "startDateInOut" or "startdate" ? What happens when you run that VBA? – Tim Williams Oct 08 '22 at 01:31
  • HEy Tim. The key's are "startdate" and "enddate", with the values coming from the variables "startDateInOut" and "EndDate". running the test sub above yields a runtime error = "Method 'Run' of object 'IWshShell3' failed" – Shaun Oct 08 '22 at 22:42
  • path appears as "C:\Users\API\Client.exe" "/api/v2/ro/my/deposits {\"startdate\":\"2022-10-01\",\"enddate\":\"2022-10-10\"} test.json" – Shaun Oct 08 '22 at 22:42
  • Have you tried using `^` to escape the embedded double-quotes? Except for a few cases it seems like the caret is the preferred escape character. https://ss64.com/nt/syntax-esc.html#:~:text=Special%20Cases,character%20instead%20of%20%5E – Tim Williams Oct 10 '22 at 18:41

0 Answers0