-1

When I am placing the code Excel file in a folder/directory which does not have any space in it the naming conventions than it is working fine and firing the Web-Service. But when I place the code Excel file in a folder which has space in it's naming convention, I am getting a run-time error:

'-2147024894 (80070002).

Please help

Sub InvokShellScript1()

Dim sApp As String
Dim var As Integer

sApp = ThisWorkbook.Path & "\protected\WSInvoke.bat   " & ThisWorkbook.Path & "\protected\Refresh.txt  " & ThisWorkbook.Path

Dim objShell As Object

   Set objShell = CreateObject("WScript.Shell")
   var = objShell.Run(sApp, 0, True)


End Sub
cybernetic.nomad
  • 6,100
  • 3
  • 18
  • 31
Jitender Singh Soam
  • 3
  • 1
  • 2
  • Possible duplicate of https://stackoverflow.com/questions/21267283/error-running-shell-object-commands-through-excel-vba – Hamedz Oct 09 '19 at 17:06

1 Answers1

0

You need quotes around the paths to be able to handle spaces. Try either of these:

sApp = Chr$(34) & ThisWorkbook.Path & "\protected\WSInvoke.bat" & Chr$(34) & " " & Chr$(34) & ThisWorkbook.Path & "\protected\Refresh.txt" & Chr$(34) & " " & Chr$(34) & ThisWorkbook.Path & Chr$(34)

or if Chr() doesn't work:

sApp = """" & ThisWorkbook.Path & "\protected\WSInvoke.bat"" """ & ThisWorkbook.Path & "\protected\Refresh.txt"" """ & ThisWorkbook.Path & """"

Jason Cote
  • 16
  • 4
  • Thank you very much buddy the Chr$ one worked. However, the second one require a little bit of Tweaking. sApp = """" & ThisWorkbook.Path & "\protected\WSInvoke.bat"" """ & ThisWorkbook.Path & "\protected\Refresh.txt"" """ & ThisWorkbook.Path & """" – Jitender Singh Soam Oct 10 '19 at 04:42
  • Oops you're right, I missed quotes there, thanks for catching it. I updated my answer with the corrected text. – Jason Cote Oct 11 '19 at 11:55