0

Hello I have a question regarding excel vba. If i want to call a python program using Excel VBA, but it has a whitespace I get a crash. For example:

Dim shell As Object
Dim exePath As String
Dim scriptPath As String

Set shell = VBA.CreateObject("Wscript.shell")
exePath = """C:\Users\****\Anaconda3\python.exe"""
scriptPath = "C:\Users\****\Desktop\Hello World\Hello_World.py"
shell.Run "cmd /k " & exePath & " " & scriptPath

when I try to run this code, the argument to the shell truncates at \Desktop\Hello, I have tried wrapping scriptPath with another set of quotes which creates a compile error, while wrapping it in chr(34) creates an error saying the filename, directory name, or volume label syntax is incorrect. I have also tried wrapping the entire shell.Run line with chr(34) which leads to Runtime Error Method Run of shell failed. Is there any way around this issue or is it just a VBA limitation? I should mention that running it directly in the command line is perfectly fine, it seems entirely an issue of running it in VBA

Shufan Wen
  • 1
  • 1
  • Did you try wrapping it with quotes as described by [Opening a File whose Name Contains a Space](https://stackoverflow.com/questions/46593437/opening-a-file-whose-name-contains-a-space)? – DarrylG Jul 20 '20 at 04:33
  • 2
    `shell.Run "cmd /k " & exePath & " """ & scriptPath & """"` – Tim Williams Jul 20 '20 at 04:50
  • This is not a problem of VBA but syntax of cmd.exe. Check the string passed to the ``shell.Run`` with the Watch window. – kamocyc Jul 20 '20 at 05:42
  • Triple quote scriptPath just like you did for exePath. Does that work? It boils down to what @Tim Williams suggests. – Excelosaurus Jul 20 '20 at 05:54
  • Hi all thanks for the suggestions, I tried quoting scriptPath as I did for exePath and I ran into an error saying the filename, directory name, or volume label syntax is incorrect. The same issue occured following Tim William's suggestion. – Shufan Wen Jul 20 '20 at 14:54

0 Answers0